Here is a very simple demonstration of a case where an Index can grow larger than the table. This happens because the pattern of data deleted and inserted doesn't allow deleted entries to be reused. For every 10 rows that are inserted, 7 rows are subsequently deleted after their status is changed to "Processed". But the space for the deleted entries from the index cannot be reused.
Note how the Index grew from 3 blocks to 17 blocks, larger than the table that grew to 13 and seemed to have reached a "steady-state" at 13 blocks.
The Index is built on only 2 of the 5 columns of the table and these two columns are also "narrow" in that they are a number and a single character. Yet it grows faster through the INSERT - DELETE - INSERT cycles.
Note the difference between the Index definition (built on TRANSACTION_ID as the leading column) and the pattern of DELETEs (which is on STATUS_FLAG).
Deleted rows leave "holes" in the index but these are entries that cannot be reused by subsequent
Inserts. The Index is ordered on TRANSACTION_ID. So if an Index entry for TRANSACTION_ID = n is deleted, the entry can be reused only for the same (or very close) TRANSACTION_ID.
Assume that an Index Leaf Block contains entries for TRANSACTION_IDs 1, 2, 3, 4 and so on upto 10. If rows for TRANSACTION_IDs 2,3,5,6,8 and 9 are deleted but 1,4,7 and 10 are not deleted then the Leaf Block has "free" space for new rows only with TRANSACTION_IDs 2,3,5,6,8 and 9. New rows with TRANSACTION_IDs 11 and above will take a new Index Leaf Block and not re-use the "free" space in the first Index Leaf Block. The first Leaf Block remains with deleted entries that are not reused.
On the other hand, when the rows are delete from the Table Block, new rows can be reinserted into the same Table Block. The Table is Heap Organised, not Ordered like the Index. Therefore, new rows are permitted to be inserted into any Block(s) that contain space for those new rows -- e.g. blocks from which rows are deleted. Therefore, after deleting TRANSACTION_IDs 2,3,5,6 from a Table Block, new TRANSACTION_IDs 11,12,13,14 can be re-inserted into the *same* Block.
.
.
.
SQL>
SQL>REM Demo Index growth larger than table !
SQL>
SQL>drop table hkc_process_list purge;
Table dropped.
SQL>
SQL>create table hkc_process_list
2 (transaction_id number,
3 status_flag varchar2(1),
4 last_update_date date,
5 transaction_type number,
6 details varchar2(25))
7 /
Table created.
SQL>
SQL>create index hkc_process_list_ndx
2 on hkc_process_list
3 (transaction_id, status_flag)
4 /
Index created.
SQL>
SQL>
SQL>REM Cycle 1 -------------------------------------
> -- create first 1000 transactions
SQL>insert into hkc_process_list
2 select rownum, 'N', sysdate, mod(rownum,4)+1, dbms_random.string('X',10)
3 from dual
4 connect by level < 1001
5 /
1000 rows created.
SQL>commit;
Commit complete.
SQL>
SQL>-- get sizes of table and index
SQL>exec dbms_stats.gather_table_stats('','HKC_PROCESS_LIST',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>select 'Table' Obj_Type, table_name, blocks Blocks
2 from user_tables
3 where table_name like 'HKC_PROCE%'
4 union
5 select 'Index', index_name, leaf_blocks
6 from user_indexes
7 where index_name like 'HKC_PROCE%'
8 order by 1
9 /
OBJ_T TABLE_NAME BLOCKS
----- ------------------------------ ----------
Index HKC_PROCESS_LIST_NDX 3
Table HKC_PROCESS_LIST 5
2 rows selected.
SQL>
SQL>-- change status flag for 70% of the transactions to 'P'
SQL>update hkc_process_list
2 set status_flag='P'
3 where mod(transaction_id,10) < 7
4 /
700 rows updated.
SQL>commit;
Commit complete.
SQL>
SQL>-- delete processed rows
SQL>delete hkc_process_list
2 where status_flag='P'
3 /
700 rows deleted.
SQL>commit;
Commit complete.
SQL>
SQL>REM Cycle 2 -------------------------------------
> -- insert another 1000 rows
SQL>insert into hkc_process_list
2 select rownum+1000, 'N', sysdate, mod(rownum,4)+1, dbms_random.string('X',10)
3 from dual
4 connect by level < 1001
5 /
1000 rows created.
SQL>commit;
Commit complete.
SQL>
SQL>-- get sizes of table and index
SQL>exec dbms_stats.gather_table_stats('','HKC_PROCESS_LIST',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>select 'Table' Obj_Type, table_name, blocks Blocks
2 from user_tables
3 where table_name like 'HKC_PROCE%'
4 union
5 select 'Index', index_name, leaf_blocks
6 from user_indexes
7 where index_name like 'HKC_PROCE%'
8 order by 1
9 /
OBJ_T TABLE_NAME BLOCKS
----- ------------------------------ ----------
Index HKC_PROCESS_LIST_NDX 7
Table HKC_PROCESS_LIST 13
2 rows selected.
SQL>
SQL>-- change status flag for 70% of the transactions to 'P'
SQL>update hkc_process_list
2 set status_flag='P'
3 where mod(transaction_id,10) < 7
4 /
700 rows updated.
SQL>commit;
Commit complete.
SQL>
SQL>-- delete processed rows
SQL>delete hkc_process_list
2 where status_flag='P'
3 /
700 rows deleted.
SQL>commit;
Commit complete.
SQL>
SQL>
SQL>REM Cycle 3 -------------------------------------
> -- insert another 1000 rows
SQL>insert into hkc_process_list
2 select rownum+2000, 'N', sysdate, mod(rownum,4)+1, dbms_random.string('X',10)
3 from dual
4 connect by level < 1001
5 /
1000 rows created.
SQL>commit;
Commit complete.
SQL>
SQL>-- get sizes of table and index
SQL>exec dbms_stats.gather_table_stats('','HKC_PROCESS_LIST',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>select 'Table' Obj_Type, table_name, blocks Blocks
2 from user_tables
3 where table_name like 'HKC_PROCE%'
4 union
5 select 'Index', index_name, leaf_blocks
6 from user_indexes
7 where index_name like 'HKC_PROCE%'
8 order by 1
9 /
OBJ_T TABLE_NAME BLOCKS
----- ------------------------------ ----------
Index HKC_PROCESS_LIST_NDX 11
Table HKC_PROCESS_LIST 13
2 rows selected.
SQL>
SQL>-- change status flag for 70% of the transactions to 'P'
SQL>update hkc_process_list
2 set status_flag='P'
3 where mod(transaction_id,10) < 7
4 /
700 rows updated.
SQL>commit;
Commit complete.
SQL>
SQL>-- delete processed rows
SQL>delete hkc_process_list
2 where status_flag='P'
3 /
700 rows deleted.
SQL>commit;
Commit complete.
SQL>
SQL>
SQL>REM Cycle 4 -------------------------------------
> -- insert another 1000 rows
SQL>insert into hkc_process_list
2 select rownum+3000, 'N', sysdate, mod(rownum,4)+1, dbms_random.string('X',10)
3 from dual
4 connect by level < 1001
5 /
1000 rows created.
SQL>commit;
Commit complete.
SQL>
SQL>-- get sizes of table and index
SQL>exec dbms_stats.gather_table_stats('','HKC_PROCESS_LIST',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>select 'Table' Obj_Type, table_name, blocks Blocks
2 from user_tables
3 where table_name like 'HKC_PROCE%'
4 union
5 select 'Index', index_name, leaf_blocks
6 from user_indexes
7 where index_name like 'HKC_PROCE%'
8 order by 1
9 /
OBJ_T TABLE_NAME BLOCKS
----- ------------------------------ ----------
Index HKC_PROCESS_LIST_NDX 15
Table HKC_PROCESS_LIST 13
2 rows selected.
SQL>
SQL>-- change status flag for 70% of the transactions to 'P'
SQL>update hkc_process_list
2 set status_flag='P'
3 where mod(transaction_id,10) < 7
4 /
700 rows updated.
SQL>commit;
Commit complete.
SQL>
SQL>-- delete processed rows
SQL>delete hkc_process_list
2 where status_flag='P'
3 /
700 rows deleted.
SQL>commit;
Commit complete.
SQL>
SQL>
SQL>REM Latest State size -------------------------
> -- get sizes of table and index
SQL>exec dbms_stats.gather_table_stats('','HKC_PROCESS_LIST',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>select 'Table' Obj_Type, table_name, blocks Blocks
2 from user_tables
3 where table_name like 'HKC_PROCE%'
4 union
5 select 'Index', index_name, leaf_blocks
6 from user_indexes
7 where index_name like 'HKC_PROCE%'
8 order by 1
9 /
OBJ_T TABLE_NAME BLOCKS
----- ------------------------------ ----------
Index HKC_PROCESS_LIST_NDX 17
Table HKC_PROCESS_LIST 13
2 rows selected.
SQL>
SQL>
Note how the Index grew from 3 blocks to 17 blocks, larger than the table that grew to 13 and seemed to have reached a "steady-state" at 13 blocks.
The Index is built on only 2 of the 5 columns of the table and these two columns are also "narrow" in that they are a number and a single character. Yet it grows faster through the INSERT - DELETE - INSERT cycles.
Note the difference between the Index definition (built on TRANSACTION_ID as the leading column) and the pattern of DELETEs (which is on STATUS_FLAG).
Deleted rows leave "holes" in the index but these are entries that cannot be reused by subsequent
Inserts. The Index is ordered on TRANSACTION_ID. So if an Index entry for TRANSACTION_ID = n is deleted, the entry can be reused only for the same (or very close) TRANSACTION_ID.
Assume that an Index Leaf Block contains entries for TRANSACTION_IDs 1, 2, 3, 4 and so on upto 10. If rows for TRANSACTION_IDs 2,3,5,6,8 and 9 are deleted but 1,4,7 and 10 are not deleted then the Leaf Block has "free" space for new rows only with TRANSACTION_IDs 2,3,5,6,8 and 9. New rows with TRANSACTION_IDs 11 and above will take a new Index Leaf Block and not re-use the "free" space in the first Index Leaf Block. The first Leaf Block remains with deleted entries that are not reused.
On the other hand, when the rows are delete from the Table Block, new rows can be reinserted into the same Table Block. The Table is Heap Organised, not Ordered like the Index. Therefore, new rows are permitted to be inserted into any Block(s) that contain space for those new rows -- e.g. blocks from which rows are deleted. Therefore, after deleting TRANSACTION_IDs 2,3,5,6 from a Table Block, new TRANSACTION_IDs 11,12,13,14 can be re-inserted into the *same* Block.
.
.
.
2 comments:
Hi ,
Thanks for this post.
A rebuild can fix this issue.
Cherif,
A REBUILD or a COALESCE. A one-time operation could be a REBUILD. A regular operation could be a COALESCE.
Hemant
Post a Comment