Re: num of blocks containing table data

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Wed, 10 Jun 1998 09:34:52 GMT
Message-ID: <357e4901.6622064@www.sigov.si>

On Tue, 09 Jun 1998 20:43:33 GMT, tkyte_at_us.oracle.com (Thomas Kyte) wrote:

>This might be too picky but...
>
>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...

You are absolutely right (as usual) about the ROWID method missing the chained rows extra blocks. I completely forgot the chained rows.

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

Hm, I'm prety sure you are wrong about this one. BLOCKS column in USER/ALL/DBA_TABLES contains the information of the maximum number of blocks that were *ever* occupied in the particular table, i.e. highwatter mark. The same principle applies to the column EMPTY_BLOCKS. I checked this with various releases from 7.2.3 to 7.3.4, I don't know if the situation is changed in release 8.

Here is an example:

SQL> ANALYZE TABLE test COMPUTE STATISTICS;

Table analyzed.

SQL> SELECT num_rows, blocks, empty_blocks, chain_cnt 2 FROM user_tables WHERE table_name = 'TEST';

NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT --------- --------- ------------ ---------

5000 148 46 0

SQL> SELECT COUNT(DISTINCT SUBSTR(rowid,1,8)||SUBSTR(rowid,15,4)) blocks_occupied
2 FROM test;

BLOCKS_OCCUPIED

148

SQL> DELETE FROM test WHERE rownum <= 2500;

2500 rows deleted.

SQL> ANALYZE TABLE test COMPUTE STATISTICS;

Table analyzed.

SQL> SELECT num_rows, blocks, empty_blocks, chain_cnt 2 FROM user_tables WHERE table_name = 'TEST';

NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT --------- --------- ------------ ---------

2500 148 46 0

SQL> SELECT COUNT(DISTINCT SUBSTR(rowid,1,8)||SUBSTR(rowid,15,4)) blocks_occupied
2 FROM test;

BLOCKS_OCCUPIED

Also the DBMS_SPACE.UNUSED_SPACE procedure (as sugested by Jonathan Lewis) doesn't help here, because its out parameter TOTAL_BLOCKS returns the number of blocks *allocated* for the segment, not the number of blocks containing data. The parameter EMPTY_BLOCKS returns the same information as USER_TABLES.EMPTY_BLOCKS.

So my question is: does anyone know of a method to determine the number of table blocks that actualy contain data, taking into acount also the chained and migrated rows?

>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government
>Herndon VA

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 Received on Wed Jun 10 1998 - 04:34:52 CDT