index on partitioned table doesn't enhance performance with a function in where clause

Question

I have this query

select col1,col2, x.id pk 
/*+ INDEX (some_index_on_col4)*/
from tbl1 y
,tbl2 x
where col2 = 'some_value' and col3 = 'U'
and x.col4 = dbms_lob.substr( REPLACE(y.PK_DATA,'"',''), 100, 1 )
;

the query is very slow, and when I explain the plan, it shows that the index is not used but a full table scan is used instead, if I remove

dbms_lob.substr( REPLACE(y.PK_DATA,'"',''), 100, 1 )

and say instead

x.col4 = 3456

it's working fine, how can I enhance this?

N.B. : tbl2 is partitioned

You may also post execution plans for both queries as described [here](http://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database?answertab=active#tab-top) — Marmite Bomber, Jun 29 '16 at 13:32
When you change it to `x.col4 = 3456` then Oracle knows there will only be one x.col4 value of interest (3456) and so can clearly use the index. With `dbms_lob.substr( REPLACE(y.PK_DATA,'"',''), 100, 1 )` there could be many different x.col4 values that are selected - maybe even **all** of them - so the optimizer may decide a full scan is preferable, — Tony Andrews, Jun 29 '16 at 15:04
actually there was no match and that is why the index wasn't used as a full scan was performed any way ... but when there is a match, the index is used — osama yaccoub, Jul 10 '16 at 11:25

score 1 · Answer 1 · answered Jun 29 '16 at 13:09

1

One obvious difference (and offen cause of not using index) is that the result of dbms_lob.substr( REPLACE(y.PK_DATA,'"',''), 100, 1 )is VARCHAR, not a NUMBER as 3456.

So if possible transform it with to_number.

But you will not get the same plan as for 3456 becaouse this is constant; the original query uses y.PK_DATA.

answered Jun 29 '16 at 13:09

Marmite Bomber

19,886
4
26
53

thank u , it helped a little, but the execution plan is still saying that it is making a full scan not using the index – osama yaccoub Jun 29 '16 at 13:34

score 0 · Accepted Answer · answered Jul 10 '16 at 11:25

0

actually there was no match and that is why the index wasn't used as a full scan was performed any way ... but when there is a match, the index is used

answered Jul 10 '16 at 11:25

osama yaccoub

1,884
2
17
47

index on partitioned table doesn't enhance performance with a function in where clause

2 Answers2