Counting Rows
BY Jonathan Lewis
Here's another little utility I use from time to time (usually for small tables) to check how many rows there are in each block of the table, and which blocks are used. It doesn't DO anything clever, just call routines IN the DBMS_ROWID PACKAGE FOR each ROWID IN the TABLE:


rem
rem     Rowid_count.SQL
rem     Generic code TO COUNT rows per block IN a TABLE
rem     Ordered BY file AND block
rem

define m_table = '&1'

spool rowid_count

SELECT
        DBMS_ROWID.rowid_relative_fno(ROWID)    rel_file_no,
        DBMS_ROWID.rowid_block_number(ROWID)    block_no,
        COUNT(*)                                rows_starting_in_block
FROM
        &m_table        t1
GROUP BY
        DBMS_ROWID.rowid_relative_fno(ROWID),
        DBMS_ROWID.rowid_block_number(ROWID)
ORDER BY
        DBMS_ROWID.rowid_relative_fno(ROWID),
        DBMS_ROWID.rowid_block_number(ROWID)
;


SELECT
        rows_starting_in_block,
        COUNT(*)        blocks
FROM
        (
        SELECT
                DBMS_ROWID.rowid_relative_fno(ROWID),
                DBMS_ROWID.rowid_block_number(ROWID),
                COUNT(*)                                rows_starting_in_block
        FROM
                &m_table        t1
        GROUP BY
                DBMS_ROWID.rowid_relative_fno(ROWID),
                DBMS_ROWID.rowid_block_number(ROWID)
        )
GROUP BY
        rows_starting_in_block
ORDER BY
        rows_starting_in_block
;

spool off