SQL Language (MOSC)

MOSC Banner

DESC index is not being used

edited Jun 5, 2012 10:04PM in SQL Language (MOSC) 5 commentsAnswered
I have Oracle database 11.2 running. I have been under the impression that Oracle would use DESC index for better performance if I select MAX () ..., but this is not true. Example:
CREATE INDEX IX1_TASK_PROCESS_BATCH ON TASK_PROCESS_BATCH (seq);
CREATE INDEX IX2_TASK_PROCESS_BATCH ON TASK_PROCESS_BATCH (seq DESC);

EXEC Dbms_Stats.gather_table_stats ('','TASK_PROCESS_BATCH');

EXPLAIN PLAN FOR
SELECT MAX (SEQ) FROM TASK_PROCESS_BATCH;

SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
Plan hash value: 2378415181 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                        |     1 |     5 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |                        |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IX1_TASK_PROCESS_BATCH |     1 |     5 |     3   (0)| 00:00:01 |

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center