Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

Index Scanning Methods In Oracle

Index Scanning Methods In Oracle:

Its is a method to retrieve a row by traversing through the index, using index column value specified by the statement.

The index scan first locate the root block of the index. Then it descends through the branch level of the index and afterwards the particular leaf block. It goes to the particular leaf block that has the first possible entry that we have mentioned in our query predicates. Then it walks along a chain of leaf blocks until it reaches the last possible entry that matches our predicates.

For each index entry, if it requires, it visit a table block. We will call this as Table Access by Index ROWID.

Types of Index Scanning Methods:

Below are the scanning methods available:

Index Unique Scan:
  • An Index Unique Scan returns only one row.
  • It requires an equality predicates
  • The database performs an Index Unique scan in the following situations:
    • A single column index, created with UNIQUE INDEX statement is referenced by the query predicates with an equality operator.
    • A composite column index, the predicate references all the columns in a unique index key by using an equality operator.
Creating setup for Index Unique Scan:
--Index Scanning Methods In Oracle : Setup 

CREATE TABLE ALL_OBJECTS_SCAN
(
 OWNER              VARCHAR2(128), 
 OBJECT_NAME        VARCHAR2(128), 
 SUBOBJECT_NAME     VARCHAR2(128), 
 OBJECT_ID          NUMBER,        
 DATA_OBJECT_ID     NUMBER,        
 OBJECT_TYPE        VARCHAR2(23),  
 CREATED            DATE,          
 LAST_DDL_TIME      DATE,          
 TIMESTAMP          VARCHAR2(19),  
 STATUS             VARCHAR2(7),   
 TEMPORARY          VARCHAR2(1),   
 GENERATED          VARCHAR2(1),   
 SECONDARY          VARCHAR2(1),      
 ORACLE_MAINTAINED  VARCHAR2(1)
);
ALTER TABLE ALL_OBJECTS_SCAN ADD CONSTRAINTS OBJECT_SCAN_PK PRIMARY KEY(OBJECT_ID);
INSERT INTO ALL_OBJECTS_SCAN(OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, ORACLE_MAINTAINED)
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, ORACLE_MAINTAINED
FROM ALL_OBJECTS;

COMMIT;
SELECT OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS 
FROM ALL_OBJECTS_SCAN
WHERE OBJECT_NAME = 'ALL_OBJECTS_SCAN';
Checking the Explain Plan:
--Index Scanning Methods In Oracle : Example

EXPLAIN PLAN FOR
SELECT OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS 
FROM ALL_OBJECTS_SCAN
WHERE OBJECT_ID = 174575;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
Index Scanning Methods In Oracle : Output
Index Scanning Methods In Oracle : Output

So in the above example, we can see that the optimizer is using the Index Unique scan. This is an example of Single Column Unique Index.

Lets check a composite column Unique Index.

ALTER TABLE ALL_OBJECTS_SCAN DROP PRIMARY KEY;
ALTER TABLE ALL_OBJECTS_SCAN ADD CONSTRAINTS OBJECT_SCAN_PK PRIMARY KEY(OWNER, OBJECT_ID);
EXPLAIN PLAN FOR
SELECT OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS 
FROM ALL_OBJECTS_SCAN
WHERE OWNER = 'HR'
AND OBJECT_ID = 174575;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
Index Scanning Methods In Oracle : Output

In the above example we have used all the columns of composite Unique Index. That's why the optimizer is using the Index Unique scan method.

Now if we remove the one column from WHERE predicate then optimizer will not use the Index Unique Scan. Lets check this in below example.

--Index Scanning Methods In Oracle : Example

EXPLAIN PLAN FOR
SELECT OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS 
FROM ALL_OBJECTS_SCAN
WHERE OWNER = 'HR';

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

Index Scanning Methods In Oracle : Output

In the above example we can see that Index Range Scan is used. Because oracle returns more than one row.

But the Explain Plan is showing 3527 rows are accessing by the query. Its not the correct data. If we check the data then it will show different result.

SELECT COUNT(1) COUNT_RECORD
FROM ALL_OBJECTS_SCAN
WHERE OWNER = 'HR';

So we will ANALYZE the index to check the correct no of rows.

