Hello David,
Oracle won't use more than one index per table in the same query (unless you
have subqueries).
You should have information about cardinality to select the best indexes. How
many rows do each table has, and how many rows exist for each value in the
condition fields. This will tell you where to start. If no extra info is
available I'd start with the following:
TAB1.IND1: (col1)
TAB2.IND1: (col1, col2, col3, col4)
TAB3.IND1: (col2, col3, col4)

I think you will be fine with that. Anyway, if your tables do have information
you could do a test creating a few more indexes (like the ones you suggested),
asking Oracle to gather statistics ant watching your query's execution plan to
see which are the indexes Oracle decides to use.
Regards,

--Claudio


On Tue, 27 Feb 2007 16:41:17 +0100, David NGUYEN wrote
> hello experts,
>
> i have someting like that...
>
> select * from .....
> .......
> where tab1.col1= tab2.col1
> and tab3.col2 = tab2.col2
> and tab3.col3 = tab2.col3
> and tab3.col4 = tab2.col4
>
> what are the best indexes on colunms..? how many indexes do we
> need for the best performence i mean the indexes should be created
> on tab1.IDX1(col1)..and tab2.IDX2(col1) and tab2.IDX(col2,col3,col4)
> and tab3(col2,col3,col4) etc.... what are your suggestions...?
>
> thanks in advance.
> david..
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html


Oracle LazyDBA home page