RDBMS version: 11.2.0.4
From a big table, I want to randomly retrieve records for a particular time frame.
Example : I want to randomly retrieve 25 records that were generated between 13-APRIL-2016 to 17-APRIL-2016
As you can see below rownum cannot do this task. It is always fetching the same records (IDs from 1 to 4) every time I execute.
As Ed Stevens and Dave comment on the below mentioned post, rownum just returns an indeterminate row. ie. The result set can repeat.
create table test5 ( id number, created_dt date );
insert into test5 values ( 1, to_date('13-APR-2016 14:20:12','DD-MON-YYYY HH24:mi:ss'));
insert into test5 values ( 2, to_date('13-APR-2016 21:23:42','DD-MON-YYYY HH24:mi:ss'));
insert into test5 values ( 3, to_date('14-APR-2016 01:36:58','DD-MON-YYYY HH24:mi:ss'));
insert into test5 values ( 4, to_date('15-APR-2016 14:55:14','DD-MON-YYYY HH24:mi:ss'));
insert into test5 values ( 5, to_date('15-APR-2016 17:32:45','DD-MON-YYYY HH24:mi:ss'));
insert into test5 values ( 6, to_date('16-APR-2016 13:20:12','DD-MON-YYYY HH24:mi:ss'));
insert into test5 values ( 7, to_date('17-APR-2016 16:23:42','DD-MON-YYYY HH24:mi:ss'));
insert into test5 values ( 8, to_date('17-APR-2016 15:36:58','DD-MON-YYYY HH24:mi:ss'));
insert into test5 values ( 9, to_date('18-APR-2016 14:25:14','DD-MON-YYYY HH24:mi:ss'));
commit;
SQL> alter session set nls_Date_format='DD-MON-YYYY HH24:MI:SS';
-- It is always fetching the same records (IDs from 1 to 4) every time I execute
select id from test5
where created_dt >= to_date('13-Apr-2016 14:00:00','DD-MON-YYYY HH24:mi:ss')
and created_dt < to_date('18-Apr-2016 15:00:00','DD-MON-YYYY HH24:mi:ss')
and rownum < 5;
ID
----------
1
2
3
4
SQL> /
ID
----------
1
2
3
4
SQL> /
ID
----------
1
2
3
4
In the below post , Chris hunter as provided the following solution for this. I have written a variant of Chris's version for this particular scenario (just added the date filter). It is mentioned below . It works fine with a small test table like TEST5. But, my real life table is huge and inner query has to fetch all the records first. And then rownum just applies to this huge output.
This will take a long time. Is there an alternative ?
https://community.oracle.com/message/13815786#13815786
SELECT id
FROM (SELECT id
FROM test5
where created_dt >= to_date('13-Apr-2016 14:00:00','DD-MON-YYYY HH24:mi:ss')
and created_dt < to_date('18-Apr-2016 15:00:00','DD-MON-YYYY HH24:mi:ss')
ORDER BY DBMS_RANDOM.VALUE
)
WHERE rownum < 5