View Expansion in Oracle

开发者_如何学PythonSo we have some developers who went a little view happy. So now we have views that reference views that reference views, ad nauseum.

So what I want, in order to assist me in Tuning, is to expand these views.

I want a function that takes a string and returns a string. The input string is the query, the output string is the same query without views.

CREATE OR REPLACE VIEW myView AS
SELECT * FROM emp

Using function/stored procedure "F":

F('SELECT * FROM myView') 

...would return:

SELECT * FROM ( SELECT * FROM emp)

1. Is there an Oracle package for this?
2. Does someone have code in:
   1. either SQL or PL/SQL
   2. In something else

Short answer:

1. Not at this time

2. Not that I'm aware of

UPDATE

It looks like Oracle 12c has exactly what you need: DBMS_UTILITY.expand_sql_text

http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_util.htm#ARPLS73973

One problem with what you are proposing is that there are usually multiple ways that a query involving views can be rewritten, so simply expanding the text of the views won't necessarily tell you a lot about how the query is being executed.

Since your purpose is tuning, I would suggest that the execution plans for the queries will probably give you the information you really need. This won't show the rewritten query, but it will show you all the actual tables and how they are referenced in executing the query.

The best way I know of to view the actual execution plan is:

SELECT /*+ gather_plan_statistics */ * FROM myView

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))

There are some methods to expand views but the output is so ugly it may not be useful.

12c Has a new procedure named DBMS_UTILITY.EXPAND_SQL_TEXT.

11g Has an undocumented procedure dbms_sql2.expand_sql_text.

Below is a simple example. The output may be helpful for the optimizer but it is probably not much use if you need a human-readable SQL statement.

create or replace view view1 as select 1 a, 2 b from dual;
create or replace view view2 as select a from view1;

declare
    v_output clob;
begin
    dbms_utility.expand_sql_text('select * from view2', v_output);
    dbms_output.put_line(v_output);
end;
/

Output:
SELECT "A1"."A" "A" FROM  (SELECT "A2"."A" "A" FROM  (SELECT 1 "A",2 "B" FROM "SYS"."DUAL" "A3") "A2") "A1"