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());
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());
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());
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 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());
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());
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());
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());
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 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.
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: