[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优化实在太复杂.
