CODEWITHSUNDEEP
mysqlsqloracle-database
FaheemFayaz
FaheemFayaz

Reputation: 43

How to get a random row from database in more optimized way

Hi i know how to get random row but i need an optimized way, i am migrating huge data from one schema to another in oracle.Other than performing count validation on each table. I am doing random record validation (for any random row , i am checking whether all column values match or not between the databases).I am using 

SELECT * FROM (SELECT * FROM  ADM_USER ORDER BY dbms_random.value) WHERE rownum = 1;

Before that i was using:

 select * from ADM_USER where ADM_USER_ID=(select Round(dbms_random.value(1,max(ADM_USER_ID))) from ADM_USER)

The problem with the latter one is that values in ADM_USER_ID are not contiguous.So most of the times the query returns empty result set. The first one is good but for tables with huge cardinality it takes 6 to 7 seconnds.

Thanks in advance.

Upvotes: 0

Views: 99

Answers (1)

Gary Myers
Gary Myers

Reputation: 35401

For Oracle, look at the SAMPLE clause. The following will look at random 1% of a table

select * from MDSYS.SDO_COORD_REF_SYS sample(1);

You can still add the rownum=1 filter on top of that.

Upvotes: 1

Related Questions