| 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
A copy of this was sent to "MotoX" <rat_at_tat.a-tat.com>
(if that email address didn't require changing)
On Wed, 8 Jul 1998 15:35:57 +0100, you wrote:
>
>Jurij Modic wrote in message <35a32cae.181456931_at_news.siol.net>...
>>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.
>
>
>It depends on the SUBSTR you use - b.r.f. If you looked on the 'r' portion,
>then you would get back *all* the blocks for the row. But true, the
>particular select given might not do this - which was why I recommended
>ANALYZE.
That doesn't make sense -- there is only 1 rowid in the entire table in this
example, even if there are 20 blocks consumed by this row -- there is only ONE
rowid to ever substr on... It matters not what function you use on this rowid,
it will always be 1 row -- 1 block apparently.
There is no way to get a rowid that points to a block that does not have an initial row piece on it -- you can't count them no matter how hard you try.
If you feel this is wrong, please post a query that shows 20 blocks being consumed by 1 row using a rowid query....
>
>>
>>>>
>>>>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....
>
>
>But then who would be daft enough to COMPUTE rather than ESTIMATE on such a
>large table?..
>
flame bait...
>>
>>>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)
>>================================================
>>The above opinions are mine and do not represent
>>any official standpoints of my employer
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jul 08 1998 - 11:10:37 CDT
 |
 |