[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次.