CODEWITHSUNDEEP
sqloracle11gsqlperformance
Alex
Alex

Reputation: 347

SQL with OR operator bad performance

I have a table with an indexed field year, and I'm experiencing bad performance with some query and I can' understand why. With a query like this:

select ..... where year = 2016 ...

I'm getting good performance, but with a query like this:

select .... where ((year = 2016 and month = 1) or (year = 2015 and month = 7)) ...

things turn really slow...why is not used the index over the year field in this last case? I know why the perfomance would be bad in a query like:

select .... where year = 2016 or month = 1

But why in my case?

Thank you in advance!

Upvotes: 1

Views: 988

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

Build an index on year and month and use:

where (year, month) in ( (2016, 1), (2015, 7) )

Oracle should be smart enough to use the composite index for your query. This version is more concise and easier to write and to read.

Upvotes: 3

Related Questions