Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Question regarding SAMPLE clause.

Question regarding SAMPLE clause.

From: Telemachus <telemachus_at_ulysseswillreturn.net>
Date: Tue, 4 Mar 2003 11:30:33 -0000
Message-ID: <x709a.245$pK2.506@news.indigo.ie>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US