Re: Question regarding SAMPLE clause.

I'm always very loath to contradict Tom, but a fairly simple test shows that this is not the whole picture, although perhaps for a relatively small table, or relatively large sample, it is accurate.

My immediate response to this was "can't be right, that would require a full tablescan or an index fast full scan to get the result". Since the SAMPLE() clause is supposed to be a quick way of getting an approximation, this doesn't seem reasonable.

So, test case:

Create a table with 10,000 blocks, 70 rows to a block, 8K block size, oracle 9.2.0.2, db_cache_size = 12M (1,500 blocks).

select count(small_col) from t1 sample (0.01).

Check v$sesstat before and after, repeat with trace 10046 , level 8.

Results: Oracle did multiblock reads of the table, 8 blocks at a time (my db_file_mbrc), but skipped its way along the table leaving fairly random gaps. Total number of blocks read ca. 1,200 - not 10,000.

The read costs are MUCH higher than I
would have guessed - on 700,000 rows
I would have hoped for a maximum of
70 blocks to be read (or perhaps 70
read requests)

In fact, on my first test case, with a sample size of 0.1 percent, I did get a full tablescan, which is why I took the sample down to 0.01.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____UK_______April 8th
____UK_______April 22nd

____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message
news:m359a.318$pK2.582_at_news.indigo.ie...


> Duhhh... Mr. Kyte had the answer

>

> for a 5% sample :

> "Think of it like this, we goto each row -- we generate a random

number


> between

> 0.00 and 1.00.  If the number if 0.05 or less -- then the row is

output.  If


> the

> number is above 0.05 we don't output it."

>