How to improve Performance with Oracle SQL Join

The situation is I have to join more than 10 differ开发者_C百科ent table. In the SQL I am joining the same table 5 times. The query looks like this.

select * from 
Tab1
join Tab2 on Tab1.x = Tab2.x
.
.
.
join Tab10 t10 on
t10.x = 'xx' and 
t10.y = 'yy' and
t10.z = 'zz' 

join Tab10 t11 on
t11.x = 'aa' and 
t11.y = 'bb' and
t11.z = 'cc' 

join Tab10 t12 on
t12.x = 'dd' and 
t12.y = 'ee' and
t12.z = 'ff' 

join Tab10 t13 on
t13.x = 'gg' and 
t13.y = 'hh' and
t13.z = 'ii' 

join Tab10 t14 on
t14.x = 'jj' and 
t14.y = 'kk' and
t14.z = 'll' 

The reason why this Tab10 is joined 5 times is get the different values based on the parameter. Is it possible to rewrite Tab10 join in a better way? I also noticed due to this Tab10 join the performance is bad.

You don't need to join 5 times, use or instead.

.
.
.
join Tab10 t10 on 
(t10.x = 'xx' and t10.y = 'yy' and t10.z = 'zz') or  
(t10.x = 'aa' and t10.y = 'bb' and t10.z = 'cc') or 
(t10.x = 'dd' and t10.y = 'ee' and t10.z = 'ff') or ...

Having a few joins doesn't have to mean bad performance. Create an index for the Tab10 table with the three fields that you use in the joins.

Don't use select *, that will reduce the performance. You will be fetching a lot of data that you don't use.

Sometimes a subselect is more efficient than a left outer join one thing you should bear in mind is in the where clause filter by the more restrictive first. As Guffa said, adding indexes is a good point. But remember that if you add indexes in a table that is far more writes than reads, it might slow down updates/insert at that table.