[20170620]11G 12c expand sql text.txt

[20170620]11G 12c expand sql text.txt

--//原来写的脚本只能分别在11g,12c单独使用.上午花一点点时间.把两者整合起来.
--//讨论链接:http://www.itpub.net/thread-2088981-1-1.html
--//再次感谢solomon_007的指点:

set long 20000
set serveroutput on
declare
    L_sqltext clob := null;
    l_version varchar2(3) := null;
    l_sql     clob := null;
    l_result  clob := null;
begin
    select regexp_replace(version,'\..*') into l_version from v$instance;
    select sql_fulltext into l_sqltext  from v$sqlarea where sql_id='&&1';

    if l_version = '11' then
       l_sql := 'begin
                   dbms_sql2.expand_sql_text( :a,:b );
                 end;';

    elsif l_version = '12' then

      l_sql := 'begin
                  dbms_utility.expand_sql_text(:a,:b);
                end;';
    end if;
    execute immediate l_sql using in l_sqltext,out l_result;
    dbms_output.put_line(l_result);
end;
/
set serveroutput off

--//继续拿原来的例子测试:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t1( id, col1, col2, col3, col4, col5, padding )
cache
pctfree 95 pctused 5
-- compress for query low
as
select
        1, 100 , 200 , 300 , 400 , 500,rpad('x',100)
from
        all_objects
where
        rownum <= 50000 ;
       
execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1')
 
select
        /*+ gather_plan_statistics find this */
        id, max(val) as high_val
from
        t1
unpivot include nulls (
        val for source in (col1, col2, col3, col4, col5)
)
group by id
order by id
;

--//获得sql_id=2v7uzcnf4kj9s.

SCOTT@book> @ &r/expand_sql_text 2v7uzcnf4kj9s
SELECT "A1"."ID" "ID",MAX("A1"."VAL") "HIGH_VAL" FROM  ( (SELECT "A3"."ID" "ID","A3"."PADDING" "PADDING",'COL1' "SOURCE","A3"."COL1" "VAL" FROM "SCOTT"."T1" "A3") UNION ALL  (SELECT "A4"."ID" "ID","A4"."PADDING" "PADDING",'COL2' "SOURCE","A4"."COL2"
"VAL" FROM "SCOTT"."T1" "A4") UNION ALL  (SELECT "A5"."ID" "ID","A5"."PADDING" "PADDING",'COL3' "SOURCE","A5"."COL3" "VAL" FROM "SCOTT"."T1" "A5") UNION ALL  (SELECT "A6"."ID" "ID","A6"."PADDING" "PADDING",'COL4' "SOURCE","A6"."COL4" "VAL" FROM
"SCOTT"."T1" "A6") UNION ALL  (SELECT "A7"."ID" "ID","A7"."PADDING" "PADDING",'COL5' "SOURCE","A7"."COL5" "VAL" FROM "SCOTT"."T1" "A7")) "A1" GROUP BY "A1"."ID" ORDER BY "A1"."ID"

PL/SQL procedure successfully completed.

--//toad格式化看看:
/* Formatted on 2017/6/20 16:10:46 (QP5 v5.269.14213.34769) */
  SELECT "A1"."ID" "ID", MAX ("A1"."VAL") "HIGH_VAL"
    FROM ( (SELECT "A3"."ID" "ID"
                  ,"A3"."PADDING" "PADDING"
                  ,'COL1' "SOURCE"
                  ,"A3"."COL1" "VAL"
              FROM "SCOTT"."T1" "A3")
          UNION ALL
          (SELECT "A4"."ID" "ID"
                 ,"A4"."PADDING" "PADDING"
                 ,'COL2' "SOURCE"
                 ,"A4"."COL2" "VAL"
             FROM "SCOTT"."T1" "A4")
          UNION ALL
          (SELECT "A5"."ID" "ID"
                 ,"A5"."PADDING" "PADDING"
                 ,'COL3' "SOURCE"
                 ,"A5"."COL3" "VAL"
             FROM "SCOTT"."T1" "A5")
          UNION ALL
          (SELECT "A6"."ID" "ID"
                 ,"A6"."PADDING" "PADDING"
                 ,'COL4' "SOURCE"
                 ,"A6"."COL4" "VAL"
             FROM "SCOTT"."T1" "A6")
          UNION ALL
          (SELECT "A7"."ID" "ID"
                 ,"A7"."PADDING" "PADDING"
                 ,'COL5' "SOURCE"
                 ,"A7"."COL5" "VAL"
             FROM "SCOTT"."T1" "A7")) "A1"
GROUP BY "A1"."ID"
ORDER BY "A1"."ID";

--//可以看出使用unpivot实际上内部要全表扫描T1 5次.