| Pick random rows [message #627129] |
Fri, 07 November 2014 12:50  |
shree_z
Messages: 75
Registered: February 2008
|
Member |
|
|
Hi
I have a procedure to select random rows from a base table into a sys_refcursor.
After I select the random rows, I want to update those rows in the base table, which was chosen for selection. How can this be achieved in the procedure.
My code looks like this
begin
open p_rec for
SELECT (some columns) FROM
(
select (some columns..)
from base table
where (some conditions)
order by SYS.DBMS_RANDOM.VALUE
)
WHERE ROWNUM<100
Now I need to update those rows in base table .( I have an update_date column in the base table)
Thanks in advance
|
|
|
|
|
|
| Re: Pick random rows [message #627132 is a reply to message #627130] |
Fri, 07 November 2014 13:08  |
shree_z
Messages: 75
Registered: February 2008
|
Member |
|
|
SELECT (some columns) FROM
(
select (some columns..)
from base table
where (some conditions)
order by SYS.DBMS_RANDOM.VALUE
)
WHERE ROWNUM<100
Doesnt the above code take random rows. Please correct if I went wrong in my understanding.
[Updated on: Fri, 07 November 2014 13:08] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Pick random rows [message #627136 is a reply to message #627133] |
Fri, 07 November 2014 13:27 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
BlackSwan wrote on Fri, 07 November 2014 20:14>Doesnt the above code take random rows
NO!
The returned rows are STRICTLY determined only by the WHERE clause.
The only thing "random" with SQL above is the order of the rows are presented to the invoker.
if the base table rows are not changed by DML, the posted SQL will always return the same 99 row every time (likely in different order)
The "Random Selection" in that way to be working for me.....
SQL> CREATE TABLE t (a NUMBER(10), b NUMBER(10));
Table created.
SQL>
SQL> INSERT INTO t VALUES (1,1);
1 row created.
SQL> INSERT INTO t VALUES (2,2);
1 row created.
SQL> INSERT INTO t VALUES (3,3);
1 row created.
SQL> INSERT INTO t VALUES (4,4);
1 row created.
SQL> INSERT INTO t VALUES (5,5);
1 row created.
SQL> INSERT INTO t VALUES (6,6);
1 row created.
SQL> INSERT INTO t VALUES (7,7);
1 row created.
SQL> INSERT INTO t VALUES (8,8);
1 row created.
SQL> INSERT INTO t VALUES (9,9);
1 row created.
SQL>
SQL> SELECT * FROM (
2 SELECT * FROM t WHERE a < 5 ORDER BY SYS.DBMS_RANDOM.Value
3 ) WHERE ROWNUM < 3;
A B
---------- ----------
1 1
4 4
SQL>
SQL> SELECT * FROM (
2 SELECT * FROM t WHERE a < 5 ORDER BY SYS.DBMS_RANDOM.Value
3 ) WHERE ROWNUM < 3;
A B
---------- ----------
4 4
2 2
SQL>
SQL> SELECT * FROM (
2 SELECT * FROM t WHERE a < 5 ORDER BY SYS.DBMS_RANDOM.Value
3 ) WHERE ROWNUM < 3;
A B
---------- ----------
1 1
3 3
SQL>
|
|
|
|
| Re: Pick random rows [message #627137 is a reply to message #627135] |
Fri, 07 November 2014 13:30 |
shree_z
Messages: 75
Registered: February 2008
|
Member |
|
|
Thanks for the replies BlackSwan and Michel
@BlackSwan Could you please suggest a way to select random rows? Thanks for correcting me.
Thanks
|
|
|
|
|
|
|
|
|
|
| Re: Pick random rows [message #627141 is a reply to message #627140] |
Fri, 07 November 2014 14:32 |
shree_z
Messages: 75
Registered: February 2008
|
Member |
|
|
Quote:See Thomas' post and tell us if this is what you want.
Yes that is what I need. but I want to update those rows chosen in random on the base table.
Quote:If you have complex computation then use SELECT FOR UPDATE and loop on it to UPDATE.
If not then directly use UPDATE.
Could you elaborate on this using my code.
begin
open p_rec for
SELECT (some columns) FROM
(
select (some columns..)
from base table
where (some conditions)
order by SYS.DBMS_RANDOM.VALUE
)
WHERE ROWNUM<100 [Updated on: Fri, 07 November 2014 14:33] Report message to a moderator |
|
|
|
|
|
| Re: Pick random rows [message #627281 is a reply to message #627139] |
Mon, 10 November 2014 10:02 |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
Oracle® Database SQL Language Reference
Sample_clause
The sample_clause lets you instruct the database to select from a random sample of data from the table, rather than from the entire table.
[Updated on: Mon, 10 November 2014 10:03] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
