If you are wondering how to use the Oracle index_desc hint, then the Oracle index_desc hint is used when descending the existing index on a range scan. This hint is valid on the statement that uses index range scan and if you have an index on ascending. Then Oracle does the descending order of ascending order index.
If the database uses a partition, then the statement result going to be in descending order in each partition. This helps improve performance when searching max.
-- select /*+ index_desc(employee_table, salary_idx) */ employee_name, max(salary) from employee_table group by employee_name; --
If the table has a descending order index then ignore the hind by oracle. Oracle goes through the index in ascending order without considering HINT.
Example of Oracle index_desc
I would like to create a table for example. I would like to run a query with and without HINT index_desc. Below I like to add a considerable number of records into a table.
--
CREATE TABLE employee_table (
emp_id VARCHAR2(50) ,
employee_name VARCHAR2(50),
age NUMBER,
salary NUMBER,
PRIMARY KEY(emp_id)
);
--Now I insert the data into table
--
declare
-- insert 100000 records into database
i integer:=0;
begin
LOOP
i:= i+1;
INSERT INTO employee_table (emp_id, employee_name, age, salary)
VALUES ('Emp-'||i, 'Emp-Name'||i, round(dbms_random.value(1000,9000),0), round(dbms_random.value(18,65),0));
EXIT WHEN i > 999999;
END LOOP;
end;
--Now I want to run the below query. Also, I’ll take the Explain Plan to check how this query executes without Oracle index_desc
-- select * from employee_table where salary = 9000 --
According to explain plan here we use an index but not in descending order. This is not the best example for checking this Oracle index_desc but you can have a situation we can gain by scanning through the descending order let’s check the below query with HINT.
-- select /*+ index_desc(employee_table, salary_idx) */ * from employee_table where salary = 3000 --
According to the Explain plan, it is oracle will use the existing index in descending order. So, we can force Oracle to use our index in descending order by using the Oracle index_desc hint. Therefore, we can use the existing same keyword for descending order as well.
