| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Block count usage by a table
On Tue, 7 Jul 1998 10:37:46 +0100, "MotoX" <rat_at_tat.a-tat.com> wrote:
>
>johnvue_at_gte.net wrote in message <6nq5uq$kt3$1_at_news-1.news.gte.net>...
>>Let's say you had a table with one row and the row was SO WIDE that it
>>was chained across 20 blocks. Woudln't the SELECT query below return
>>only one block instead of 20 blocks?!?!
>
>
>No, if it required 20 blocks, it would use 20 blocks. If the current extent
>didn't have 20 blocks left, a new extent would be added to that segment,
>which might be, say, another 10 blocks, or 100, or 1000 (plus the block
>rounding of 5 blocks + 1 block, if the allocation request was 5 blocks or
>greater).
>
>Have a look at the Oracle Concepts manual for how blocks are allocated. It
>ain't simple! :-)
The question was not how many blocks would the chained row require but how many block will the query with SUBSTR's on ROWID report as being used. And johnvue_at_gte.net is right about that, it'll count only 1 block.
>>
>>At least one book mentions that the SELECT query is faster than doing
>>a ANALYZE TABLE for finding out block count.
>
>I have a 100,000,000 row table in my db. That select would take a mightly
>long time...
True, but analyzing that same table (with COMPUTE STATISTICS) will very likely take a much longer time....
>And remember, the ANALYZE command isn't just for giving *you* data, it's for
>giving the *CBO* data.
Could not agree more. In any case, whene there are chained rows in a table neither "ANALYZE TABLE" nor the "SELECT COUNT on distinct ROWID portions" will give you the correct number of blocks being used by the data.
>MotoX.
Regards,
--
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |