Oracle SQL index ignored when using OR

Question

I've got a table with 2 columns and around 8 million rows. equip_no and op_id. These columns are indexed both individually and together in a single index. The values for both are all nearly unique.

I've found when performing a query which uses an AND on the 2 columns the indexes are used fine and performance is great, 8 million rows with sub second response times.

select *
from asset_table
where equip_no like 'something%'
AND op_id like 'somethingElse%'

However, if I take exactly the same query and change the AND to OR I suddenly end up with a TABLE ACCESS FULL in my plan and the query takes 20 or 30 seconds.

select *
from asset_table
where equip_no like 'something%'
OR op_id like 'somethingElse%'    

My Oracle version is as follows in case that's helpful.

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

So I'm not entirely new to indexes but I've never really specialised in them. I was wondering if this was known behaviour or am I not doing something I should. I created the indexes with the following commands...

create index IDX_asset_TABLE_EQ_OPID
on asset_table(equip_no, op_id)

create index IDX_asset_TABLE_EQ
on asset_table(equip_no)

create index IDX_asset_TABLE_OPID
on asset_table(op_id)

Any help is appreciated. I've googled for quite some time and have had no luck so far.

Thanks

Answer 1

Check your table, column, and index stats and the information in the explain plan. Without that information it is hard to give a good answer. But I can explain the basics.

The 'and' makes your query more selective (less rows returned) which is what indexes are good for.

The 'or' makes your query less selective (returns more rows) which makes the full table scan look cheaper.

example:

 born in june and female  (1/12 * 1/2) = 1/24 of the rows 
 born in june or  female  (1/12 + 1/2) = 7/12 of the rows