How to improve Performance with Oracle SQL Join

Question

The situation is I have to join more than 10 different 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.

A table like that is often a design smell. EAV tables are a very poor way to do business in a relational database. — HLGEM
– HLGEM, Commented Oct 14, 2011 at 15:12

xdazz · Accepted Answer · 2011-10-14 07:49:53Z

1

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 ...

answered Oct 14, 2011 at 7:49

xdazz

161k38 gold badges255 silver badges278 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Guffa · Accepted Answer · 2011-10-14 07:35:30Z

0

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.

answered Oct 14, 2011 at 7:35

Guffa

703k112 gold badges760 silver badges1k bronze badges

Comments

ssedano · Accepted Answer · 2011-10-14 07:41:40Z

0

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.

answered Oct 14, 2011 at 7:41

ssedano

8,47210 gold badges63 silver badges99 bronze badges

Collectives™ on Stack Overflow

How to improve Performance with Oracle SQL Join

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related