DESC index is not being used
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 |
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 |
0