Database Tuning (MOSC)

MOSC Banner

DESC Index Is Not Being Used

edited Jun 6, 2012 1:16AM in Database Tuning (MOSC) 6 comments
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);

There are more than 840K rows in this table, and SEQ is unique and not null.


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 

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