ANALYZE INDEX OBJECT_SCAN_PK VALIDATE STRUCTURE;
EXPLAIN PLAN FOR
SELECT OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS 
FROM ALL_OBJECTS_SCAN
WHERE OWNER = 'HR';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
Index Scanning Methods In Oracle : Output
Index Range Scan:
  • An Index Range Scan is an ordered scan of an Index.
  • It happens when Index is scanned for a range of Index values.
  • One or more leading columns of an index are specified in a condition like:
    • Indexed_Column = : Val
    • > : Val
    • < : Val
    • BETWEEN
    • LIKE
    • Or any combination of above
  • Index Range Scan works in the following manner:
    • Read the Root block
    • Read the Branch block
    • Alternatively follows the below steps until all the records are retrieved
      • Read the Leaf block to get the ROWID
      • Read the Table block to get the ROW by using the ROWID

Now we will use the same Example used for Index Unique Scan.

Indexed_Column = : Val :
EXPLAIN PLAN FOR
SELECT OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS 
FROM ALL_OBJECTS_SCAN
WHERE OWNER = 'HR';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
Index Scanning Methods In Oracle : Output
Example of Indexed_Column > : Val :
EXPLAIN PLAN FOR
SELECT OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS 
FROM ALL_OBJECTS_SCAN
WHERE OWNER = 'HR'
AND OBJECT_ID > 73150;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
Index Scanning Methods In Oracle : Output
Example of Indexed_Column < : Val :
EXPLAIN PLAN FOR
SELECT OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS 
FROM ALL_OBJECTS_SCAN
WHERE OWNER = 'HR'
AND OBJECT_ID < 73150;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
Index Scanning Methods In Oracle : Output
Example of Indexed_Column BETWEEN :
EXPLAIN PLAN FOR
SELECT OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS 
FROM ALL_OBJECTS_SCAN
WHERE OWNER = 'HR'
AND OBJECT_ID BETWEEN 70000 AND 80000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
Index Scanning Methods In Oracle : Output
Example of Indexed_Column LIKE :
EXPLAIN PLAN FOR
SELECT OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS 
FROM ALL_OBJECTS_SCAN
WHERE OWNER LIKE 'HR%';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
Index Scanning Methods In Oracle : Output
Index Full Scan:
  • An Index Full Scan reads the entire index in sorted order of Index key.
  • Full Index Scan is performed if below conditions are true:
    • We are selecting the data from a table with an order by clause
    • The columns in the order by clause are in sync with order of the leading indexed columns
  • The Index Full Scan works in the following manner:
    • The database reads the Root block
    • Then it navigates down to the left hand side of the Index for Ascending Index or right hand side of the index for Descending Index
    • Once we reach the Leaf block we will read across the entire bottom of the index one block at a time in sorted order.
    • We will use Single Block I/o rather than Multiple Block I/O.
Index Scanning Methods In Oracle : output
EXPLAIN PLAN FOR
SELECT *
FROM ALL_OBJECTS_SCAN
ORDER BY OWNER, OBJECT_ID;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
Index Fast Full Scan:
  • In Index Fast Full Scan reads the entire index in unsorted order as it exists in the disk.
  • This Index scan is using the Index as a shorter version of the table.
  • Here basically we are not using the Index as a way to get to the table. Rather we are using the Index instead of table.
  • This scan will be used in a situation where the Index itself will contain all the columns to answer the query.
  • In Index Fast Full Scan, multiple block I/O happens and we will read all Root, Branch and Leaf blocks.
  • We will ignore the Root and Branch blocks and just process the unordered data of Leaf blocks.
EXPLAIN PLAN FOR
SELECT OWNER, OBJECT_ID 
FROM ALL_OBJECTS_SCAN;

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

In the above example in Table ALL_OBJECTS_SCAN, the composite index is created on columns OWNER and OBJECT_ID. In the above query we are also selecting those 2 indexed columns only. That's why the optimizer is using the INDEX FAST FULL SCAN.

Index Skip Scan:
  • Optimizer uses the Index Skip Scan when the following criteria are matched:
    • One of the leading indexed column of a composite index is skipped or not used in the query.
    • Few distinct values exist in leading indexed column and many distinct values exist in nonleading indexed column.
SELECT OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS 
FROM ALL_OBJECTS_SCAN
WHERE OBJECT_NAME = 'ALL_OBJECTS_SCAN';
EXPLAIN PLAN FOR
SELECT OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, STATUS 
FROM ALL_OBJECTS_SCAN
WHERE OBJECT_ID = 174575;

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

In the above example, we have used the OBJECT_ID column only in the query predicate. OWNER column is missing in the WHERE clause. Also OWNER column has few distinct values and OBJECT_ID is unique. That's why optimizer is using the SKIP SCAN INDEX.

RELATED TOPICS:

Leave a Comment

Your email address will not be published.