CODEWITHSUNDEEP
oracle-databaseindexingquery-performance
reza ramezani matin
reza ramezani matin

Reputation: 1474

Function index does not work in oracle where it is used with other operator

You assume this simple query:

select name, code 
  from item 
 where length(code) > 5

Due to avoiding of full access table, there is an function-index on length(code) by following command:

create index index_len_code on item(length(code));

The optimizer detects the index and use it(INDEX RANGE SCAN). Nonetheless the optimizer does not detect the above index for the below query:

select i.name, i.code
  from item i, item ii
 where length(i.code) - length(ii.code) > 0

When I see the execution plan, it is the access full table, not to be index range scan while index is existed on length(code).

Where is wrong and what is wrong?

Upvotes: 0

Views: 593

Answers (1)

user5683823
user5683823

Reputation:

If you have an EMP table with a column HIREDATE, and that column is indexed, then the optimizer may choose to use the index for accessing the table in a query with a condition like

... HIREDATE >= ADD_MONTHS(SYSDATE, -12)

to find employees hired in the last 12 months.

However, HIREDATE has to be alone on the left-hand side. If you add or subtract months or days to it, or if you wrap it within a function call like ADD_MONTHS, the index can't be used. The optimizer will not perform trivial arithmetic manipulations to convert the condition into one where HIREDATE by itself must satisfy an inequality.

The same happened in your second query. If you change the condition to

... length(i.code) > length(ii.code)

then the optimizer can use the function-based index on length(code). But even in your first query, if you change the condition to

... length(code) - 5 > 0

the index will NOT be used, because this is not an inequality condition on length(code). Again, the optimizer is not smart enough to perform trivial algebraic manipulations to rewrite this in a form where it's an inequality condition on length(code) itself.

Upvotes: 4

Related Questions