Indexing helps to improve performance for databases query. And most of the DBA believe that rebuilding indexes also help to increase the performance and they are rebuilding indexes at a regular period of time.
But most of the cases this is not true and very rare case index rebuild is required or helped on performance.
In this article I will discuss how Index changes or works internally when we perform DML.
Most of cases, Rebuilding indexes are not required and will not help on performance but just extra work on database.
How many DBA measure the performance before and after rebuilding the Indexes on database?
Ans. : Near 100% cases no measurement.
Delete and insert similar data into table:
If deleted and inserted records are same in table, then rebuild is not requited. On below example, you will find Number of leaf rows and number of blocks are same.
SQL> CREATE TABLE HR.TBL_IDXTEST AS (SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 25000);
Table created.
SQL> CREATE INDEX HR.IDX_OBJECT_ID ON HR.TBL_IDXTEST(OBJECT_ID);
Index created.
SQL> SELECT LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
no rows selected
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> BEGIN
FOR i IN 1 .. 5000 LOOP
DELETE FROM HR.TBL_IDXTEST WHERE OBJECT_ID = i*5;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SQL>
SQL> COMMIT;
Commit complete.
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ----------- ---------- ----------
55 1 4372 64 88
SQL> BEGIN
FOR i IN 1 .. 5000 LOOP
INSERT INTO HR.TBL_IDXTEST (SELECT * FROM ALL_OBJECTS WHERE OBJECT_ID = i*5);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ----------- ---------- ----------
57 1 0 64 86Delete and insert very similar data into table:
If deleted and inserted records are kind of similar, then rebuild is not requited. For example, if you delete row which id is 5 and insert 4 or 6 then no much changes on index and new record will insert into same leaf of rows. On below example, you will find Number of leaf rows and number of blocks are same.
SQL> DROP TABLE HR.TBL_IDXTEST CASCADE CONSTRAINTS;
Table dropped.
SQL> CREATE TABLE HR.TBL_IDXTEST AS (SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID <= 25000);
Table created.
SQL> SELECT COUNT(*), MIN(OBJECT_ID), MAX(OBJECT_ID) FROM HR.TBL_IDXTEST;
COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
24507 2 25000
SQL> CREATE INDEX HR.IDX_OBJECT_ID ON HR.TBL_IDXTEST(OBJECT_ID);
Index created.
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ----------- ---------- ----------
54 1 0 64 87SQL> DELETE FROM HR.TBL_IDXTEST WHERE MOD(OBJECT_ID,5)=0;
4901 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ----------- ---------- ----------
54 1 4901 64 87
SQL> INSERT INTO HR.TBL_IDXTEST (SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID IN (SELECT OBJECT_ID+2 FROM DBA_OBJECTS WHERE MOD(OBJECT_ID,5)=0 AND OBJECT_ID <= 25000));
4886 rows created.
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ----------- ---------- ----------
55 1 0 64 86
Delete and Insert Randomly:
Even if you delete and insert lots of data randomly after certain time, you will see there is no changes on number of leaf rows. So even if you see lots of changes on table but not changing much on index structure then rebuild may not required. On below example, you will find Number of leaf rows and number of blocks are same.
SQL> CREATE TABLE HR.TBL_IDXTEST AS (SELECT OBJECT_ID FROM ALL_OBJECTS WHERE OBJECT_ID < 50000);
Table created.
SQL> CREATE INDEX HR.IDX_OBJECT_ID ON HR.TBL_IDXTEST(OBJECT_ID);
Index created.
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ----------- ---------- ----------
107 1 0 120 89Procedure to insert and delete random values in a table:
SQL> CREATE OR REPLACE PROCEDURE DEL_INS_ROWS AS
VAL NUMBER;
C NUMBER := 0;
BEGIN
LOOP
VAL := TRUNC(DBMS_RANDOM.VALUE(1,50000));
DELETE FROM HR.TBL_IDXTEST WHERE OBJECT_ID=VAL AND ROWNUM=1;
IF SQL%FOUND THEN
COMMIT;
VAL := TRUNC(DBMS_RANDOM.VALUE(1,50000));
INSERT INTO HR.TBL_IDXTEST VALUES(VAL);
C := C + 1;
EXIT WHEN C = 100000;
END IF;
END LOOP;
COMMIT;
END;
/
Trigger the procedure 20 times which will delete and insert 2 millions of times.
DECLARE
C NUMBER := 0;
BEGIN
LOOP
DEL_INS_ROWS;
C:=C+1;
EXIT WHEN C=20;
END LOOP;
COMMIT;
END;
/
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT HEIGHT, LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
HEIGHT LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ---------- ----------- ---------- ----------
2 181 1 84 256 53
#Again call the procedure - DEL_INS_ROWS for 20 times.
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT HEIGHT, LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
HEIGHT LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ---------- ----------- ---------- ----------
2 194 1 98 256 50
SQL> DECLARE
C NUMBER := 0;
BEGIN
LOOP
DEL_INS_ROWS;
C:=C+1;
EXIT WHEN C=20;
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT HEIGHT, LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
HEIGHT LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ---------- ----------- ---------- ----------
2 197 1 105 256 49
In this stage, LF_BLKS is not changing.
SQL> SELECT HEIGHT, LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
HEIGHT LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ---------- ----------- ---------- ----------
2 197 1 109 256 49
2 197 1 92 256 49
2 197 1 104 256 49
2 197 1 94 256 49
2 197 1 93 256 49
4.
SQL> CREATE TABLE HR.TBL_IDXTEST AS (SELECT OBJECT_ID FROM DBA_OBJECTS);
Table created.
SQL> CREATE INDEX HR.IDX_OBJECT_ID ON HR.TBL_IDXTEST(OBJECT_ID);
Index created.
SQL> SELECT COUNT(*) FROM HR.TBL_IDXTEST;
COUNT(*)
----------
91136
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT HEIGHT, LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
HEIGHT LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ---------- ----------- ---------- ----------
2 202 1 0 256 90
SQL> INSERT INTO HR.TBL_IDXTEST (SELECT * FROM HR.TBL_IDXTEST);
91136 rows created.
SQL> SELECT COUNT(*) FROM HR.TBL_IDXTEST;
COUNT(*)
----------
729088
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT HEIGHT, LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
HEIGHT LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ---------- ----------- ---------- ----------
3 1659 5 0 1792 88
SQL> CREATE OR REPLACE PROCEDURE DEL_INS_ROWS AS
VAL NUMBER;
C NUMBER := 0;
BEGIN
LOOP
VAL := TRUNC(DBMS_RANDOM.VALUE(1,90000));
DELETE FROM HR.TBL_IDXTEST WHERE OBJECT_ID=VAL AND ROWNUM=1;
IF SQL%FOUND THEN
COMMIT;
VAL := TRUNC(DBMS_RANDOM.VALUE(1,90000));
INSERT INTO HR.TBL_IDXTEST VALUES(VAL);
C := C + 1;
EXIT WHEN C = 100000;
END IF;
END LOOP;
COMMIT;
END;
/
Procedure created.
SQL> DECLARE
C NUMBER := 0;
BEGIN
LOOP
DEL_INS_ROWS;
C:=C+1;
EXIT WHEN C=30;
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT HEIGHT, LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
HEIGHT LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ---------- ----------- ---------- ----------
3 2582 6 1301 2688 57
SQL> DELETE FROM HR.TBL_IDXTEST WHERE OBJECT_ID < 50000;
399094 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT HEIGHT, LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
HEIGHT LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ---------- ----------- ---------- ----------
3 2582 6 399672 2688 57
SQL> DECLARE
C NUMBER := 100000;
BEGIN
LOOP
INSERT INTO HR.TBL_IDXTEST VALUES (C+10000);
C:=C+1;
EXIT WHEN C=3000000;
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT HEIGHT, LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
HEIGHT LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ---------- ----------- ---------- ----------
3 7233 14 578 7424 93
SQL> ALTER INDEX HR.IDX_OBJECT_ID REBUILD ONLINE;
Index altered.
SQL> ANALYZE INDEX HR.IDX_OBJECT_ID VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT HEIGHT, LF_BLKS, BR_BLKS, DEL_LF_ROWS, BLOCKS, PCT_USED FROM INDEX_STATS;
HEIGHT LF_BLKS BR_BLKS DEL_LF_ROWS BLOCKS PCT_USED
---------- ---------- ---------- ----------- ---------- ----------
3 7482 14 0 7680 90
SQL> select name, height, lf_rows, del_lf_rows, round((del_lf_rows/lf_rows)*100,2) as ratio from index_stats where (lf_rows > 100 and del_lf_rows > 0) and (height > 3 or ((del_lf_rows/lf_rows)*100) > 20);
no rows selected
SQL> SELECT COUNT(*) FROM HR.TBL_IDXTEST;
COUNT(*)
----------
3229995
