How to randomly select a record from a table

How to randomly select a piece of data from a table

Oracle random reading method of N data in the table:

1) select * from (select * from tablename order by sys_guid()) where rownum 2) select * from (select * from tablename order by dbms_random.value) where rownum3) select * from (select * from table_name sample( 10) order by trunc(dbms_random.value(0, 1000))) where rownum Description:

sample(10) means to retrieve 10% of the data in the table. The sample value should be between [0.000001,99.999999], where sys_guid() and dbms_random.value are both internal functions

Note:

When using the sys_guid() method, sometimes the same records will be obtained, that is, the result set of the previous query is the same. This situation may be related to the operating system. For example, it is normal under Windows system and abnormal under Linux system. In addition, there may be problems with the sys_guid() function itself and further research is needed.

To ensure that the data read on different platforms are random, it is recommended to adopt two solutions: 2) and 3). Among them, the 2) solution is more commonly used, and the 3) solution is suitable for querying large tables and not extracting much data, and can improve the query speed.

How to randomly extract some records from a large data set

Oracle random reading method of N data in the table:

1

2

3

1) select * from (select * from tablename order by sys_guid()) where rownum 2) select * from (select * from tablename order by dbms_random.value) where rownum3) select * from (select * from table_name sample( 10) order by trunc(dbms_random.value(0, 1000))) where rownum

illustrate:

sample(10) means to retrieve 10% of the data in the table. The sample value should be between [0.000001,99.999999], where sys_guid() and dbms_random.value are both internal functions

Note:

When using the sys_guid() method, sometimes the same records will be obtained, that is, the result set of the previous query is the same. This situation may be related to the operating system. For example, it is normal under Windows system and abnormal under Linux system. In addition, there may be problems with the sys_guid() function itself and further research is needed.

To ensure that the data read on different platforms are random, it is recommended to adopt two solutions: 2) and 3). Among them, the 2) solution is more commonly used, and the 3) solution is suitable for querying large tables and not extracting much data, and can improve the query speed.

How to query a random record from a table in oracle

The need for work these days is to randomly extract a record from a relatively large table. Unlike MS SQLSERVER, Oracle can directly use Select TOP 1 * From TABLE Order By NewID () to efficiently randomize Find a record. After a lot of trouble, we used a table t_id with 900,000 records and only one gameid field. There is no index on this field. The table contains a series of data records from 100000 to 999999 for testing:

method 1.

Using rownum and dbms_random.value, the average time is 5 seconds. This efficiency is indeed too low. It should be OK for small tables, but it is not suitable for large tables.

declare

n_id number(6);

begin

SELECT gameid into n_id FROM(SELECT gameid FROM t_id T ORDER BY dbms_random.value()) WHERE ROWNUM=1;

dbms_output.put_line(to_char(n_id));

end;

/

Method 2.

Using oracle sample syntax, setting the random sample to 1%, the result takes about 0.01, which is quite fast. However, according to the official website, using the sample collection feature may produce inaccurate result sets. In my test, No incorrect results were encountered. But there is a problem, that is, the distribution of random results is very uneven, and the results are almost all stored in the 100000-200000 records. Although the efficiency is good, it does not achieve a good random effect. If the results are not very high, this method is quite good.

declare

n_id number(6);

begin

SELECT gameid into n_id FROM t_id SAMPLE (1) WHERE ROWNUM = 1;

dbms_output.put_line(to_char(n_id));

end;

/

Method 3.

Using minus syntax, first randomly obtain a random number within the range of the total number of records in the table, and then query the two result sets through rownum with only one record difference, and use minus to subtract the record with the random number in advance. The average time is about 1 second. The smaller the random number, the faster the query speed. When the random number is 20000, it takes 0.016 seconds. Although this method

It can get a very random effect, but the efficiency is not as good as this method 2. If the efficiency is average, you can still consider using it.

declare

n_count int:=0;

n_rand_num int:=0;

n_id number(6);

begin

SELECT COUNT(*) INTO n_count FROM t_id;

SELECT trunc(dbms_random.value(1,n_count 1)) INTO n_rand_num FROM DUAL;

select gameid into n_id from (SELECT gameid FROM t_id T WHERE rownum

minus

SELECT gameid FROM t_id T WHERE rownum

dbms_output.put_line(to_char(n_id));

end;

/

The above is the detailed content of How to randomly select a record from a table. For more information, please follow other related articles on the PHP Chinese website!