Oracle Descending Index FAQs with examples

1. What is a Descending Index in Oracle?

A Descending Index is an index that stores column values in descending (highest to lowest) order instead of the default ascending order.

It improves the performance of queries that frequently sort data in descending order.

Example:

CREATE INDEX idx_salary_desc
ON employees(salary DESC);

2. Why do we use a Descending Index?

Descending indexes are used to:

• Speed up ORDER BY ... DESC queries
• Improve sorting performance
• Reduce sorting operations
• Improve query performance

3. How do you create a Descending Index?

Syntax:

CREATE INDEX index_name
ON table_name(column_name DESC);

Example:

CREATE INDEX idx_hiredate_desc
ON employees(hire_date DESC);

4. When should you use a Descending Index?

Use a Descending Index when queries frequently retrieve the highest or latest values.

Example:

SELECT *
FROM employees
ORDER BY salary DESC;

Or:

SELECT *
FROM employees
ORDER BY hire_date DESC;

5. What is the default index order in Oracle?

By default, Oracle creates indexes in ascending order.

Example:

CREATE INDEX idx_salary
ON employees(salary);

This is equivalent to:

CREATE INDEX idx_salary
ON employees(salary ASC);

6. Difference between Ascending and Descending Indexes

Ascending Index	Descending Index
Stores values in ascending order	Stores values in descending order
Default index type	Must be specified using DESC
Best for ORDER BY ASC	Best for ORDER BY DESC

7. Can a Descending Index contain multiple columns?

Yes.

Example:

CREATE INDEX idx_salary_dept
ON employees(salary DESC, department_id);

Another example:

CREATE INDEX idx_hire_salary
ON employees(hire_date DESC, salary DESC);

8. Can a Descending Index be Unique?

Yes.

Example:

CREATE UNIQUE INDEX idx_email_desc
ON employees(email DESC);

The values remain unique even though they are stored in descending order.

9. Can Oracle use a Descending Index for ORDER BY?

Yes.

Example:

SELECT *
FROM employees
ORDER BY salary DESC;

Oracle can use the Descending Index to avoid an additional sort operation.

10. Can Oracle use a Descending Index for WHERE clauses?

Yes.

Example:

SELECT *
FROM employees
WHERE salary = 60000;

Oracle can use the Descending Index for equality searches.

11. How do you view Descending Indexes?

SELECT index_name,
       column_name,
       descend
FROM user_ind_columns;

The DESCEND column shows either ASC or DESC.

12. How do you drop a Descending Index?

DROP INDEX idx_salary_desc;

13. What are the advantages of a Descending Index?

• Faster ORDER BY DESC queries
• Improves sorting performance
• Reduces sorting overhead
• Supports equality searches
• Improves performance for retrieving latest or highest values

14. What are the disadvantages of a Descending Index?

• Uses additional storage space
• Slows INSERT, UPDATE, and DELETE operations because the index must be maintained
• May provide little benefit if queries rarely sort in descending order

15. Interview Example

Create a table:

CREATE TABLE employees (
  employee_id NUMBER,
  first_name  VARCHAR2(50),
  salary      NUMBER
);

Create a Descending Index:

CREATE INDEX idx_salary_desc
ON employees(salary DESC);

Run the query:

SELECT *
FROM employees
ORDER BY salary DESC;

Oracle can use the Descending Index to return rows in descending salary order more efficiently.

16. Real-World Example

Suppose an HR application frequently displays the highest-paid employees.

Query:

SELECT *
FROM employees
ORDER BY salary DESC;

Create the index:

CREATE INDEX idx_salary_desc
ON employees(salary DESC);

The query can retrieve employees ordered by salary without performing an additional sort.

17. Can a Descending Index improve TOP-N queries?

Yes.

Example:

SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

A Descending Index helps Oracle efficiently retrieve the top 10 highest-paid employees.

18. How do you check if Oracle is using a Descending Index?

Use an execution plan.

EXPLAIN PLAN FOR
SELECT *
FROM employees
ORDER BY salary DESC;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

If the execution plan shows an index access path such as INDEX FULL SCAN (DESCENDING) or another appropriate index scan, Oracle is using the Descending Index.

Key Point:
Descending indexes are useful when your queries frequently sort or fetch data in descending order, especially for latest records and TOP-N queries.