Re: views on views on views
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: amar.padhi@xxxxxxxxx
- Date: Fri, 27 Mar 2009 20:10:09 +0100
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>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
