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?