Oracle Index Scans
Version 12.1.0

General Information
Background I have put this page together because, quite frankly, it is important when learning to use hints to have demos that reliably reproduce a specified behavior and to my horror, though not my amazement, the examples in the Oracle docs going backward as far as I could check all include examples that do not work.

So on this page I have extracted the relevant descriptive text from the Oracle docs and with attribution, from the work of Richard Foote, Jonathan Lewis, and a few others to help you build the demos and understand why the work.
Demo Tables not included in the general library table build can be found [here] conn uwclass/uwclass@pdbdev

CREATE TABLE t (testcol NUMBER);

INSERT INTO t (testcol) VALUES (1);
INSERT INTO t (testcol) VALUES (2);
INSERT INTO t (testcol) VALUES (3);
INSERT INTO t (testcol) VALUES (4);
INSERT INTO t (testcol) VALUES (5);
COMMIT;
 
Index Scan Demos
Fast Full Scan A fast full scan is a full index scan in which the database reads all index blocks in no particular order. The database accesses the data in the index itself, without accessing the table.

This is the optimal access method when all of the information required to answer a query is contained in indexed columns. Thus we will start the demo by first finding indexed columns and then building a query that uses only those columns. And, to make the demo as reliable as possible only those where the column is the first column in the index.
conn uwclass/uwclass@pdbdev

col column_name format a30

SELECT table_name, index_name, column_position, column_name
FROM user_ind_columns
WHERE column_position = 1
ORDER BY 1,2,3;

SELECT DISTINCT program_id
FROM airplanes;

EXPLAIN PLAN FOR
SELECT DISTINCT program_id
FROM airplanes;

SELECT * FROM TABLE(dbms_xplan.display);

-------------------------------------------------------------------------
| Id | Operation             | Name         | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|  0 | SELECT STATEMENT      |              |    5 |    20 |    201 (41)|
|  1 |  HASH UNIQUE          |              |    5 |    20 |    201 (41)|
|  2 |   INDEX FAST FULL SCAN| PK_AIRPLANES |  250K|   976K|    132 (10)|
-------------------------------------------------------------------------
Full Scan A full index scan eliminates a sort operation, because the data is ordered by the index key. It reads all index blocks singly. The database uses a full scan in any of the following situations:

* 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

  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.

  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.
conn uwclass/uwclass@pdbdev

SELECT srvr_id
FROM servers;

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------
| Id | Operation        | Name       | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT  |            |  126 |   504 |       1 (0)|
| 1 |  INDEX FULL SCAN  | PK_SERVERS |  126 |   504 |       1 (0)|
------------------------------------------------------------------
Range Scan According to the Oracle docs (as of 30-Nov-2009)
The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions, such as the following:
* col1 = :b1
* col1 < :b1
* col1 > :b1
* AND combination of the preceding conditions for leading columns in the index

and this just isn't correct without some clarification.

A range scan is one in which the index is scanned for a range of values and the easiest way to force this behavior is with constructs such as BETWEEN and BOOLEAN operators such as < and >.
conn uwclass/uwclass@pdbdev

-- example with equals ("=")
SELECT *
FROM t
WHERE testcol = 2;

CREATE INDEX nui_t ON t(testcol);

EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE testcol = 2;

SELECT * FROM TABLE(dbms_xplan.display);

---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|  0 | SELECT STATEMENT | | 1 | 13 | 1 (0)|
|* 1 |  INDEX RANGE SCAN| NUI_T | 1 | 13 | 1 (0)|
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TESTCOL"=2)

DROP INDEX nui_t;

CREATE UNIQUE INDEX nui_t ON t(testcol);

EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE testcol < 2;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------
| Id | Operation         | Name  | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------
|  0 | SELECT STATEMENT  |       |    1 |    13 |       0 (0)|
|* 1 |  INDEX UNIQUE SCAN| NUI_T |    1 |    13 |       0 (0)|
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TESTCOL"=2)

/* What the Oracle docs don't say and yet is obviously important is that if the index is a unique index Oracle will preferentially choose a more efficient unique scan if it can. */

-- examples with greater-than and less-than


DROP INDEX nui_t;

CREATE INDEX nui_t ON t(testcol);

EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE testcol < 2;

SELECT * FROM TABLE(dbms_xplan.display);

-------------------------------------------------------------
| Id | Operation        | Name  | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------
|  0 | SELECT STATEMENT |       |    1 |    13 |       1 (0)|
|* 1 |  INDEX RANGE SCAN| NUI_T |    1 |    13 |       1 (0)|
-------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TESTCOL"<2)

Note
-----
   - dynamic sampling used for this statement

DROP INDEX nui_t;

CREATE UNIQUE INDEX nui_t ON t(testcol);

EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE testcol < 2;

SELECT * FROM TABLE(dbms_xplan.display);

-------------------------------------------------------------
| Id | Operation        | Name  | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------
|  0 | SELECT STATEMENT |       |    1 |    13 |       1 (0)|
|* 1 |  INDEX RANGE SCAN| NUI_T |    1 |    13 |       1 (0)|
-------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TESTCOL"<2)

Note
-----
-- dynamic sampling used for this statement

/* with the Boolean greater-than and less-than operators it does not matter whether the index is unique or non-unique */
Range Scan Descending An index range scan descending is identical to an index range scan, except that the data is returned in descending order. Indexes, by default, are stored in ascending order. Usually, the database uses this scan when ordering data in a descending order to return the most recent data first, or when seeking a value less than a specified value.

The optimizer uses index range scan descending when an an index can satisfy an order by descending clause.
conn uwclass/uwclass@pdbdev

SELECT *
FROM t;

-- if it exists
DROP INDEX nui_t;

CREATE INDEX di_t
ON t(testcol DESC);

EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE testcol BETWEEN 3 AND 4;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------
|  0 | SELECT STATEMENT |      |    2 |    26 |       1 (0)|
|* 1 |  INDEX RANGE SCAN| DI_T |    2 |    26 |       1 (0)|
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(SYS_OP_DESCEND("TESTCOL")>=HEXTORAW('3EFAFF') AND
SYS_OP_DESCEND("TESTCOL")<=HEXTORAW('3EFBFF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("TESTCOL"))>=3 AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("TESTCOL"))<=4)

Note
-----
- dynamic sampling used for this statement

/* That wasn't it so we can forget about that. It a DESCENDING SCAN has nothing to do with the use of a descending index. Now lets look at what it is. */

DROP INDEX di_t;

CREATE INDEX nui_t
ON t(testcol);

EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE testcol BETWEEN 3 AND 4
ORDER BY testcol DESC;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 1 (0)|
|* 1 | INDEX RANGE SCAN DESCENDING| NUI_T | 2 | 26 | 1 (0)|
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TESTCOL">=3 AND "TESTCOL"<=4)

Note
-----
- dynamic sampling used for this statement

-- It is, as you can now see a reverse read on an ascending index.
Skip Scan The following description is from the 11gR2 docs:
An index skip scan uses logical subindexes of a composite index. The database "skips" through a single index as if it were searching separate indexes. Skip scanning is beneficial if there are few distinct values in the leading column of a composite index and many distinct values in the nonleading key of the index.

The database may choose an index skip scan when the leading column of the composite index is not specified in a query predicate. For example, assume that you run the following query for a customer in the sh.customers table:

The following demo was written and posted by Richard Foote and is recreated here for your convenience. A few minor changes have been made to avoid using keywords and to clarify the demo: Though I should expect the very mention of David Bowie should be sufficient for everyone to know this is Richard's.

Links to Richard's blog: Click Here
conn uwclass/uwclass@pdbdev

/* create a table with one column (RID) having many distinct values and one column (CODE) having a small number of distinct values. */

CREATE TABLE ziggy_stuff AS
SELECT MOD(rownum,500000) rid, mod(rownum,5) code, 'ZIGGY' fname
FROM dual
CONNECT BY LEVEL <= 1000000;

/* Then, add a row that has a very distinct CODE value. Although there are only 6 different CODE values, there's only one occurrence of value 42. */

INSERT INTO ziggy_stuff
(rid, code, fname)
VALUES
(42, 42, 'BOWIE');
COMMIT;

SELECT code, COUNT(*)
FROM ziggy_stuff
GROUP BY code;

exec dbms_stats.gather_table_stats(USER, 'ZIGGY_STUFF', CASCADE => TRUE, estimate_percent=> NULL, method_opt => 'FOR ALL COLUMNS SIZE 1');

/* Create a histogram on the CODE value so that the CBO knows there are very few distinct CODEs with a value of 42. */

exec dbms_stats.gather_table_stats(USER, 'ZIGGY_STUFF', CASCADE => TRUE, estimate_percent=> NULL, method_opt => 'FOR COLUMNS CODE SIZE 10');

-- Next, create a multi-column index with the RID column as the leading column

CREATE INDEX ix_ziggy_stuff_rid_code
ON ziggy_stuff(rid, code);

set autotrace on

SELECT *
FROM ziggy_stuff
WHERE rid = 42
AND code = 42;

-----------------------------------------------------------------------------------------
| Id | Operation                  | Name                    | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |                         |    1 |    13 |       4 (0)|
|  1 | TABLE ACCESS BY INDEX ROWID| ZIGGY_STUFF             |    1 |    13 |       4 (0)|
|* 2 | INDEX RANGE SCAN           | IX_ZIGGY_STUFF_RID_CODE |    1 |       |       3 (0)|
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("RID"=42 AND "CODE"=42)

Statistics
----------------------------------------------------------
  0 recursive calls
  0 db block gets
  5 consistent gets
  2 physical reads
  0 redo size
535 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

-- as expected, Oracle searched on both columns and the index was used

SELECT *
FROM ziggy_stuff
WHERE rid = 42;

-----------------------------------------------------------------------------------------
| Id | Operation                  | Name                    | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |                         |    2 |    26 |       5 (0)|
|  1 | TABLE ACCESS BY INDEX ROWID| ZIGGY_STUFF             |    2 |    26 |       5 (0)|
|* 2 | INDEX RANGE SCAN           | IX_ZIGGY_STUFF_RID_CODE |    2 |       |       3 (0)|
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("RID"=42)

Statistics
----------------------------------------------------------
  1 recursive calls
  0 db block gets
  7 consistent gets
  0 physical reads
  0 redo size
603 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  3 rows processed

/* Now lets search on only the leading column (RID) and again the index can be used effectively */

SELECT *
FROM ziggy_stuff
WHERE code = 42;

-------------------------------------------------------------------
| Id | Operation        | Name        | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|  0 | SELECT STATEMENT |             |    1 |    13 |    559 (15)|
|* 1 | TABLE ACCESS FULL| ZIGGY_STUFF |    1 |    13 |    559 (15)|
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("CODE"=42)

Statistics
----------------------------------------------------------
   1 recursive calls
   0 db block gets
2635 consistent gets
   0 physical reads
   0 redo size
 531 bytes sent via SQL*Net to client
 416 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   1 rows processed

-- However, search on the CODE column only and the index can not be used.
-- Whereas the leading column is very selective, a CODE value of 42 could
-- potentially be referenced within any of the index leaf blocks

-- Let's now re-create the index but with the columns reversed.


DROP INDEX ix_ziggy_stuff_rid_code;

CREATE INDEX ix_ziggy_stuff_code_rid
ON ziggy_stuff(code, rid);

SELECT *
FROM ziggy_stuff
WHERE rid = 42 AND code = 42;

-----------------------------------------------------------------------------------------
| Id | Operation                  | Name                    | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |                         |    1 |    13 |       4 (0)|
|  1 | TABLE ACCESS BY INDEX ROWID| ZIGGY_STUFF             |    1 |    13 |       4 (0)|
|* 2 | INDEX RANGE SCAN           | IX_ZIGGY_STUFF_CODE_RID |    1 |       |       3 (0)|
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CODE"=42 AND "RID"=42)

Statistics
----------------------------------------------------------
  1 recursive calls
  0 db block gets
  4 consistent gets
  2 physical reads
  0 redo size
535 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

-- again as expected, the index is used when both columns are searched

SELECT *
FROM ziggy_stuff
WHERE code = 42;

-----------------------------------------------------------------------------------------
| Id | Operation                  | Name                    | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |                         |    1 |    13 |       4 (0)|
|  1 | TABLE ACCESS BY INDEX ROWID| ZIGGY_STUFF             |    1 |    13 |       4 (0)|
|* 2 | INDEX RANGE SCAN           | IX_ZIGGY_STUFF_CODE_RID |    1 |       |       3 (0)|
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CODE"=42)

Statistics
----------------------------------------------------------
  1 recursive calls
  0 db block gets
  4 consistent gets
  0 physical reads
  0 redo size
535 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

/* When searching on just the CODE column for the value 42, with the histogram in place, the CBO estimates there's only the one row and so can use the index effectively */

SELECT * FROM ziggy_stuff WHERE rid = 42;

----------------------------------------------------------------------------------------
| Id | Operation                  | Name |                   Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |                         |  2 |     26 |      10 (0)|
|  1 | TABLE ACCESS BY INDEX ROWID| ZIGGY_STUFF             |  2 |     26 |      10 (0)|
|* 2 | INDEX SKIP SCAN            | IX_ZIGGY_STUFF_CODE_RID |  2 |        |       8 (0)|
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("RID"=42)
filter("RID"=42)

Statistics
----------------------------------------------------------
  1 recursive calls
  0 db block gets
 19 consistent gets
 10 physical reads
  0 redo size
603 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  3 rows processed

/* When searching on just RID column, the CBO knows there are only 6 distinct CODE values. The CBO can effectively probe the index in 6 different locations and retrieve all the necessary rows.

At 19 consistent gets though, it's not as good as the 7 consistent gets with the previous index. However, it's not too bad and much better than the approx 2635 consistent gets required for a full table scan.

Perhaps the new index will suffice, making the overheads associated having a second index unnecessary ... */


SELECT /*+ NO_INDEX_SS(ziggy_stuff ix_ziggy_stuff_code_rid) */ *
FROM ziggy_stuff
WHERE rid = 42;

---------------------------------------------------------------------------------
| Id | Operation        | Name        | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|  0 | SELECT STATEMENT |             |    2 |    26 |    552 (14)|
|* 1 | TABLE ACCESS FULL| ZIGGY_STUFF |    2 |    26 |    552 (14)|
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RID"=42)

Statistics
----------------------------------------------------------
   1 recursive calls
   0 db block gets
2636 consistent gets
   0 physical reads
   0 redo size
 581 bytes sent via SQL*Net to client
 416 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   3 rows processed

-- Sure looks that way: The skip scan is a good compromise.
Unique Scan From the Oracle 11gR2 docs:
In contrast to an index range scan, an index unique scan must have either 0 or 1 rowid associated with an index key. 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 other words the index must be created as a unique index either using the CREATE UNIQUE INDEX syntax or as a byproduct of the default creation (not deferrable) of a primary key or unique constraint.
CREATE INDEX nui_t on t(testcol);

SELECT COUNT(*)
FROM t
WHERE testcol = 2;

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM t
WHERE testcol = 2;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------
| Id | Operation        | Name  | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|  0 | SELECT STATEMENT |       |    1 |    13 |       1 (0)| 00:00:01 |
|  1 | SORT AGGREGATE   |       |    1 |    13 |            |          |
|* 2 | INDEX RANGE SCAN | NUI_T |    1 |    13 |       1 (0)| 00:00:01 |
------------------------------------------------------------------------

DROP INDEX nui_t;

CREATE UNIQUE INDEX ui_t on t(testcol);

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM t
WHERE testcol = 2;

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|  0 | SELECT STATEMENT |      |    1 |    13 |       0 (0)| 00:00:01 |
|  1 | SORT AGGREGATE   |      |    1 |    13 |            |          |
|* 2 | INDEX UNIQUE SCAN| UI_T |    1 |    13 |       0 (0)| 00:00:01 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("TESTCOL"=2)

-- other ways of producing a UNIQUE SCAN
WHERE testcol IN (2)
WHERE testcol IN (2,3)
WHERE testcol IN (SELECT COUNT(*) FROM user_tables WHERE table_name = 'T')
WHERE testcol BETWEEN 3 AND 3 -- a range scan results if the values are not identical

Related Topics
Explain Plan
Hints
Histograms
Indexes
SYS_OP_UNDESCEND
Tuning

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved