Oracle INDEX_DESC | How to Change Performance

Asiri Gunasena

Published:

SQL

oracle INDEX_desc

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
--
oracle index_desc

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
--
oracle index_desc

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.

Resource: http://www.dba-oracle.com/t_index_desc_hint.htm

Categories SQL
ennicode

Address: 89/1 Rabbegamuwa, Handessa, Kandy, Central, Sri Lanka

Email to: Primary: [email protected]

Services

E-Learning

Company Websites

Support and Configuration work

Banners, Covers, and Post

Web Development & Configurations

Content Writing and Marketing

Contact

Ennicode