INDEX SCAN define how the Optimizer will traverse the index block to get the result.
It is a access path used by the Query Optimizer to produce the best SQL Engine – (Physical|Execution) Plan.
In this method, a row is retrieved by traversing the index, using the indexed column values specified by the statement. An index scan retrieves data from an index based on the value of one or more columns in the index. To perform an index scan, Oracle searches the index for the indexed column values accessed by the statement. If the statement accesses only columns of the index, then Oracle reads the indexed column values directly from the index, rather than from the table.
The index contains :-
• Indexed value
• ROWIDs of rows in the table having that value.
Therefore, if the statement accesses other columns in addition to the indexed columns, then Oracle can find the rows in the table by using either a “TABLE ACCESS BY ROWID” or a “Cluster Scan/Access”.
INDEX CLUSTERING FACTOR
Oracle does I/O by blocks. Therefore, the optimizer’s decision to use full table scans is influenced by the percentage of blocks accessed, not rows. This is called the index clustering factor. If blocks contain single rows, then rows accessed and blocks accessed are the same.
However, most tables have multiple rows in each block. Consequently, the desired number of rows could be clustered together in a few blocks, or they could be spread out over a larger number of blocks.
Although the index clustering factor is a property of the index, the index clustering factor actually relates to the spread of similar indexed column values within data blocks in the table.
A lower clustering factor indicates that the individual rows are concentrated within fewer blocks in the table. Conversely, a high clustering factor indicates that the individual rows are scattered more randomly across blocks in the table. Therefore, a high clustering factor means that it costs more to use a range scan to fetch rows by ROWID, because more blocks in the table need to be visited to return the data.
INDEX UNIQUE SCAN
The database performs a unique scan when a predicate references all of the columns in a UNIQUE index key using an equality operator.
An index unique scan stops processing as soon as it finds the first record because no second record is possible.
In a nutshell, this scan returns, at most, a single ROWID. Oracle performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.
SQL> CREATE TABLE trans_demo
2 (
3 id INT,
4 salary INT,
5 dept_no INT
6 );
Table created.
SQL> INSERT INTO trans_demo
2 SELECT rownum,TRUNC(DBMS_RANDOM.VALUE(100,9000)),TRUNC(DBMS_RANDOM.VALUE(1,10))
3 FROM dual
4 CONNECT BY LEVEL <= 100000;
100000 rows created.
SQL> COMMIT;
Commit complete.
SQL> CREATE UNIQUE INDEX idx_trans_demo ON trans_demo(id);
Index created.
SQL> ANALYZE INDEX idx_trans_demo VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT * FROM trans_demo WHERE id = 10;
ID SALARY DEPT_NO
---------- ---------- ----------
10 1428 1
Execution Plan
----------------------------------------------------------
Plan hash value: 1400834815
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TRANS_DEMO | 1 | 39 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_TRANS_DEMO | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=10)
--Instead Of Unique Index, If We Create A Normal Index Then The Optimizer Will Choose "Index Range Scan", Because The Optimizer Is NOT Sure The # Of Values To Search For
SQL> DROP INDEX idx_trans_demo;
Index dropped.
SQL> CREATE INDEX idx_trans_demo ON trans_demo(id);
Index created.
SQL> ANALYZE INDEX idx_trans_demo VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT * FROM trans_demo WHERE id = 10;
ID SALARY DEPT_NO
---------- ---------- ----------
10 1428 1
Execution Plan
----------------------------------------------------------
Plan hash value: 30488434
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TRANS_DEMO | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TRANS_DEMO | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=10)
Note
-----
- dynamic sampling used for this statement (level=2) In table – trans_demo,UNIQUE index has been created but no PRIMARY KEY has been created,and UNIQUE index may contain NULL,so when we issue COUNT(*),it will result to TABLE ACCESS FULL instead of picking the index for query optimization because in index doesn’t store NULL values where chances are there that the table may contain a NULL value in id column.So to avoid this confusion,the optimizer will use TABLE ACCESS FULL.
SQL> SELECT COUNT(*) FROM trans_demo;
COUNT(*)
----------
100000
Execution Plan
----------------------------------------------------------
Plan hash value: 292873347
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TRANS_DEMO | 106K| 67 (2)| 00:00:01 |
-------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT * FROM trans_demo WHERE id IS NULL;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3773167284
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TRANS_DEMO | 100K| 1171K| 67 (2)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)INDEX RANGE SCAN
An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by ROWID.
If data must be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort.
The database performs a range scan when it finds one or more leading columns of an index specified in below conditions :-
- col1 = :b1
- col1 < :b1
- col1 > :b1
- col1 BETWEEN
- col1 LIKE
SQL> SELECT * FROM trans_demo WHERE id BETWEEN 50 AND 60;
ID SALARY DEPT_NO
---------- ---------- ----------
50 526 8
51 5538 7
52 6974 9
53 7189 8
54 3778 4
55 5766 2
56 4626 4
57 3486 2
58 4382 4
59 5637 4
60 1357 3
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 30488434
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 429 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TRANS_DEMO | 11 | 429 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TRANS_DEMO | 11 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=50 AND "ID"<=60)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT * FROM trans_demo WHERE id < 11;
ID SALARY DEPT_NO
---------- ---------- ----------
1 2910 3
2 5758 7
3 4065 2
4 2029 2
5 5820 4
6 6722 2
7 4458 1
8 2273 7
9 7408 4
10 2217 6
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 30488434
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 390 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TRANS_DEMO | 10 | 390 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TRANS_DEMO | 10 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<11)
Note
-----
- dynamic sampling used for this statement (level=2)INDEX RANGE SCAN – DESCENDING
An index range scan DESCENDING is identical to an index range scan, except that the data is returned in descending order.
Usually, the database uses this scan to return the most recent data first.
The optimizer uses index range scan DESCENDING when an an index can satisfy an order by descending clause.
SQL> SELECT * FROM trans_demo WHERE id < 11 ORDER BY id DESC;
ID SALARY DEPT_NO
---------- ---------- ----------
10 2217 6
9 7408 4
8 2273 7
7 4458 1
6 6722 2
5 5820 4
4 2029 2
3 4065 2
2 5758 7
1 2910 3
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3632529847
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 390 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TRANS_DEMO | 10 | 390 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| IDX_TRANS_DEMO | 10 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<11)
Note
-----
- dynamic sampling used for this statement (level=2)INDEX FULL SCAN
In INDEX FULL SCAN,the table will NOT be referenced at all and instead all information will be fetched from the index itself using single-block read.
***In single-block read, the index is read one block at a time in sorted order. It will start from root block, navigate down to either the left hand side or right hand side of the index and then when we hit the leaf block.***
A Index full scan eliminates a sort operation, because the data is ordered by the index key.
Prerequisites For INDEX FULL SCAN :-
• All the columns that are used in the SELECT must be part of index, if any column is missing, it will NOT use INDEX FULL SCAN.
• An ORDER BY clause that meets the following requirements is present in the query :
o All of the columns in the ORDER BY clause must be in the index.
o The order of the columns in the ORDER BY clause must match the order of the leading index columns.
o The ORDER BY clause can contain all of the columns in the index or a subset of the columns in the index.
• The query requires a sort merge join. The database can perform a full index scan instead of doing a full table scan followed by a sort when the query meets the following requirements :-
o All of the columns referenced in the query must be in the index.
o The order of the columns referenced in the query must match the order of the leading index columns.
o The query can contain all of the columns in the index or a subset of the columns in the index.
• A GROUP BY clause is present in the query, and the columns in the GROUP BY clause are present in the index. The columns do not need to be in the same order in the index and the GROUP BY clause. The GROUP BY clause can contain all of the columns in the index or a subset of the columns in the index.
INDEX FAST FULL SCAN
In INDEX FAST FULL SCAN the table will NOT be referenced at all and instead all information will be fetched from the index itself using Multi-block read (128 times faster than sequential read). This result in performance upgradation.
Prerequisites For INDEX FAST FULL SCAN :-
• All the columns that are used in the SELECT must be part of index,if any column is missing,it will NOT use INDEX FAST FULL SCAN.
• There should not be any ORDER BY clause in the query.The optimizer should randomly perform multi-block read.
SQL> SELECT COUNT(*) FROM trans_demo;
COUNT(*)
----------
100000
Execution Plan
----------------------------------------------------------
Plan hash value: 4093706250
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 59 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_TRANS_DEMO | 106K| 59 (2)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> CREATE TABLE index_demo AS SELECT * FROM all_objects;
Table created.
--This Will Create A COMPOSITE Index.
SQL> ALTER TABLE index_demo ADD CONSTRAINT pk_index_demo PRIMARY KEY(owner,object_name,object_type,object_id);
Table altered.
--This Will Create A UNIQUE Index.
SQL> CREATE UNIQUE INDEX idx_object_id ON index_demo(object_id);
Index created.
SQL> ANALYZE INDEX idx_object_id VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT owner,index_name,index_type,table_name,uniqueness,tablespace_name FROM all_indexes WHERE table_name = 'INDEX_DEMO';
OWNER INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------------------------- ------------------------------ --------- -------
SYSTEM PK_INDEX_DEMO NORMAL INDEX_DEMO UNIQUE SYSTEM
SYSTEM IDX_OBJECT_ID NORMAL INDEX_DEMO UNIQUE SYSTEM
SQL> SELECT owner,object_name,object_type,object_id FROM index_demo;
72188 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1346912921
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73341 | 4154K| 150 (1)| 00:00:02 |
| 1 | INDEX FAST FULL SCAN| PK_INDEX_DEMO | 73341 | 4154K| 150 (1)| 00:00:02 |
--------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT owner,object_name,object_type,object_id,data_object_id FROM index_demo;
72188 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1033748746
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73341 | 5085K| 282 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| INDEX_DEMO | 73341 | 5085K| 282 (1)| 00:00:04 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT owner,object_name,object_type,object_id FROM index_demo ORDER BY owner;
72188 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2342405610
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73341 | 4154K| 548 (1)| 00:00:07 |
| 1 | INDEX FULL SCAN | PK_INDEX_DEMO | 73341 | 4154K| 548 (1)| 00:00:07 |
----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT owner,object_name,object_type,object_id FROM index_demo ORDER BY object_id;
72188 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3057513319
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73341 | 4154K| | 1183 (1)| 00:00:15 |
| 1 | SORT ORDER BY | | 73341 | 4154K| 5208K| 1183 (1)| 00:00:15 |
| 2 | INDEX FAST FULL SCAN| PK_INDEX_DEMO | 73341 | 4154K| | 150 (1)| 00:00:02 |
-----------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
INDEX SKIP SCAN
A composite index could only be used if the first column, the leading edge, of the index was referenced in the WHERE clause of a statement. In Oracle 9i this restriction is removed because the optimizer can perform skip scans to retrieve ROWIDS for values that do not use the prefix.
The prefix column should be of low cardinality.
Skip Scans are initiated by probing the index for distinct values of the prefix column. Each of these distinct values is then used as a starting point for a regular index search. The result is several separate searches of a single index that, when combined (UNION operator internally), eliminate the affect of the prefix column. Essentially, the index has been searched from the second level down.
The optimizer uses statistics to decide if a skip scan would be more efficient than a full table scan.
SQL> CREATE TABLE test_objects AS SELECT * FROM all_objects;
Table created.
SQL> CREATE INDEX test_objects_i ON test_objects (owner, object_name, subobject_name);
Index created.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TEST_OBJECTS', CASCADE => TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT owner, object_name FROM test_objects WHERE owner = 'SYS' AND object_name = 'DBMS_OUTPUT';
OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS DBMS_OUTPUT
SYS DBMS_OUTPUT
Execution Plan
----------------------------------------------------------
Plan hash value: 3650344004
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_OBJECTS_I | 1 | 31 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OWNER"='SYS' AND "OBJECT_NAME"='DBMS_OUTPUT')
SQL> SELECT owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
OWNER OBJECT_NAME
------------------------------ ------------------------------
PUBLIC DBMS_OUTPUT
SYS DBMS_OUTPUT
SYS DBMS_OUTPUT
Execution Plan
----------------------------------------------------------
Plan hash value: 1293870291
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 62 | 32 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | TEST_OBJECTS_I | 2 | 62 | 32 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='DBMS_OUTPUT')
filter("OBJECT_NAME"='DBMS_OUTPUT')
INDEX SPAWNING Vs INDEX SPLITTING
• INDEX SPAWNING happens when an index creates a new level which result the B-Tree to grow vertically.
• INDEX SPLITTING happens when an index node is filled with keys and a new index node is created at the same level as the full node. which result the B-Tree to grow horizontally.
SQL> CREATE TABLE idx_split
2 (
3 id INT,
4 name VARCHAR2(90)
5 );
Table created.
SQL> INSERT INTO idx_split
2 SELECT rownum,'DAS' AS name
3 FROM dual
4 CONNECT BY LEVEL <= 100;
100 rows created.
SQL> COMMIT;
Commit complete.
SQL> CREATE UNIQUE INDEX idx_idx_split ON idx_split(id);
Index created.
SQL> ANALYZE INDEX idx_idx_split VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT height,name,lf_blks,br_blks FROM index_stats;
HEIGHT NAME LF_BLKS BR_BLKS
---------- ------------------------------ ---------- ----------
1 IDX_IDX_SPLIT 1 0
SQL> INSERT INTO idx_split
2 SELECT 100 + rownum,'DAS' AS name
3 FROM dual
4 CONNECT BY LEVEL <= 1000;
1000 rows created.
SQL> COMMIT;
Commit complete.
SQL> ANALYZE INDEX idx_idx_split VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT height,name,lf_blks,br_blks FROM index_stats;
HEIGHT NAME LF_BLKS BR_BLKS
---------- ------------------------------ ---------- ----------
2 IDX_IDX_SPLIT 2 1
--This Phenomenon Of Vertical Tree Growth Is Known As "Index Spawning".Happens in Auto-Incremental Scenario.
SQL> INSERT INTO idx_split
2 SELECT 1100 + rownum,'DAS' AS name
3 FROM dual
4 CONNECT BY LEVEL <= 2000;
2000 rows created.
SQL> COMMIT;
Commit complete.
SQL> ANALYZE INDEX idx_idx_split VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT height,name,lf_blks,br_blks FROM index_stats;
HEIGHT NAME LF_BLKS BR_BLKS
---------- ------------------------------ ---------- ----------
2 IDX_IDX_SPLIT 6 1
SQL> INSERT INTO idx_split
2 SELECT 3100 + rownum,'DAS' AS name
3 FROM dual
4 CONNECT BY LEVEL <= 200000;
200000 rows created.
SQL> COMMIT;
Commit complete.
SQL> ANALYZE INDEX idx_idx_split VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT height,name,lf_blks,br_blks FROM index_stats;
HEIGHT NAME LF_BLKS BR_BLKS
---------- ------------------------------ ---------- ----------
2 IDX_IDX_SPLIT 380 1
--This Phenomenon Of Horizontal Tree Growth Is Known As "Index Splitting".Happens In Auto-Incremental Scenario.
