Re: views on views on views

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Fri, 27 Mar 2009 20:10:09 +0100
Message-ID: <49CD2491.9010207_at_roughsea.com>

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

Received on Fri Mar 27 2009 - 14:10:09 CDT