Fully unwrap a view

Question

In Oracle, is there an easy way to fully unwrap a view? eg: If I have a view which is made up of selects on more views, is there some way to unwrap it to just select directly on real tables?

Answer 1

1. Get the query text of your view.

   SELECT text FROM dba_views
   WHERE owner = 'the-owner' AND view_name = 'the-view-name';
   

2. Parse. Search for view names within the query text.

3. Get the query text for each view name found. (see item 1.)

4. Replace each view name in the query with the related query text.

5. Do this recursively until there are no more views found.

Easy?

EDIT: The above instructions do not do everything required. Thinking about this a little more it gets hairy, grows legs, and maybe another arm. Finding column names, and column names that might be elaborate functions and subqueries. Bringing it all back together with the joins and clauses. The resulting query might look very ugly.

Somewhere within Oracle there may be something that is actually unwrapping a view. I don't know. I am glad I didn't use views that much in Oracle.

Answer 2

The concept of in-line views can be used to do this. Suppose you have these 2 views:

create or replace view london_dept as
select * from dept
where loc = 'LONDON';

and

create or replace view london_mgr as
select * from emp 
where job='MANAGER'
and deptno in (select deptno from london_dept);

In the second view's SQL, the reference to view london_dept can be replaced by an in-line view using the SQL from the london_dept view definition as follows:

select * from emp 
where job='MANAGER'
and deptno in (select deptno from (select * from dept
where loc = 'LONDON'));

Of course, you can now see that is overly verbose and could be simplified to:

select * from emp 
where job='MANAGER'
and deptno in (select deptno from dept where loc = 'LONDON');

Finally, some advice from Tom Kyte on the advantages and disadvantages of creating views of views