[20170328]使用with優化1例.txt

[20170328]使用with優化1例.txt

--//這個以前問的問題,http://www.itpub.net/thread-1932784-1-1.html,就是使用union all的情況下謂詞無法推入.
--//實際上使用with就可以很好的優化.

1.環境:
SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--建表
create table t11 as select rownum id1,rownum idx, 't11'||lpad('x',20,rownum) x1 from dual connect by level<=2e5;
create table t12 as select * from t11 where 1=0;

create table t21 as select rownum id1,rownum idy, 't21'||lpad('x',20,rownum) x2 from dual connect by level<=2e5;
create table t22 as select * from t21 where 1=0;

create table t31 as select rownum id3,rownum idz, 't31'||lpad('x',20,rownum) x3 from dual connect by level<=1e5;

--建立索引：
create  unique index pk_t11 on t11(id1);
create  unique index pk_t12 on t12(id1);

create  index  i_t11_idx on t11(idx);
create  index  i_t12_idx on t12(idx);

create  unique index pk_t21 on t21(id1);
create  unique index pk_t22 on t22(id1);

create  index pk_t31 on t31(idz);
create  unique index i_t31_id3 on t31(id3);

--建立檢視：
create view v_t1 as
select * from t11
union all
select * from t12;

create view v_t2 as
select * from t21
union all
select * from t22;

create view v_tall
as
select
v_t1.id1,
v_t1.idx,v_t1.x1,
v_t2.idy,v_t2.x2,
t31.idz,t31.x3,t31.id3
from v_t1,v_t2 ,t31
where v_t1.id1=v_t2.id1
and v_t1.idx = t31.idz;

--分析表忽略。Method_Opt => 'FOR ALL COLUMNS SIZE 1 '

2.測試:
SCOTT@test> alter session set statistics_level=all;
Session altered.

select * from v_tall where id3 =42;

SELECT v_t1.id1
      ,v_t1.idx
      ,v_t1.x1
      ,v_t2.idy
      ,v_t2.x2
      ,t31.idz
      ,t31.x3
      ,t31.id3
  FROM v_t1, v_t2, t31
WHERE v_t1.id1 = v_t2.id1 AND v_t1.idx = t31.idz AND t31.id3 = 42;

SQL_ID  3q0sph3p9471x, child number 1
-------------------------------------
select * from v_tall where id3 =42
Plan hash value: 190959487
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      1 |        |       |   308 (100)|          |      1 |00:00:00.14 |    1090 |
|   1 |  NESTED LOOPS                  |           |      1 |      1 |   124 |   308   (1)| 00:00:04 |      1 |00:00:00.14 |    1090 |
|   2 |   NESTED LOOPS                 |           |      1 |      1 |    83 |   306   (1)| 00:00:04 |      1 |00:00:00.14 |    1087 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T31       |      1 |      1 |    34 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  4 |     INDEX UNIQUE SCAN          | I_T31_ID3 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  5 |    VIEW                        | V_T1      |      1 |      1 |    49 |   304   (1)| 00:00:04 |      1 |00:00:00.14 |    1084 |
|   6 |     UNION-ALL                  |           |      1 |        |       |            |          |    200K|00:00:00.12 |    1084 |
|   7 |      TABLE ACCESS FULL         | T11       |      1 |    200K|  6640K|   302   (1)| 00:00:04 |    200K|00:00:00.03 |    1084 |
|   8 |      TABLE ACCESS FULL         | T12       |      1 |      1 |    49 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|   9 |   VIEW                         | V_T2      |      1 |      1 |    41 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|  10 |    UNION ALL PUSHED PREDICATE  |           |      1 |        |       |            |          |      1 |00:00:00.01 |       3 |
|  11 |     TABLE ACCESS BY INDEX ROWID| T21       |      1 |      1 |    34 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|* 12 |      INDEX UNIQUE SCAN         | PK_T21    |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|  13 |     TABLE ACCESS BY INDEX ROWID| T22       |      1 |      1 |    49 |     0   (0)|          |      0 |00:00:00.01 |       0 |
|* 14 |      INDEX UNIQUE SCAN         | PK_T22    |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F5BB74E1
   3 - SEL$F5BB74E1 / T31@SEL$2
   4 - SEL$F5BB74E1 / T31@SEL$2
   5 - SET$1        / V_T1@SEL$2
   6 - SET$1
   7 - SEL$3        / T11@SEL$3
   8 - SEL$4        / T12@SEL$4
   9 - SET$BE4AEC69 / V_T2@SEL$2
  10 - SET$BE4AEC69
  11 - SEL$9384AC1D / T21@SEL$5
  12 - SEL$9384AC1D / T21@SEL$5
  13 - SEL$42078B5C / T22@SEL$6
  14 - SEL$42078B5C / T22@SEL$6
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T31"."ID3"=42)
   5 - filter("V_T1"."IDX"="T31"."IDZ")
  12 - access("ID1"="V_T1"."ID1")
  14 - access("ID1"="V_T1"."ID1")

