How Index affect table access in Oracle?

How Index affect table access in Oracle?

Index in brief:

• Index is a schema object.
• Its created based on a table or cluster column.
• It contains an entry for each value that appears in the indexed column of the table or cluster
• It provides a direct and fast access to the rows.

Overview of B-Tree Index:

• B-Tree or Balanced Tree Indexes are most commonly used index in Oracle.
• It provides an excellent retrieval performance gain when used properly.
• B-Tree index has 2 types of blocks:
  • Branch blocks for searching
  • Leaf blocks for storing index key and rowid values
• Upper level of Branch blocks of a B-tree index contains the indexing data that points to the lower level Leaf blocks
• All the Leaf blocks should be in the same level of the Tree. It means that any traversal from root to leaf will visit the same no of blocks. Its also called as height of the Tree.

Setup creation to test the impact of Index:

We will create 2 tables OBJ_PERFORMANCE_MAIN and OBJ_PERFORMANCE_DATE_WISE. OBJ_PERFORMANCE_MAIN will be a master table of all objects and OBJ_PERFORMANCE_DATE_WISE will store the master data date wise.

--How Index affect table access in Oracle? : Setup

CREATE TABLE OBJ_PERFORMANCE_MAIN
(OBJECT_ID      NUMBER, 
 OWNER          VARCHAR2(128), 
 OBJECT_NAME    VARCHAR2(128),  
 STATUS         VARCHAR2(7)
);

Inserting data into OBJ_PERFORMANCE_MAIN from ALL_OBJECTS data dictionary view.

--How Index affect table access in Oracle? : Setup

INSERT INTO OBJ_PERFORMANCE_MAIN(OBJECT_ID, OWNER, OBJECT_NAME, STATUS)
SELECT OBJECT_ID, OWNER, OBJECT_NAME, STATUS
FROM ALL_OBJECTS
ORDER BY OBJECT_ID;

COMMIT;

--How Index affect table access in Oracle? : Setup

CREATE TABLE OBJ_PERFORMANCE_DATE_WISE
(OBJECT_ID      NUMBER, 
 CREATED        DATE,
 OWNER          VARCHAR2(128),   
 STATUS         VARCHAR2(7)
);

Inserting data for last 10 days from ALL_OBJECTS table.

--How Index affect table access in Oracle? : Example

BEGIN
  FOR I IN 1..10
  LOOP
    INSERT INTO OBJ_PERFORMANCE_DATE_WISE(OBJECT_ID, CREATED, OWNER, STATUS)
    SELECT OBJECT_ID, TRUNC(SYSDATE - I), OWNER, STATUS
    FROM ALL_OBJECTS;
  END LOOP;
END;
/

COMMIT;

Performance impact on query without using Index:

In the above setup we haven't used any index so far. So we will check the explain plan for below query to see how optimizer is accessing the data.

--How Index affect table access in Oracle? : Example

EXPLAIN PLAN FOR
SELECT B.* 
FROM OBJ_PERFORMANCE_MAIN A,
     OBJ_PERFORMANCE_DATE_WISE B
WHERE B.CREATED = '03-JUN-2022'
AND A.OBJECT_ID = B.OBJECT_ID;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

So its clear that Full table scan is happening.

Performance impact on query using Index:

Now we will create an index on OBJECT_ID column of OBJ_PERFORMANCE_MAIN table.

CREATE INDEX OBJECT_ID_INDX ON OBJ_PERFORMANCE_MAIN(OBJECT_ID);

EXPLAIN PLAN FOR
SELECT B.* 
FROM OBJ_PERFORMANCE_MAIN A,
     OBJ_PERFORMANCE_DATE_WISE B
WHERE B.CREATED = '03-JUN-2022'
AND A.OBJECT_ID = B.OBJECT_ID;

Checking the explain plan for the same query:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

Now we can see that the table OBJ_PERFORMANCE_MAIN is accessing by the optimizer via index. But the table OBJ_PERFORMANCE_DATE_WISE is still accessing via Full table scan.

Lets create an index on OBJECT_ID column of OBJ_PERFORMANCE_DATE_WISE table.

CREATE INDEX OBJECT_ID_DTWISE_INDX ON OBJ_PERFORMANCE_DATE_WISE(OBJECT_ID);

EXPLAIN PLAN FOR
SELECT B.* 
FROM OBJ_PERFORMANCE_MAIN A,
     OBJ_PERFORMANCE_DATE_WISE B
WHERE B.CREATED = '03-JUN-2022'
AND A.OBJECT_ID = B.OBJECT_ID;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

Now we can see that the table OBJ_PERFORMANCE_DATE_WISE is still accessed via Full table scan.

The reason is that the OBJECT_ID column of table OBJ_PERFORMANCE_MAIN is unique. But the same column of table OBJ_PERFORMANCE_DATE_WISE is not unique. Together OBJECT_ID and CREATED column will be unique. So we need to change the definition of index on OBJ_PERFORMANCE_DATE_WISE table.

DROP INDEX OBJECT_ID_DTWISE_INDX;

CREATE INDEX OBJECT_ID_DTWISE_INDX ON OBJ_PERFORMANCE_DATE_WISE(CREATED, OBJECT_ID);

Now we will check the explain plan for the same query:

SELECT B.* 
FROM OBJ_PERFORMANCE_MAIN A,
     OBJ_PERFORMANCE_DATE_WISE B
WHERE B.CREATED = '03-JUN-2022'
AND A.OBJECT_ID = B.OBJECT_ID;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

Now we can see that Index is being used by the optimizer.

RELATED TOPICS:

• How to increase sequence value by 100 in Oracle?
• How To Copy Table Including Data, Indexes, Constraints?
• ALTER INDEX Remove or Add column in Oracle
• Index Scanning Methods in Oracle