In ORACLE, there are five ways of Index Lookup paths: INDEX UNIQUE SCAN, INDEX RANGE SCAN, INDEX FULL SCAN, INDEX FAST FULL SCAN, INDEX SKIP SCAN. The following five index access paths are introduced and summarized through some cases. This article is a summary of the knowledge points in this regard, so some places in the article refer to and quote the reference materials may wish to content. Detailed and specific information can be referred to official information. Index Scans
Index Unique Scan (INDEX UNIQUE SCAN)
Index unique scan only occurs on UNIQUE INDEX, which only applies to SQL with equivalent query in WHERE condition, because for unique index, equivalent query only returns one record at most. For composite unique indexes, the WHERE condition needs to contain all index columns to use index unique scan (INDEX UNIQUE SCAN).
SQL> SET AUTOTRACE TRACEONLY;SQL> SELECT * FROM SCOTT.EMP2 WHERE EMPNO=7788;Execution Plan----------------------------------------------------------Plan hash value: 2949544139--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("EMPNO"=7788)Statistics----------------------------------------------------------1 recursive calls0 db block gets2 consistent gets0 physical reads0 redo size891 bytes sent via SQL*Net to client512 bytes received via SQL*Net from client1 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed
Index Unique Scan (INDEX UNIQUE SCAN) is only applicable to the SQL with equivalent query in WHERE condition. If the query condition is an interval range, index unique scan will not be used. As shown below, the execution plan becomes an index range scan (INDEX RANGE SCAN).
SQL> SET AUTOTRACE TRACEONLY;SQL> SELECT * FROM SCOTT.EMP2 WHERE EMPNO>=788 AND EMPNO <=7788;8 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 169057108--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 11 | 418 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 11 | 418 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | PK_EMP | 11 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("EMPNO">=788 AND "EMPNO"<=7788)Statistics----------------------------------------------------------1 recursive calls0 db block gets4 consistent gets0 physical reads0 redo size1383 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)8 rows processed
Note: As shown below, if the query condition is equivalent query under DEPTNO, because the index on field DEPTNO is IX_DEPTNO (non-unique index), there will be no index unique scan. That is to say, the index unique scan only occurs on the unique index.
SQL> SELECT * FROM SCOTT.EMP2 WHERE DEPTNO=10;Execution Plan----------------------------------------------------------Plan hash value: 1198124189-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IX_DEPTNO | 5 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("DEPTNO"=10)Statistics----------------------------------------------------------1 recursive calls0 db block gets4 consistent gets1 physical reads0 redo size1159 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)3 rows processedSQL>
For composite unique indexes, the WHERE condition needs to contain all index columns to use index unique scanning. Otherwise, the index range scan (INDEX RANGE SCAN) will be used.
SQL> CREATE TABLE SCOTT.EMP_TEST2 AS3 SELECT * FROM SCOTT.EMP;Table created.SQL> CREATE UNIQUE INDEX SCOTT.IDX_EMP_TEST_U ON SCOTT.EMP_TEST(EMPNO, ENAME);Index created.SQL> SET AUTOTRACE ON EXPLAIN;SQL> SELECT * FROM SCOTT.EMP_TEST2 WHERE EMPNO=7788;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------7788 SCOTT ANALYST 7566 19-APR-87 3000 20Execution Plan----------------------------------------------------------Plan hash value: 206749441----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEST | 1 | 87 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_EMP_TEST_U | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("EMPNO"=7788)Note------ dynamic sampling used for this statement (level=2)SQL> SELECT * FROM SCOTT.EMP_TEST2 WHERE EMPNO=7788 AND ENAME='SCOTT';EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------7788 SCOTT ANALYST 7566 19-APR-87 3000 20Execution Plan----------------------------------------------------------Plan hash value: 2899991080----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEST | 1 | 87 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | IDX_EMP_TEST_U | 1 | | 0 (0)| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("EMPNO"=7788 AND "ENAME"='SCOTT')SQL>
For a composite unique index, if the leading column of the index is not in the WHERE condition, the execution plan path will follow a full table scan instead of an index scan, as follows:
SQL> SET AUTOTRACE ON EXPLAIN;SQL> SELECT * FROM SCOTT.EMP_TEST2 WHERE ENAME='SCOTT';EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------7788 SCOTT ANALYST 7566 19-APR-87 3000 20Execution Plan----------------------------------------------------------Plan hash value: 3124080142------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| EMP_TEST | 1 | 38 | 3 (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("ENAME"='SCOTT')SQL>
In addition, in the next step of the index's only scan in the execution plan, you will notice that it may be "TABLE ACCESS BY INDEX ROWID" or "SELECT STATEMENT". "TABLE ACCESS BY INDEX ROWID" means that table data is not accessed by FTS operations, but by rowid lookup. If all the necessary data resides in the index, table lookup may be unnecessary, and all you will see is index access (no table access). In the following example, all columns (only EMPNO columns) are in the index. Therefore, it does not make table access, but uses SELET* to generate table access:
SQL> SET AUTOTRACE ON EXPLAIN;SQL> SELECT EMPNO FROM SCOTT.EMP2 WHERE EMPNO=7788;EMPNO----------7788Execution Plan----------------------------------------------------------Plan hash value: 56244932----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:01 ||* 1 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - access("EMPNO"=7788)SQL> SELECT * FROM SCOTT.EMP2 WHERE EMPNO=7788;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------7788 SCOTT ANALYST 7566 19-APR-87 3000 20Execution Plan----------------------------------------------------------Plan hash value: 2949544139--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("EMPNO"=7788)SQL>
Usually, the execution plan first finds the ROWID value corresponding to the data through Index (for non-unique index, it may return multiple ROWID values), and then gets the specific data directly from the table according to rowid, which is called index loopup. A ROWID represents only one row of data, and the corresponding data block of that row is obtained by one I/O, in which case only one data block is read. In an index, in addition to storing the value of each index, the index also stores the value of ROWID corresponding to the row with this value.
Index scanning can be composed of two steps:
Scanning the index to get the corresponding ROWID value
Read specific data from tables by finding ROWID
Each step is a separate I/O, but for index, most of them have been CACHE into memory, so the first step of I/O is often logical I/O, that is, data can be obtained from memory. But for the second step, if the table is large, its data can not be all in memory, so its I/O is likely to be physical I/O, which is a physical operation, relative to logical I/O, is extremely time-consuming. So if we scan large tables indexed, the efficiency of using index scanning will be greatly reduced if the data extracted is more than 5% - 10% of the total amount. But if all the query data can be found in the index, the second step operation can be avoided, and unnecessary I/O can be avoided. At this time, even if more data can be retrieved through index scanning, the efficiency is still very high. Further, if the index columns are sorted in the SQL statement, because the indexes are pre-sorted, there is no need to sort the index columns again in the execution plan.
Index Range Scan (INDEX RANGE SCAN)
Index range scanning is a very common way to access tables. Typically, index range scanning uses range operators (<,>,<>,>=,<=, BEWTEEN) in predicates (WHERE constraints). Here are some scenarios where index range scans occur
1: Use range lookup operators (>,<,<>,>=,<=, BEWTEEN) etc. on unique indexes.
2: In a composite unique index, only part of the column is used for query (must contain leading column), resulting in multiple records (or possibly one record).
3: Make any queries on non-unique index columns.
1: Use range lookup operators (>,<,<>,>=,<=, BEWTEEN) etc. on unique indexes.
SQL> SET AUTOTRACE ON EXPLAIN;SQL> SELECT * FROM SCOTT.EMP2 WHERE EMPNO >=7788;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------7788 SCOTT ANALYST 7566 19-APR-87 3000 207839 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 23-MAY-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 107 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 169057108--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | PK_EMP | 5 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("EMPNO">=7788)SQL>
2: On a composite unique index, queries using only part of the columns (which must contain leading column s) result in one or more records being queried
SQL> SET AUTOTRACE OFF;SQL> SET AUTOTRACE ON EXPLAIN;SQL> SELECT * FROM SCOTT.EMP_TEST2 WHERE EMPNO=7788;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------7788 SCOTT ANALYST 7566 19-APR-87 3000 20Execution Plan----------------------------------------------------------Plan hash value: 206749441----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEST | 1 | 38 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_EMP_TEST_U | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("EMPNO"=7788)SQL>
3: Make any queries on non-unique index columns.
SQL> SET AUTOTRACE TRACEONLY;SQL> SELECT * FROM SCOTT.EMP2 WHERE DEPTNO=20;Execution Plan----------------------------------------------------------Plan hash value: 1198124189-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IX_DEPTNO | 5 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("DEPTNO"=20)Statistics----------------------------------------------------------1 recursive calls0 db block gets4 consistent gets0 physical reads0 redo size1241 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)5 rows processedSQL> SELECT * FROM SCOTT.EMP2 WHERE DEPTNO >=10;14 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1198124189-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IX_DEPTNO | 14 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("DEPTNO">=10)Statistics----------------------------------------------------------1 recursive calls0 db block gets4 consistent gets0 physical reads0 redo size1630 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)14 rows processedSQL>
Index descending range scan (INDEX RANGE SCAN DESCENDING)
By default, indexes store data in ascending order of index keywords. When SQL statements are sorted using ORDER BY COLUMN_NAME DESC, the access path of INDEX RANGE SCAN DESCENDING appears
In general, if there are conditions for ORDER BY COLUMN_NAME DESC, then
First, we need to read data from the index.
(2) Sort them in descending order according to COLUMN_NAME field in the condition.
The choice of INDEX RANGE SCAN DESCENDING is to find the data directly in descending order according to the index keywords (in fact, to find the first row of index rows of the leaf block on the right side of the corresponding index, and then access the data through the bi-directional linked list between the leaf blocks). This is precisely to avoid searching the data according to the index first, and then doing a descending order sorting operation. The following tests show:
SQL> SET LINESIZE 1200;SQL> SET AUTOTRACE TRACEONLY;SQL> SELECT * FROM SCOTT.EMP_TEST2 WHERE EMPNO >= 77883 ORDER BY EMPNO DESC;7 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 4203867900-----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID | EMP_TEST | 5 | 190 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN DESCENDING| IDX_EMP_TEST_U | 5 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("EMPNO">=7788 AND "EMPNO" IS NOT NULL)Statistics----------------------------------------------------------0 recursive calls0 db block gets4 consistent gets0 physical reads0 redo size1324 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)7 rows processedSQL>
Index Full Scan (INDEX FULL SCAN)
Full index scanning requires scanning all index rows of all leaf blocks of the target index. However, this does not mean that a full scan of the index requires scanning all branches of the index. By default, the index global scan only needs to locate the first row of index rows of the leftmost leaf block of the index by accessing the necessary branch blocks. Then, using the bi-directional pointer list between the index leaf blocks, all index rows of the index leaf blocks can be scanned sequentially from left to right. The results of index full scan are ordered. It is sorted by the key column of the index. That is to say, global scan with index can achieve sorting effect. This avoids reordering the index key value of the index. However, it is the orderliness of index full scan that makes it impossible for index full scan to execute in parallel. If the query columns are all index columns of the target index, then the full scan of the index does not need to return the table.
SQL> CREATE INDEX SCOTT.IX_EMP_N1 ON SCOTT.EMP(EMPNO, ENAME);Index created.SQL> SET AUTOTRACE TRACEONLY;SQL> select empno, ename from scott.emp order by empno,ename;14 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 162731191------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 140 | 1 (0)| 00:00:01 || 1 | INDEX FULL SCAN | IX_EMP_N1 | 14 | 140 | 1 (0)| 00:00:01 |------------------------------------------------------------------------------Statistics----------------------------------------------------------1 recursive calls0 db block gets2 consistent gets0 physical reads0 redo size837 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)14 rows processedSQL>
INDEX FAST FULL SCAN
Fast index scanning, like full index scanning, also requires scanning all index rows of all leaf blocks in the target index. It also applies to all types of B-tree indexes (including uniqueness index and non-uniqueness index). There are three differences between fast full scan and full scan.
1: Fast full scan of index is only suitable for CBO mode, and full scan of index can be used for CBO or RBO.
2: Quick full scan of index can be read by multiple blocks or executed in parallel. In this access method, either multi-block reading or parallel reading can be used to achieve maximum throughput and shorten execution time.
3: The execution result of index fast full scan is not necessarily orderly, because index fast full scan is scanned according to the physical storage order of index on disk, not according to the logical order of index rows. So the scanning results are not necessarily ordered (for index rows in a single index leaf block, the physical storage order is the same as the logical storage order; but for index leaf blocks with adjacent physical storage locations, the physical storage order of index rows between blocks is not necessarily logically ordered).
SQL> BEGIN2 FOR IndexLoop IN 0..1000 LOOP3 INSERT INTO SCOTT.EMP(EMPNO,ENAME)4 VALUES(IndexLoop,CONCAT('TEST',IndexLoop));5 END LOOP;6 END;7 /PL/SQL procedure successfully completed.SQL> execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>'EMP',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');PL/SQL procedure successfully completed.SQL> SELECT EMPNO FROM SCOTT.EMP;1015 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 366039554-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1015 | 4060 | 3 (0)| 00:00:01 || 1 | INDEX FAST FULL SCAN| PK_EMP | 1015 | 4060 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------Statistics----------------------------------------------------------0 recursive calls0 db block gets72 consistent gets0 physical reads0 redo size18050 bytes sent via SQL*Net to client1260 bytes received via SQL*Net from client69 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1015 rows processed
Index Jump Scan (INDEX SKIP SCAN)
When a table has a composite index and other columns in the query except the first column in the index are used as conditions and the optimizer mode is CBO, it is possible for query plan to use index Skip scan Skipscan to detect the unique number of values in the leading column of the index, and each unique value will be used as the entry of the conventional scan. On this basis, a search is made and these values are merged finally. query
INDEX SKIP SCAN, which occurs on a composite index created by multiple columns, may occur if the predicate condition in SQL contains only part of the columns in the index and these columns contain the first column (leading column) at the time of indexing. Index skip scanning is only applicable to those cases where the number of DISTINCT values of the leading column of the target index is small and the selectivity of the subsequent non-leading column is very good.
The documents of Oracle 10g are as follows:
Index Skip Scans
Index skip scans improve index scans by nonprefix columns. Often, scanning index
blocks is faster than scanning table data blocks.
Skip scanning lets a composite index be split logically into smaller subindexes. In skip
scanning, the initial column of the composite index is not specified in the query. In
other words, it is skipped.
The number of logical subindexes is determined by the number of distinct values in
the initial column. Skip scanning is advantageous if there are few distinct values in the
leading column of the composite index and many distinct values in the nonleading
key of the index.
Example 13–5 Index Skip Scan
Consider, for example, a table employees (sex, employee_id, address) with a
composite index on (sex, employee_id). Splitting this composite index would result
in two logical subindexes, one for M and one for F.
For this example, suppose you have the following index data:
('F',98)
('F',100)
('F',102)
('F',104)
('M',101)
('M',103)
('M',105)
The index is split logically into the following two subindexes:
■ The first subindex has the keys with the value F.
■ The second subindex has the keys with the value M.
Figure 13–2 Index Skip Scan Illustration
The column sex is skipped in the following query:
SELECT *
FROM employees
WHERE employee_id = 101;
A complete scan of the index is not performed, but the subindex with the value F is
searched first, followed by a search of the subindex with the value M.
SQL> DROP TABLE SCOTT.OBJECT_TEST;Table dropped.SQL> CREATE TABLE SCOTT.OBJECT_TEST2 AS3 SELECT * FROM DBA_OBJECTS;Table created.SQL> CREATE INDEX SCOTT.IDX_OBJECT_TEST_N1 ON SCOTT.OBJECT_TEST(OWNER, OBJECT_ID, OBJECT_TYPE);Index created.SQL> execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>'OBJECT_TEST');PL/SQL procedure successfully completed.SQL>SQL> SET AUTOTRACE TRACEONLY;SQL> SELECT * FROM SCOTT.OBJECT_TEST2 WHERE OBJECT_ID=13;Execution Plan----------------------------------------------------------Plan hash value: 1063825859--------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 97 | 28 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| OBJECT_TEST | 1 | 97 | 28 (0)| 00:00:01 ||* 2 | INDEX SKIP SCAN | IDX_OBJECT_TEST_N1 | 1 | | 27 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("OBJECT_ID"=13)filter("OBJECT_ID"=13)Statistics----------------------------------------------------------1 recursive calls0 db block gets18 consistent gets0 physical reads0 redo size1607 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processedSQL>
Reference material:
http://blog.itpub.net/26736162/viewspace-2139246/
http://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#i51571