While going through a thread in Oracle Forums, I came across ‘SAMPLE’ clause which is used in SELECT statement to fetch a specified percentage of rows from the full table. I tried to found out, it is there since Oracle 8i.
EXAMPLE:
SELECT *
FROM customers SAMPLE(1);
In the example statement, Oracle is instructed to randomly visit 1% of the rows in the table.
RESTRICTION:
This clause works for single table queries only. If you include the sample clause with a multiple table query, you will get a parse error or ORA-30561: SAMPLE option not allowed in statement with multiple table references.
There is one more option with this, SAMPLE BLOCK, which fetches specified number of table blocks rather than rows.
EXAMPLE:
SELECT *
FROM customers SAMPLE BLOCK (10) ;
In the example statement, Oracle is instructed to randomly visit 10% of blocks.
2 responses so far ↓
1 Charles Schultz // Dec 1, 2007 at 4:17 am
What about when you see a 2nd parameter, as shown in dynamic sampling:
SAMPLE BLOCK (.0314, 1)
2 Rajender Singh // Dec 1, 2007 at 9:41 am
Hi Charles,
Official Synatx is as follows:
SAMPLE [ BLOCK ] (sample_percent) [ SEED (seed_value) ]
Where
sample_percent
specify the percentage of the total row or block count to be included in the sample. The value must be in the range .000001 to, but not including, 100. This percentage indicates the probability of each row, or each cluster of rows in the case of block sampling, being selected as part of the sample. It does not mean that the database will retrieve exactly sample_percent of the rows of table.
SEED seed_value
Specify this clause to instruct the database to attempt to return the same sample from one execution to the next. The seed_value must be an integer between 0 and 4294967295. If you omit this clause, then the resulting sample will change from one execution to the next.
Regards,
Raj
Leave a Comment