Re: Block count usage by a table

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! :-)

>
>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...

And remember, the ANALYZE command isn't just for giving *you* data, it's for giving the *CBO* data.

MotoX.

>
>
>On Mon, 6 Jul 1998 08:15:07 +0100, "MotoX" <rat_at_tat.a-tat.com> wrote:
>
>>Er, why? The blocks would still be assigned to the table/index in
question.
>>
>>But sure, the data is not necessarily 'packed' tight into the 'least
number
>>of blocks'. But then that's a different matter. Storage clauses will have
an
>>effect on the packing or rows in a table, as will the 'staleness' of
indexes
>>and their storage parameters. Likewise, lots of chained migrated rows will
>>have an effect.
>>
>>I'd use ANALYSE over the technique listed below for a better picture -
>>unless you are on an old Oracle release.
>>
>>MotoX.
>>
>>johnvue_at_gte.net wrote in message <6npf4n$8aa$1_at_news-1.news.gte.net>...
>>>I see the following query mentioned to find out how many blocks a
>>>table is using up:
>>>
>>>select COUNT(DISTINCT(SUBSTR(ROWID,1,8)||SUBSTR(ROWID,15,4)))
>>> Blocks_Used
>>> from sometable;
>>>
>>>
>>>If most/all of the rows are chained/migrated, won't this query be
>>>inaccurate?
>>
>
Received on Tue Jul 07 1998 - 04:37:46 CDT