oracle index in table join

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 开发者_如何学JAVAbetter?

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?

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.