| 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: num of blocks containing table data
A copy of this was sent to jurij.modic_at_mf.sigov.mail.si (Jurij Modic)
(if that email address didn't require changing)
On Tue, 09 Jun 1998 09:52:56 GMT, you wrote:
>On Tue, 09 Jun 1998 13:36:12 +0500, Umar FArooq
><umar.farooq_at_cressoft.com.pk> wrote:
>
>> Hi:
>>Is there a way to know how many blocks of a table actually contain data.
>>The blocks column of the user_tables gives teh high water mark blocks.
>>BAsically I wish to know how many extra blocks are being visited by
>>Oracle in case of a full table scan.
>>
>
>You can write a query to count all distinct "file and block sections"
>of rowids of that table. For Oracle7 you can use:
>
>SELECT COUNT(DISTINCT SUBSTR(rowid,1,8) || SUBSTR(rowid,15,4))
>FROM mytable;
>
>In Oracle8, the format of ROWID has changed, check the manuals about
>the exact format.
>
This might be too picky but...
I'm pretty sure the only way to get the number of blocks actually containing data is to analyze the table and look at BLOCKS and EMPTY_BLOCKS in the user_tables (or all_tables or dba_tables) views.
The problem with inspecting the rowid as above is that is probably misses blocks with chained rows (if you have longs or your rows > blocksize, you definitely will have chained rows) and migrated rows. Since a row can be on more then one block, the above method will only count blocks that have an initial row piece on them -- not all blocks that contain data...
>>Thx.
>>
>>Umar Farooq Chaudhri
>>umar.farooq_at_cressoft.com.pk
>
>Regards,
>============================================================
>Jurij Modic Republic of Slovenia
>jurij.modic_at_mf.sigov.mail.si Ministry of Finance
>============================================================
>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 Tue Jun 09 1998 - 15:43:33 CDT
 |
 |