Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Annamalai.

Asked: July 18, 2013 - 6:52 am UTC

Last updated: July 19, 2013 - 3:28 pm UTC

Version: 11G

Viewed 1000+ times

You Asked

I have some questions related to performance tuning of SQL Queries..

1. Will the OUTER JOIN (LEFT / RIGHT) always reduce the performance of the query ?

2. Creating views with 'n' no.of levels will degrade the performance?

For example, I have five tables "TABLE_1", "TABLE_2", "TABLE_3", "TABLE_4" and "TABLE_5".I want to create a view "VIEW_FINAL" by joining all these 5 tables.

I have two approaches for this..
Approach 1: Joining all these 5 tables in a single select query and create it as view "VIEW_FINAL".
Approach 2: Joining the tables "TABLE_1", "TABLE_2" and "TABLE_3" and creating it as VIEW_1. And, Joining the "VIEW_1", "TABLE_4 and "TABLE_5" and create the view "VIEW_FINAL".

Which of the above approach is the best with respect to performance..?

In this example, i have given only 2 levels. If i need to join 25+ tables, creating the view in one level is better ? or multiple levels will be better ?

The advantage of creating it in multiple levels will give more readability of the code. But, performance wise, which approach will be better ?

and Tom said...

1) nope, sometimes it makes it run much faster. Outer joins are not inherently "evil". They have a bad name for some reason.

If you *need* to outer join, you *need to outer join*. That is the only consideration for them.

2) this is likely - not because views of views of views are inherently bad but rather what tends to happen is

a) someone develops the end all, be all, generic view of the world.

b) someone develops a refined view on top of that to filter it, this refined view doesn't need all of the things (a) references

c) someone develops a refined view on top of (b) - it too doesn't need everything (b) needed

d) and so on....

and then someone joins (d) to (e) to (f) - you can see now that it would be the ugliest, biggest, nastiest query that hits a lot of stuff you don't care about.

I prefer a SINGLE LEVEL of views. Yes, there will be some repetition in their definition but I don't care about that. You can document that. You can maintain that.

You'll get a view that processes exactly only that which you actually need.

and I disagree with the readability. the code will select from a view. whether that view is 25 layers deep or 1 layer deep - the client code is identical. So it does nothing for the client code.

and it makes it more readable at the view level. You have a view whose goal is "X" and only "X". You can look at the single defining query and say "it does "X" and only "X"". If it were views of views of views - you'd have to layer them all back in to say that.

And if someone "fixes" a low level view - it ripples up through everything. maybe it fixes five other layered views but break 50 others.

Is this answer out of date? If it is, please let us know via a Comment