Oracle sample syntax

I posted a post these two days to find a better way to randomly retrieve a record from the table.

See: http://www.oracle.com.cn/viewthread.php? Tid = 20848

 

This article introduces the application of Oracle sample syntax. using sample is also a solution, but it is not very good, mainly because the returned records are not evenly distributed, or it depends on some of the preceding tables, or no record is returned.
The following is an example of the sample Syntax:
Select record 10%
Select * from T1 sample (10)
Select record 0.1%
Select * from T1 sample (0.1)
Select 1% records based on data blocks
Select * from T1 sample block (1)
The difference between using data block selection and using record rows: using data block selection indicates that sample collection is based on data blocks. That is to say, if a sample is collected as a sample, all records in the data block are samples.

Sample statistics are collected based on statistics. There is a probability problem and it may not be completely accurate. For example, if you want to retrieve 50% of records, but it may actually return 49% of the records to you, it may also return 51% of the record sets to you.

For example
If table T1 has data blocks b1, b2
B1 records R1, R2, R3, R4, R5
B2 records R6, R7, R8, R9, R10
If you use the following SQL statement to select 50% of the data
Select * from T1 sample block (50)
The returned result may be a record of data block B1.
R1, R2, R3, R4, R5
It may also be a data block B2 record.
R6, R7, R8, R9, R10
Or the record set may not be returned.

If you use the following SQL statement to select 50% of the data
Select * from T1 sample (50)
The returned result may be
R2, R3, R5, R8, R9
It may also look like the following:
R1, R3, R4, R8

Application Example:
Randomly retrieve one record from the table. The probability of selecting a record is 1%.
Select * from T1 sample (1) where rownum = 1
Randomly retrieve 10 records from the table. The probability of selecting records is 0.1%.
Select * from T1 sample (0.1) Where rownum <= 10
Note: The lower the probability of selection, the more records will be accessed to the table.

Oracle reference manual:
Sample_clause
The sample_clause lets you instruct Oracle to select from a random sample of rows from the table, rather than from the entire table.

Block
Block instructs Oracle to perform random block sampling instead of random row sampling.

Sample_percent
Sample_percent is a number specifying the percentage of the total row or block count to be sorted in the sample. The value must be in the range. 000001 to (but not including) 100.
Restrictions on sampling during queries
You can specify sample only in a query that selects from a single table. joins are not supported. however, you can achieve the same results by using a create table... as SELECT query to materialize a sample of an underlying table and then rewrite the original query to refer to the newly created table sample. if you wish, you can write additional queries to materialize samples for other tables.
When you specify sample, Oracle automatically uses cost-based optimization. Rule-based optimization is not supported with this clause.
--------------------------------------------------------------------------------
Caution:
The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results.
--------------------------------------------------------------------------------

Translation:
Sample options
Using the sample option means specifying Oracle to randomly select record samples from the table, which is more efficient than selecting from the entire table.
Block options
When the Block option is added, the data block is randomly retrieved, rather than the record row.
Sample_percent Option
Sample_percent is the percentage value of the data sample for the specified total record row or data block. The value can only be between 0.000001 and 100, and cannot be equal to 100.
Restrictions
You can only specify the sample option in the SQL statement for a single table query, but do not support queries that hold connections. However, you can use the create table... as SELECT query syntax to achieve the same effect, and then rewrite the query SQL statement using the new sample table.
When you specify the sample, the optimization rule based on the Rule (rule) is not supported. Oracle automatically uses the optimization rule of the basic cost (cost.
Note:
The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results.
This sentence is not quite understandable. It is estimated that using the sample collection feature may produce inaccurate result sets.