Join condition on complex views

Question

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?

Answer 1

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.

Answer 2

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