--//可以發現T11,T12是全表掃描,無法使用idx欄位的索引.而單獨這樣寫
select * from v_t1,t31 where v_t1.idx = t31.idz AND t31.id3 = 42;
--//是可以使用idx欄位索引的.感覺很奇怪,為什麼加入一個檢視以及連線條件就無法推入.
--//注:跟表T11大小無關,我測試加大記錄
create table t11 as select rownum id1,rownum idx, 't11'||lpad('x',20,rownum) x1 from dual connect by level<=4e5;
--//結果一樣.
--//我當時這樣寫,也不行,現在想想為什麼不想到with+MATERIALIZE呢?

SELECT *
  FROM (SELECT /*+ MATERIALIZE*/ *
          FROM v_t1, t31
         WHERE v_t1.idx = t31.idz AND t31.id3 = 42) x
      ,v_t2
WHERE x.id1 = v_t2.id1

--//使用with+MATERIALIZE,就可以優化這種語句,我的測試不加提示MATERIALIZE不行.

3.改寫如下:

WITH a
     AS (SELECT /*+ MATERIALIZE */ t31.idz
               ,t31.x3
               ,t31.id3
               ,v_t1.idx
               ,v_t1.x1
               ,v_t1.id1
           FROM v_t1, t31
          WHERE v_t1.idx = t31.idz AND t31.id3 = 42)
SELECT a.*, v_t1.*
  FROM a,v_t1 where v_t1.id1 =a.id1;

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0d1v4huxs72gc, child number 0
-------------------------------------
WITH a      AS (SELECT /*+ MATERIALIZE */ t31.idz
,t31.x3                ,t31.id3                ,v_t1.idx
,v_t1.x1                ,v_t1.id1            FROM v_t1, t31
WHERE v_t1.idx = t31.idz AND t31.id3 = 42) SELECT a.*, v_t1.*   FROM
a,v_t1 where v_t1.id1 =a.id1

Plan hash value: 3758650173

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                             |      1 |        |       |     7 (100)|          |      1 |00:00:00.01 |      24 |      1 |      1 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION       |                             |      1 |        |       |            |          |      1 |00:00:00.01 |      24 |      1 |      1 |       |       |          |
|   2 |   LOAD AS SELECT                 |                             |      1 |        |       |            |          |      0 |00:00:00.01 |      10 |      0 |      1 |   270K|   270K|  270K (0)|
|   3 |    NESTED LOOPS                  |                             |      1 |      1 |    68 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |      0 |      0 |       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID  | T31                         |      1 |      1 |    34 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
|*  5 |      INDEX UNIQUE SCAN           | I_T31_ID3                   |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
|   6 |     VIEW                         | V_T1                        |      1 |      1 |    34 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
|   7 |      UNION-ALL PARTITION         |                             |      1 |        |       |            |          |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
|   8 |       TABLE ACCESS BY INDEX ROWID| T11                         |      1 |      1 |    34 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
|*  9 |        INDEX RANGE SCAN          | I_T11_IDX                   |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
|  10 |       TABLE ACCESS BY INDEX ROWID| T12                         |      1 |      1 |    49 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|* 11 |        INDEX RANGE SCAN          | I_T12_IDX                   |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|  12 |   NESTED LOOPS                   |                             |      1 |      1 |   132 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |      1 |      0 |       |       |          |
|  13 |    VIEW                          |                             |      1 |      1 |    98 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |      1 |      0 |       |       |          |
|  14 |     TABLE ACCESS FULL            | SYS_TEMP_0FD9D661C_175E0481 |      1 |      1 |    68 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |      1 |      0 |       |       |          |
|  15 |    VIEW                          | V_T1                        |      1 |      1 |    34 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
|  16 |     UNION-ALL PARTITION          |                             |      1 |        |       |            |          |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
|  17 |      TABLE ACCESS BY INDEX ROWID | T11                         |      1 |      1 |    34 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
|* 18 |       INDEX UNIQUE SCAN          | PK_T11                      |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
|  19 |      TABLE ACCESS BY INDEX ROWID | T12                         |      1 |      1 |    49 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|* 20 |       INDEX UNIQUE SCAN          | PK_T12                      |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$4
   2 - SEL$1
   4 - SEL$1        / T31@SEL$1
   5 - SEL$1        / T31@SEL$1
   6 - SET$AD7CC163 / V_T1@SEL$1
   7 - SET$AD7CC163
   8 - SEL$661FCD0D / T11@SEL$2
   9 - SEL$661FCD0D / T11@SEL$2
  10 - SEL$A8E2213E / T12@SEL$3
  11 - SEL$A8E2213E / T12@SEL$3
  13 - SEL$D67CB2D2 / A@SEL$4
  14 - SEL$D67CB2D2 / T1@SEL$D67CB2D2
  15 - SET$7BE537C4 / V_T1@SEL$4
  16 - SET$7BE537C4
  17 - SEL$693A5C0E / T11@SEL$5
  18 - SEL$693A5C0E / T11@SEL$5
  19 - SEL$1FB8A168 / T12@SEL$6
  20 - SEL$1FB8A168 / T12@SEL$6

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T31"."ID3"=42)
   9 - access("IDX"="T31"."IDZ")
  11 - access("IDX"="T31"."IDZ")
  18 - access("ID1"="A"."ID1")
  20 - access("ID1"="A"."ID1")

--//僅僅做一個記錄,oracle優化實在太複雜.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2136165/，如需轉載，請註明出處，否則將追究法律責任。