Rebuilding Indexes on Oracle Database

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         86

Delete 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         87
SQL> 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         89

Procedure 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

Leave a Reply