Follow that Index

April 7, 2010

A thread about access paths and hints on the OTN forums caught my attention.  The thread started with the following statement:

When I used Index Range Scan Descending hint for two select statements, it worked with one and not with the other.

The thread evolved a bit into whether or not the hint was valid, and what would happen if another access path had a lower cost than the hinted access path.  It was an interesting discussion, including a brief question about whether a hint could result in Oracle returning the wrong resultset for a query.  In the thread I put together a test case that demonstrated what happens with the potential existence of NULL values when index hints are used against those columns that permit NULL values.

Creating the table, inserting 10,000 rows into the test table, creating an index on the second column, and then gathering the statistics for the table and indexes:

CREATE TABLE T1 (
  C1 NUMBER,
  C2 NUMBER,
  C3 VARCHAR2(100),
  PRIMARY KEY (C1));

INSERT INTO T1
SELECT
  ROWNUM,
  DECODE(MOD(ROWNUM,100),0,NULL,ROWNUM),
  RPAD('A',100,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

COMMIT;

CREATE INDEX IND_T1_C2 ON T1(C2);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)

In the above table, 1 row of every 100 rows contains a NULL value in column C2. If we were to tell Oracle to order the rows using that column, Oracle could not use the IND_T1_C2 index to speed up the ordering of the rows because the rows containing the NULL values are not contained in the index.

SELECT
  *
FROM
  T1
ORDER BY
  C2;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

The execution plan is as expected, using a full table scan:

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       |   217 (100)|          |
|   1 |  SORT ORDER BY     |      | 10000 |  1054K|  2376K|   217   (2)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |  1054K|       |    11  (10)| 00:00:01 |
-----------------------------------------------------------------------------------

Now let’s repeat the test, making certain that no NULL values are introduced in column C2:

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
  C1 NUMBER,
  C2 NUMBER,
  C3 VARCHAR2(100),
  PRIMARY KEY (C1));

INSERT INTO T1
SELECT
  ROWNUM,
  ROWNUM,
  RPAD('A',100,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

COMMIT;

CREATE INDEX IND_T1_C2 ON T1(C2);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,NO_INVALIDATE=>FALSE)

In the above, NO_INVALIDATE=>FALSE was used to make certain that the queries for this table are hard parsed, if re-executed.  Trying the query again, with a hinted access path:

SELECT /*+ INDEX(T1 IND_T1_C2) */
  *
FROM
  T1
ORDER BY
  C2;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

Oracle still cannot use that index to speed up retrieval, because the column C2 could possibly contain a NULL value, even though it does not:

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time   |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       |   217 (100)|          |
|   1 |  SORT ORDER BY     |      | 10000 |  1054K|  2376K|   217   (2)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |  1054K|       |    11  (10)| 00:00:01 |
-----------------------------------------------------------------------------------

So, if we fix the problem of potential NULL values and re-run the test:

ALTER TABLE T1 MODIFY (C2 NOT NULL);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,NO_INVALIDATE=>FALSE)

SELECT /*+ INDEX(T1 IND_T1_C2) */
  *
FROM
  T1
ORDER BY
  C2;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

What happens now? Oracle knows that all possible values for C2 will be in the index due to the NOT NULL constraint, so there is no chance that it could return the wrong result (missing rows):

SQL_ID  2d4j0qkfaynst, child number 0
-------------------------------------
SELECT /*+ INDEX(T1 IND_T1_C2) */   * FROM   T1 ORDER BY   C2

Plan hash value: 4220775576

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |   182 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        | 10000 |  1054K|   182   (1)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | IND_T1_C2 | 10000 |       |    22   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

The same execution plan is achieved even without the index hint due to the calculated cost of the plan, so we should perform a couple more tests while artificially increasing the cost for the index access:

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=200;

SELECT
  *
FROM
  T1
ORDER BY
  C2;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

The execution plan now shows a full table scan, rather than an INDEX FULL SCAN:

SQL_ID c9s457r2swafn, child number 1

SELECT   * FROM   T1 ORDER BY  C2

Plan hash value: 2148421099
-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       |   217 (100)|          |
|   1 |  SORT ORDER BY     |      | 10000 |  1054K|  2376K|   217   (2)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |  1054K|       |    11  (10)| 00:00:01 |
-----------------------------------------------------------------------------------

Now with an index hint to force the execution path:

SELECT /*+ INDEX(T1 IND_T1_C2) */
  *
FROM
  T1
ORDER BY
  C2;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

---

SQL_ID 7cxfwpzxqfsbz, child number 0

SELECT /*+ INDEX(T1 IND_T1_C2) */   * FROM   T1 ORDER BY   C2

Plan hash value: 4220775576

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |   364 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        | 10000 |  1054K|   364   (1)| 00:00:02 |
|   2 |   INDEX FULL SCAN           | IND_T1_C2 | 10000 |       |    45   (3)| 00:00:01 |
-----------------------------------------------------------------------------------------

The above shows that the hinted execution path was selected even though it now has a higher calculated cost.

You might be wondering if we need a descending index to potentially speed up retrieval if the rows retrieved by the SQL statement needed to be sorted in descending order, with the requirement that the SORT ORDER BY operation does not appear in the execution plan.  Let’s test, leaving the altered OPTIMIZER_INDEX_COST_ADJ parameter value in place:

SELECT /*+ INDEX(T1 IND_T1_C2) */
  *
FROM
  T1
ORDER BY
  C2 DESC;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID 3t3x7us3c0yyb, child number 0

SELECT /*+ INDEX(T1 IND_T1_C2) */   * FROM   T1 ORDER BY   C2 DESC

Plan hash value: 654729690

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |       |   570 (100)|          |
|   1 |  SORT ORDER BY               |           | 10000 |  1054K|  2376K|   570   (1)| 00:00:03 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1        | 10000 |  1054K|       |   364   (1)| 00:00:02 |
|   3 |    INDEX FULL SCAN           | IND_T1_C2 | 10000 |       |       |    45   (3)| 00:00:01 |
--------------------------------------------------------------------------------------------------

The above shows that we used an index full scan, like before, but now there is also a SORT ORDER BY operation, which sorted the rows in descending order.  Not quite what we need, so we need a different hint:

SELECT /*+ INDEX_DESC(T1 IND_T1_C2) */
  *
FROM
  T1
ORDER BY
  C2 DESC;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID bq9wha0byz2hm, child number 0

SELECT /*+ INDEX_DESC(T1 IND_T1_C2) */  * FROM   T1 ORDER BY C2 DESC

Plan hash value: 4067756747

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |   364 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        | 10000 |  1054K|   364   (1)| 00:00:02 |
|   2 |   INDEX FULL SCAN DESCENDING| IND_T1_C2 | 10000 |       |    45   (3)| 00:00:01 |
-----------------------------------------------------------------------------------------

This time we were able to avoid the sort operation, as Oracle was able to simply read the index in reverse order.  You might wonder if this would happen automatically, without a hint.  First, we need to reset the OPTIMIZER_INDEX_COST_ADJ parameter:

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=100;

Now a quick test:

SELECT
  *
FROM
  T1
ORDER BY
  C2 DESC;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID 0790s346b7qsv, child number 0

SELECT   * FROM   T1 ORDER BY  C2 DESC

Plan hash value: 4067756747

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |   182 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        | 10000 |  1054K|   182   (1)| 00:00:01 |
|   2 |   INDEX FULL SCAN DESCENDING| IND_T1_C2 | 10000 |       |    22   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

So, the answer is yes – Oracle is able to automatically read an index in reverse order to avoid a sort operation without a hint, even when all columns will be returned from a table.