Re: views on views on views

Sorry for being a little late on that thread - the fact is that indeed
nesting views, especially when they are not plain old vanilla SQL, makes
life particularly interesting for the optimizer (I have even seen wrong
results once - UNION + left outer joins in the view). My mantra is to
try, as much as possible, to build views on "base tables"; and to keep
clear of the look-up kind of user-defined function.

The following query checks in user_dependencies what innocent-looking
views hide, some of you may find it useful (not least to convince the
developer or his manager that perhaps ....) - it purposely eliminates
underlying views but shows what they are based upon:

col "REFERENCES" format A35
col name format A40
select d.padded_name name,
       v.text_length,
       d."REFERENCES"
from (select name,
             lpad(name, level + length(name)) padded_name,
             referenced_name || ' (' || lower(referenced_type) || ')' 
"REFERENCES"
      from user_dependencies
      where referenced_type <> 'VIEW'
      connect by prior referenced_type = type
          and prior referenced_name = name
      start with type = 'VIEW') d
     left outer join user_views v
          on v.view_name = name
/

HTH

S Faroult


Amar Padhi wrote:
> Jared,
> I know of Java Architects who have designed web applications keeping
> data and application completely separate. One of them told me that
> this removes dependency on the database vendor. So tomorrow they can
> port and certify the application on some other platform. Well
> different school of thoughts... but then they are actually applying
> design concepts to have each tier do its job. And yes these guys use
> only Java against Oracle, no PL/SQL either. It was tough for me to
> digest this part.
>
> Thanks!
> amar
> www.amar-padhi.com <http://www.amar-padhi.com>
>


--
http://www.freelists.org/webpage/oracle-l

Other related posts: