Re: Block count usage by a table

From: Jurij Modic <jmodic_at_src.si>
Date: Wed, 08 Jul 1998 08:23:22 GMT
Message-ID: <35a328e9.180491402@news.siol.net>

On Mon, 6 Jul 1998 11:13:09 +0200, "Reiner Neumann" <reiner.neumann_at_messe.de> wrote:

>easier and accurate will be another way:
>
>analyze table sometable compute statistics;
>
>select table_name, num_rows, blocks, empty_blocks from user_tables
>where table_name = 'SOMETABLE';

Nope, this doesn't give you the number of blocks actualy containig rows, it merely shows you a highwattermark for that table. There was a discusion about this topic a month ago in this ng and aparently there is no obvious method to get the exact count of *occupied* blocks in a table where there are chained rows involved.

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

---

             75

>Regards,
>Reiner Neumann

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 Received on Wed Jul 08 1998 - 03:23:22 CDT