A speculation :
The sample clause is using the same functionality as DBMS_STATS for
ESTIMATE ?
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:b42t28$at5$1$8302bc10_at_news.demon.co.uk...
>
> 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."
> >
>
>
>
Received on Wed Mar 05 2003 - 04:57:36 CST
