N Records in the Oracle random table

Sampling 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. Use sample to get 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

Sql>create table Zeeno as SELECT * from Dba_objects;

1), sample (Sample_percent):

SQL code

-- random 10% records from "Full table scan" in table Zeeno, random query of 5 records

Sql>select object_name from Zeeno sample () where rownum<6;

object_name

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

uet$

view$

I_superobj2

triggercol$

I_view1

Sql&gt; /

object_name

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

I_file1

ind$

clu$

fet$

i_cobj#

2),sample block (sample_percent)

SQL code

-- random 10% records from "Sample Table Scan" in table Zeeno, random query of 5 records

Sql> Select object_name from Zeeno sample block (Ten) where rownum<6;

Www.2cto.com

object_name

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

Urifactory

Dbms_xmlgen

Dbms_xmlgen

Dbms_xmlstore

Dbms_xmlstore

3),sample block (sample_percent) seed (seed_value)

SQL code

-- use seed to return a fixed result set. From the table Zeeno "sampling table scan" randomly extracts 10% of records, randomly query 5 records.

Sql> Select object_name from Zeeno sample () seed (ten) where rownum<6;

object_name

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

uet$

I_con1

I_file2

fet$

I_col1

Sql&gt; Select object_name from Zeeno sample (Ten) seed where rownum&lt;6;

object_name

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

uet$

I_con1

I_file2

fet$

I_col1

Note the following points:

1.sample is only valid for single tables and cannot be used for table joins and remote tables

2.sample will make SQL automatically use the CBO

N Records in the Oracle random table