Tuesday, April 24, 2012

DBMS_ROWID and PCTFREE

This was tested in an Oracle 9 database. It works the same way in Oracle 11 although the group by statement no longer returns the results in block number order (see here for details). 
DBMS_ROWID lets you break down a row's rowid to see the file number and block number where that row is stored:

SQL> create table andrew as
  2  select sysdate todays_date from dual
  3  /

Table created.

SQL> select dbms_rowid.rowid_relative_fno(rowid)
  2  from andrew
  3  /

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------
                                  22

SQL> select dbms_rowid.rowid_block_number(rowid)
  2  from andrew
  3  /

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                2838

SQL> drop table andrew
  2  /

Table dropped.

SQL>

You can use this information to see the effect of the PCTFREE clause when you create a table. This specifies the percentage of each block which should be left free for updates to existing rows in a table. If it is set to zero, no space is left for updates. This means that the table occupies fewer blocks. In the first example below, table ANDREW fits into 6 blocks:

SQL> create table andrew
  2  pctfree 0
  3  as select owner, table_name
  4  from dba_tables
  5  /

Table created.

SQL> select
  2  dbms_rowid.rowid_relative_fno(rowid) as file_num,
  3  dbms_rowid.rowid_block_number(rowid) as block_num,
  4  count(*)
  5  from andrew
  6  group by
  7  dbms_rowid.rowid_relative_fno(rowid),
  8  dbms_rowid.rowid_block_number(rowid)
  9  /

  FILE_NUM  BLOCK_NUM   COUNT(*)
---------- ---------- ----------
        22       2838        204
        22       2839        146
        22       2840        137
        22       2841        158
        22       2842        134
        22       2843         56

6 rows selected.

SQL> drop table andrew
  2  /

Table dropped.

SQL>

In the next example, table ANDREW contains the same data but with a PCTFREE of 25. This means that it occupies more blocks (8 in all) with fewer rows in each:

SQL> create table andrew
  2  pctfree 25
  3  as select owner, table_name
  4  from dba_tables
  5  /

Table created.

SQL> select
  2  dbms_rowid.rowid_relative_fno(rowid) as file_num,
  3  dbms_rowid.rowid_block_number(rowid) as block_num,
  4  count(*)
  5  from andrew
  6  group by
  7  dbms_rowid.rowid_relative_fno(rowid),
  8  dbms_rowid.rowid_block_number(rowid)
  9  /

  FILE_NUM  BLOCK_NUM   COUNT(*)
---------- ---------- ----------
        22       2838        156
        22       2839        119
        22       2840        111
        22       2841        100
        22       2842        120
        22       2843        107
        22       2844         98
        22       2845         24

8 rows selected.

SQL> drop table andrew
  2  /

Table dropped.

SQL>

In the final example, the table has a PCTFREE of 50 so half of each block is reserved for updates to existing rows. This time it uses 11 blocks:

SQL> create table andrew
  2  pctfree 50
  3  as select owner, table_name
  4  from dba_tables
  5  /

Table created.

SQL> select
  2  dbms_rowid.rowid_relative_fno(rowid) as file_num,
  3  dbms_rowid.rowid_block_number(rowid) as block_num,
  4  count(*)
  5  from andrew
  6  group by
  7  dbms_rowid.rowid_relative_fno(rowid),
  8  dbms_rowid.rowid_block_number(rowid)
  9  /

  FILE_NUM  BLOCK_NUM   COUNT(*)
---------- ---------- ----------
        22       2838        105
        22       2839         98
        22       2840         71
        22       2841         75
        22       2842         66
        22       2843         70
        22       2844         76
        22       2845         80
        22       2846         70
        22       2847         64
        22       2848         60

11 rows selected.

SQL>

No comments: