Amount of rows that could be stored in a block is affected by the value set for pctfree. If the pctfree is high the row count in a block goes down (useful in reducing hot blocks) and when pctfree is low the row count inside the block goes high (useful for full table scans). Size of the block and the length of the row also plays a part in the row count inside the block.
Following is a simple test to illustrate this point. Two tables are created identical in all aspects except the pctfree value. Both reside in a tablespace which has a block size of 2K.
create table pct0 (id varchar2(100)) tablespace tbs2k pctfree 0; create table pct99 (id varchar2(100)) tablespace tbs2k pctfree 99; SQL> select table_name,pct_free from user_tables where table_name in ('PCT0','PCT99'); TABLE_NAME PCT_FREE ------------------------------ ---------- PCT99 99 PCT0 0Following PL/SQL code block will insert 100 rows to each table
declare x varchar2(40); begin for i in 1 .. 100 loop x := dbms_random.string('x',36); insert into pct0 values (x); insert into pct99 values (x); commit; end loop; end; /There are two ways to get the row count inside a block either by using dbms_rowid package or decoding the rowid.
Using dbms_rowid package on pct0 table
SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT", 2 DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE", 3 DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",count(*) from pct0 4 group by DBMS_ROWID.ROWID_OBJECT(rowid), DBMS_ROWID.ROWID_RELATIVE_FNO(rowid), DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid); OBJECT FILE BLOCK COUNT(*) ---------- ---------- ---------- ---------- 103409 14 206983 100which tells that all the rows are in one block. Querying the pct99 table
SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT", 2 DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE", 3 DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",count(*) from pct99 4 group by DBMS_ROWID.ROWID_OBJECT(rowid), DBMS_ROWID.ROWID_RELATIVE_FNO(rowid), DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid); OBJECT FILE BLOCK COUNT(*) ---------- ---------- ---------- ---------- 103411 14 206998 1 103411 14 207007 1 103411 14 207014 1 103411 14 207017 1 103411 14 207041 1 103411 14 207049 1 .. .. .. OBJECT FILE BLOCK COUNT(*) ---------- ---------- ---------- ---------- 103411 14 207098 1 100 rows selected.which tells that each row is stored in single block and 100 blocks are needed to store all the rows.
select rows_per_block,count(*) from (select substr(rowid,10,6) as block,count(*) as rows_per_block from pct0 group by substr(rowid,10,6)) group by rows_per_block order by 1; ROWS_PER_BLOCK COUNT(*) -------------- ---------- 100 1 select rows_per_block,count(*) from (select substr(rowid,10,6) as block,count(*) as rows_per_block from pct99 group by substr(rowid,10,6)) group by rows_per_block order by 1; ROWS_PER_BLOCK COUNT(*) -------------- ---------- 1 100As more blocks are used the space usage will also increase compared to pct0 table above.
SQL> select segment_name as table_name, bytes/1024 as "Size KB" from user_segments where segment_name IN ('PCT0','PCT99'); TABLE_NAME Size KB ---------- ---------- PCT0 64 PCT99 896Multiple factors must be considered when setting the pctfree value such as the trade-offs between increase in storage vs reduction in IO (in FTS), the nature of access (FTS vs single block access) to the segment.