Skip to Main Content
  • Questions

  • Why INSTR more effective than IN for use in second condition of composite index?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alexey.

Asked: March 22, 2017 - 3:00 pm UTC

Last updated: March 23, 2017 - 3:46 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi, Tom!

I have big table my_table with columns like (my_date date, my_state varchar2(20)).
Table indexed by composite index: create index idx_my_index on my_table(my_date, my_state).

Please tell me, why access with IN by my_state much slower (by cost in plan) than with INSTR for this usecases:

--slow
select * from my_table
where my_date >= sysdate-1 and my_date < sysdate
  and my_state IN ('STATE_A','STATE_B')


--fast
select * from my_table
where my_date >= sysdate-1 and my_date < sysdate
  and INSTR(';STATE_A;STATE_B;',';'||my_state||';') != 0


Many thanks!

and Chris said...

So is using instr actually faster, or does it just have a higher cost?

If it's the latter, remember that the cost is an estimate. A higher cost query can still be faster than a lower cost one!

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9534189900346120939

I'd be surprised if instr turned out to be notably better than IN when it comes to running your queries.

In any case to help us answer this we need a complete test case. In addition to the table data you've provided, we also need:

- Inserts to for the data
- The execution plans for your queries

https://blogs.oracle.com/sql/entry/how_to_create_an_execution

Send us those and we'll see what's going on.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I think it would be faster yes.

Gh.., March 22, 2017 - 6:02 pm UTC

I can guess ans not surprised if instr faster.
If the first query run full table scan because for example the selectivity make the optimiser not tu use thé index . I doubt that the index is used in 1st qry.
As for the 2nd one where the instr is used but in same time the the first key of the composite is the Date so oracle estimated using the index on behalf of that logic.

So the 2nd is surely faster as the index is engaged which is not the case of the 1st one.
Connor McDonald
March 23, 2017 - 3:46 am UTC

We can guess...but we'll wait until we see the plans.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.