Randomly extracting N Records in Oracle

First,Oracletake random Data
1,Oraclebasic ways to access data:
1), full table scan: Perform a full table scan, Oracle reads all the records in the table, and examines whether each row satisfies the where condition. Oracle sequential reads are assigned to each block of data in the table, and each block of data is read once by Oracle. Such a full table scan can benefit from multiple reads.

2), sample table scan (sample table Scan): Scans the returned table for random sampled data, which needs to include the sample option or the sample block option in the FROM statement.

Note: Oracle provides sampling table scanning features from oracle8i


2, usingSampleget a random result set
2.1. Syntax: SAMPLE [BLOCK] (sample_percent) [SEED (Seed_value)]
Sample option: Indicates that a full table scan is performed by line sampling, and Oracle reads a specific percentage of records from the table and determines whether the WHERE clause is satisfied to return the result.
Block: Represents the use of random block examples rather than random row examples.
Sample_percent: A percentage of the records in a table are randomly obtained. For example, the value is 10, which is the random 10 record in the table.
Value must be greater than or equal to. 000001, less than 100.
SEED: Indicates which record to return from, similar to the pre-set example results, so each return result is fixed. The value must be between 0 and 4294967295.
  
2.2. Examples and explanations
To create a test staging table: 

SQL Code   

1. sql> Select dbms_random.value () from dual;

2.

3. Dbms_random. VALUE ()

4.-------------------

5.0.146123095968043

6.

7. sql> Select dbms_random.value () from dual;

8.

9. Dbms_random. VALUE ()

Ten.-------------------

11.0.90175764902345

SQL Code   

1. sql> Select dbms_random.value (1,10) from dual;

2.

3. Dbms_random. VALUE (1,10)

4.-----------------------

5.9.86601968210438

6.

7. sql> Select dbms_random.value (1,10) from dual;

8.

9. Dbms_random. VALUE (1,10)

Ten.-----------------------

11.3.43475105499398

3.2. Examples and explanations

SQL Code   

1. sql> Select * from (select object_name from Zeeno order by DB Ms_random.random) where rownum<6;

2.

3. object_name

4.--------------------------------------------------------------------------------

5./6dd0fe0e_certificatecertifica

6./cf5224d7_sunjsse_a4

7. Ku$_parsed_items

8. Javax/swing/text/iconview

9. oracle/xml/jdwp/xsljdwpstring

10.

11.sql> Select * from (select object_name from Zeeno order by Dbms_random.random) where rownum<6;

12.

13.object_name

--------------------------------------------------------------------------------

15.java/io/objectoutputstream$1

16.sun/security/krb5/krbasreq

17./2d52a21c_last

18.sys_yoid0000006594$

19./308fbfa1_beancontextservices

SQL Code   

1. sql> Select * from (select object_name from Zeeno order by tr UNC (Dbms_random.value (1,3))) where rownum<6;

2.

3. object_name

4.--------------------------------------------------------------------------------

5. icol$

6. c_cobj#

7. proxy_role_data$

8. i_obj#

9. uet$

10.

11.sql> Select * from (select object_name from Zeeno order by Trunc (Dbms_random.value (1,3))) where rownum<6;

12.

13.object_name

--------------------------------------------------------------------------------

15.icol$

16.undo$

17.i_proxy_role_data$_1

18.i_cdef2

19.uet$

SQL Code   

1. sql> Select trunc (dbms_random.value (0, +)) Randomnum from dual; --(An integer of 0-1000)

2.

3. Randomnum

4.----------

5.790

6.

7. sql> Select dbms_random.value (0,) randomnum from dual; --(0-1000 of floating-point numbers)

8.

9. Randomnum

Ten.----------

11.997.876726

4 , using intrinsic Functions Sys_guid ()

SQL Code   

1. sql> Select * from (select object_name from Zeeno order by Sys_guid ()) where rownum < 6;

2.

3. object_name

4.--------------------------------------------------------------------------------

5./6bedadd5_keymanagerfactory1

6./ffd795c8_addcrif

7. Table_export_objects

8./278cd3a4_cgparselet

9. ku$_refcol_t

10.

11.sql> Select * from (select object_name from Zeeno order by S Ys_guid ()) where rownum < 6;

12.

13.object_name

--------------------------------------------------------------------------------

15.sun/awt/inputmethodsupport

16.v_$restore_point

17.COLORSLIST

18.java/util/weakhashmap$entry

19.dbmsoutput_linesarray

Note :

When using Sys_guid () This method, sometimes get to the same record, that is, the result set of the previous query is the same, to find relevant data, some say that the operating system, under the Windows platform normal, the obtained data is random, And in Linux and other platforms are always the same constant data set, some say because the sys_guid () function itself, that is, Sys_guid () will generate a 16-byte globally unique identifier on the query, This identifier consists of a host identifier and the thread identifier of the process or process on most platforms, which means that it is likely to be random, but it does not necessarily mean that it is absolutely absolute.

Therefore, in order to ensure that the data read each time on different platforms is random, most of us use the sample function or Dbms_random package to obtain a random result set, which uses the sample function is more commonly used, because its query narrowed the query scope, in the query large table, And to extract the data is not very few cases, the query speed will be significantly improved.

Second, the other database randomly removed N Records:

1 , SQL Server randomly extracting database records in

Select Top N * from table order by NEWID ()

--------------------------------------------------------------------------------
Select Top * FROM tablename order Bynewid ()
Select Top * FROM tablename ORDER by NEWID ()

2 , MySQL randomly extracting database records in

Select * FROM Table ORDER by rand () Limit n

-------------------------------------------------------------------------------
SELECT * FROM TableName ORDER by rand () limit 10
SELECT * FROM TableName ORDER by rand () limit 10

3 , Access randomly extracting database records in

Select Top n * from table OrDER by Rnd (ID)

-------------------------------------------------------------------------------
SELECT Top * FROM tablename ORDER byrnd (FId)
SELECT Top * FROM tablename ORDER by Rnd (FId)
FID: The ID field name for your current table




Reprint Source http://kb.cnblogs.com/a/1443619/
Reprint Source http://hpitcn.iteye.com/blog/676330

Randomly extracting N Records in Oracle