views vs joins performance?

it404
it404 used Ask the Experts™
on
Hi,
I've been told that joins are a bit faster than views but my somewhat limited testing on a medium large sized dataset shows the joins being faster by 10-20%.  

Anybody have any information for me on this?  

Are there differences in how Oracle is configured that would affect this?  

Tks for any info.  DRE
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Actually since a view is simply a stored query, you should not notice any difference in performace between a query against the view and a query against the base tables.

I think if you are noticing a 10-20% difference between a view and a join, then you are not making an *exact* comparison between the two.



Ali
I believe you are comparing chalk with cheese, a view is a filtered select, a join is self explanitory.

You can have a view of several tables joined together, the logic is that the complete select is performed, then the view is applied (data filtered) and only data that complies with the view is seen.  Views can be used for security or to make queries appear less complicated.

In ways of comparison, I would say that as a view is an extra layer on top of any joins, queries using them will in general be slower than queries without them.

Commented:
You can use a materialzed view to get some performance. The query parts of the view definitions will be performed upon materializing it and there you save the time in your production queries.

Usually views act something like a macro. If you execute a query that is based on a view the resulting sql will be a statement that performs the query as if there was no view defined. The overhead for this operation is not very large.

You cannot add hints to views (at least in 8.0 that where we tried it the first&the last time).

Building a handcrafted statement allows you to optimize it manually (hints, etc.). So you can improve performance in very complex queries. But this pays only in rare cases (more that (say) six tables joined with relations in the data that are not derivable from declaration).
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
M-Ali, I'm pretty sure its the same. I took the qry with the views and started substituting directly from the view definition untill there were no more views.  

In general thouhg is there any difference in how oracle goes about its buisness in retrieving the info with views vs only joins that could make the difference I'm observing?  DRE
Commented:
run explain plan on the view and the join and see the results...

they should be the same.

the other overhead is looking up the view to get the sql before parsing... that should be it.

be careful when adding where clauses to views because they may behave differently than if you made the entire thing a real join in the first place...  

i haven't done this since about version 5 when i thought a whole bunch of views upon views would simplify the schema into business logic, and found that it was too slow to use. so i went to direct sql for almost everything and have not recommended views ever since.

randy

Commented:
>>run explain plan on the view and the join and see the results...they should be the same.

Agree with randyd on this. Also as randyd mentions there will be a slight overhead for "looking up the view to get the sql before parsing" but it should be a marginal difference.

Since you notice a difference, suggest you use explain plan on both and observe the plans. Maybe your queries are different (if you add some clause to the view query), or maybe it causes you to use different indexes. Looking at the explain plan output will help you here.


HTH
Ali

Author

Commented:
Tks R for suggesting the explain plan.  I dont know why I didnt think of that nor why a room full of dbas didnt think of that either.  A bit obvious in hindsight.

Anyway, that uncovered at least one problem.  It seems the db is choosing not to use one of the indexes for some reason.  We set a hint /*+ RULE */ and it decided to do so.  The plans match with the rule. The db has grown very quickly and seems to be a bit confused about some stuff(we reeindex it periodically).  What to do about it is perhaps a subject of another question.  Thanks. DRE

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial