Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Block count usage by a table

Re: Block count usage by a table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 08 Jul 1998 16:10:37 GMT
Message-ID: <35a79910.11853163@192.86.155.100>


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  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US