oracle index in table join

Question

if I do

select * 
    from table1 
    where table1.col1 = 'xx' 
        and table1.col2 = 'yy' 
        and table1.col3= 'zz'`

the execution plan shows full table scan. The indexes on this table exist for col4 and col5. Do I need to set an index on each one of col1,col2,col3 to make the query perform better?

Also if the query is like this:

select * 
    from table1,table2 
    where table1.col1=table2.col2 
        and table1.col2 = 'yy' 
        and table1.col3= 'zz'

If we create an index on col1 and col2, will it suffice?

Mark Byers Mark Byers · Accepted Answer · 2011-03-09T22:38:33

You should try adding indexes on the columns that you are using in the query:

table1 col1
table1 col2
table1 col3
table2 col2

Note that it can also be advantageous in some cases to have multi-column indexes, for example:

table1 (col2, col3)

It's hard to predict which index will work best without knowing more about your data, but you can try a few different possibilities and see what works best.

oracle index in table join

1 Answers