Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If i join two/more complex views (views that are made up by joining two/more tables); would it result in multiple iteration of reads on the base tables?

Would it not be more efficient just to have the join conditions on the base tables itself?

This is a problem i've analysed in our Production Oracle 10g R2 (ASM disk groups) environment, the sql queries are dynamically generated on the fly by the application itself and instead of using Tables to extract results, composite views are used.
Moreover, conditional joins are frequently implemented on these composite views.

Can such joins on the complex views unnecessarily cause high cpu utilization and also eat up the precious buffer cache?

share|improve this question
add comment

2 Answers

It is certainly possible that joining complex large views can cause additional overhead but the best way is to test. Create queries on the base tables that yield the same results as using the complex views.
Then run a series of queries against the base tables and compare the same series against the complex views. I've seen one database structure where all application access was through views and speed of response issues started appearing with the reporting tool as the size of the database grew. As a_horse_with_no_name points out there are built in tools to help you analyze what is going on under the hood. With large applications it is hard to point to one thing and definitively say "this caused the problem".

Test in development, change one thing, test again and repeat. This approach keeps cautious managers happier than just saying "the problem is this".

share|improve this answer
add comment

Never assume or "belief" the cause for something. Test it.

In your case run an EXPLAIN PLAN on the statements in question and check if the plan with views is more expensive than the corresponding SELECT using the base tables.

You can also use SQL*Plus' autotrace feature to check out logical IO done by both queries.

If that still doesn't give enough information you can turn on tracing and examine the output of the TKPROF formatting utility of the trace file.

share|improve this answer
    
i do have the explain of such dynamic queries using joins on complex views. however, i've not tested the same queries using base tables. i need help in understanding the explain plan. –  Jack Aug 27 '11 at 21:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.