Join condition on complex views

oracleview

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?

Best Answer

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.