Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Question regarding SAMPLE clause.
The Concepts and the SQL Ref aren't too clear on this.
I' like to know if SAMPLE is or is not guaranteed to return exact numbers.
I have a test table with 1,000,000 rows and issue a 50% sample clause Are the over/under results I'm getting acceptable i.e. SAMPLE not guaranteed to be exact or to within a specific percentage?
The docs (Metalink 95455.1) says of the SAMPLE option "reads a specified
percentage and
<...> examines it to see whether it satisfies the where clause "
On 9202
Stats fully up-to-date :
SQL> select count (*) from indiv;
COUNT(*)
1000000
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=52 Card=1) 1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT) 3 2 BITMAP INDEX (FAST FULL SCAN) OF 'INDIV_EXD_BM'
SQL> select /*+ FULL(INDIV) */ count (*) from indiv sample (50);
COUNT(*)
500155
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3343 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (SAMPLE) OF 'INDIV' (Cost=3343 Card=500000)
SQL> select count (*) from indiv sample (50);
COUNT(*)
499841
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=398 Card=1) 1 0 SORT (AGGREGATE)
2 1 INDEX (SAMPLE FAST FULL SCAN) OF 'IDX_INDIV_ADDRESS_NU' (NON-UNIQUE) (Cost=398 Card=500000)
or even
SQL> select count (*) from indiv sample block (50);
COUNT(*)
477533
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1) 1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT) 3 2 BITMAP INDEX (SAMPLE FAST FULL SCAN) OF 'INDIV_EXD_BM'
Y'know I've never seen them plan steps before; There's a first time for everything !
TIA Telemachus. Received on Tue Mar 04 2003 - 05:30:33 CST