分享程序员开发的那些事...
更新时间：2022-09-13 11:03:02
[20150710]11G谓词推入问题3.txt
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 t1 as select rownum id1, trunc(rownum/10)+1 id2,cast('t1test' as varchar2(20)) pad1 from xmltable('1 to 100000');
create table t2 as select rownum id1, cast('t2test' as varchar2(20)) pad2 from xmltable('1 to 100000');
create table t3 as select rownum id1, trunc(dbms_random.value(0,100)) x1,trunc(dbms_random.value(0,200)) x2,cast('t3test' as varchar2(20)) pad3 from xmltable('1 to 100000');
create table t4 as select * from t3 where 1=2;
--t4是空表,主要目的这样与生产系统信息符合.没有什么其它意思.
create unique index pk_t1 on t1 (ID1);
alter table t1 add constraint pk_t1 primary key (id1);
create index i_t1_id2 on t1 (ID2);
create index pk_t2 on t2 (ID1);
create index i_t3_id1 on t3 (ID1);
create index i_t4_id1 on t4 (ID1);
create or replace view v_tallz as
select t1.id1,t1.id2,t1.pad1,t2.pad2,sum_x1,sum_x2,(select dname from dept where deptno=t1.id1) dname
from t1,t2,
(select /*+ qb_name(t3t4) */ id1,sum(x1) sum_x1,sum(x2) sum_x2 from v_t3t4 group by id1) vx
where t1.id1=t2.id1 and t1.id1=vx.id1;
select /*+ push_pred(@t3t4) */ * from v_tallz where id2=2;
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID c8uj2b0d026wk, child number 1
-------------------------------------
select /*+ push_pred(@t3t4) */ * from v_tallz where id2=2
Plan hash value: 4283725052
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 52 (100)| | 10 |00:00:00.01 | 41 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 10 | 1 | 13 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 5 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 10 | 1 | | 0 (0)| | 1 |00:00:00.01 | 4 |
| 3 | NESTED LOOPS | | 1 | 99991 | 5272K| 52 (0)| 00:00:01 | 10 |00:00:00.01 | 41 |
| 4 | NESTED LOOPS | | 1 | 10 | 280 | 22 (0)| 00:00:01 | 10 |00:00:00.01 | 16 |
| 5 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 10 | 160 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 5 |
|* 6 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 10 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 3 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 10 | 1 | 12 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 11 |
|* 8 | INDEX RANGE SCAN | PK_T2 | 10 | 1 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 9 |
| 9 | VIEW PUSHED PREDICATE | | 10 | 1 | 26 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 25 |
|* 10 | FILTER | | 10 | | | | | 10 |00:00:00.01 | 25 |
| 11 | SORT AGGREGATE | | 10 | 1 | 27 | | | 10 |00:00:00.01 | 25 |
| 12 | VIEW | V_T3T4 | 10 | 2 | 54 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 25 |
| 13 | UNION-ALL | | 10 | | | | | 10 |00:00:00.01 | 25 |
| 14 | TABLE ACCESS BY INDEX ROWID| T3 | 10 | 1 | 12 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 25 |
|* 15 | INDEX RANGE SCAN | I_T3_ID1 | 10 | 1 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 15 |
| 16 | TABLE ACCESS BY INDEX ROWID| T4 | 10 | 1 | 39 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 17 | INDEX RANGE SCAN | I_T4_ID1 | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------------------------------
--征集一下，如果再加入一个条件：
select /*+ push_pred(@t3t4) */ * from v_tallz where id2=2 and sum_x1=29;
Plan hash value: 3790116785
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 123 (100)| | 1 |00:00:00.60 | 334 | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 1 | 13 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 | | | |
| 3 | NESTED LOOPS | | 1 | | | | | 1 |00:00:00.60 | 334 | | | |
| 4 | NESTED LOOPS | | 1 | 10 | 590 | 123 (5)| 00:00:01 | 1 |00:00:00.60 | 333 | | | |
|* 5 | HASH JOIN | | 1 | 10 | 470 | 103 (6)| 00:00:01 | 1 |00:00:00.60 | 330 | 1096K| 1096K| 741K (0)|
| 6 | JOIN FILTER CREATE | :BF0000 | 1 | 10 | 160 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 160 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | | | |
|* 8 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 10 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 2 | | | |
| 9 | VIEW | | 1 | 1001 | 31031 | 101 (6)| 00:00:01 | 1 |00:00:00.60 | 327 | | | |
|* 10 | FILTER | | 1 | | | | | 1 |00:00:00.60 | 327 | | | |
| 11 | HASH GROUP BY | | 1 | 1001 | 21021 | 101 (6)| 00:00:01 | 22 |00:00:00.60 | 327 | 838K| 838K| 3144K (0)|
| 12 | VIEW | V_T3T4 | 1 | 100K| 2050K| 96 (2)| 00:00:01 | 22 |00:00:00.59 | 327 | | | |
| 13 | JOIN FILTER USE | :BF0000 | 1 | | | | | 22 |00:00:00.59 | 327 | | | |
| 14 | UNION-ALL | | 1 | | | | | 100K|00:00:00.49 | 327 | | | |
| 15 | TABLE ACCESS FULL | T3 | 1 | 100K| 1171K| 94 (2)| 00:00:01 | 100K|00:00:00.10 | 327 | | | |
| 16 | TABLE ACCESS FULL | T4 | 1 | 1 | 39 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 17 | INDEX RANGE SCAN | PK_T2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 12 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
--如何避开t3,t4的全表扫描。
--如何改写避开t3,t4的全表扫描。不能改写视图定义。
--itpub上给出了答案：
--http://www.itpub.net/thread-1928636-1-1.html
select * from (select /*+ push_pred(@t3t4) */ a.*,rownum from v_tallz a where id2=2 )where sum_x1=29;
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1vphzru**bmc, child number 0
-------------------------------------
select * from (select /*+ push_pred(@t3t4) */ a.*,rownum from v_tallz a
where id2=2 )where sum_x1=29
Plan hash value: 1749510999
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 52 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
|* 3 | VIEW | | 99991 | 9569K| 52 (0)| 00:00:01 |
| 4 | COUNT | | | | | |
| 5 | NESTED LOOPS | | 99991 | 5272K| 52 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 10 | 280 | 22 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 10 | 160 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | I_T1_ID2 | 10 | | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 12 | 2 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PK_T2 | 1 | | 1 (0)| 00:00:01 |
| 11 | VIEW PUSHED PREDICATE | | 1 | 26 | 3 (0)| 00:00:01 |
|* 12 | FILTER | | | | | |
| 13 | SORT AGGREGATE | | 1 | 27 | | |
| 14 | VIEW | V_T3T4 | 2 | 54 | 3 (0)| 00:00:01 |
| 15 | UNION-ALL | | | | | |
| 16 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 12 | 2 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | I_T3_ID1 | 1 | | 1 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 39 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | I_T4_ID1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$4 / DEPT@SEL$4
2 - SEL$4 / DEPT@SEL$4
3 - SEL$335DD26A / from$_subquery$_001@SEL$1
4 - SEL$335DD26A
7 - SEL$335DD26A / T1@SEL$3
8 - SEL$335DD26A / T1@SEL$3
9 - SEL$335DD26A / T2@SEL$3
10 - SEL$335DD26A / T2@SEL$3
11 - SEL$789AFB00 / VX@SEL$3
12 - SEL$789AFB00
14 - SET$1 / V_T3T4@T3T4
15 - SET$1
16 - SEL$5 / T3@SEL$5
17 - SEL$5 / T3@SEL$5
18 - SEL$6 / T4@SEL$6
19 - SEL$6 / T4@SEL$6
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$789AFB00")
PUSH_PRED(@"SEL$335DD26A" "VX"@"SEL$3" 3)
OUTLINE_LEAF(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"T3T4")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
INDEX_RS_ASC(@"SEL$335DD26A" "T1"@"SEL$3" ("T1"."ID2"))
INDEX_RS_ASC(@"SEL$335DD26A" "T2"@"SEL$3" ("T2"."ID1"))
NO_ACCESS(@"SEL$335DD26A" "VX"@"SEL$3")
LEADING(@"SEL$335DD26A" "T1"@"SEL$3" "T2"@"SEL$3" "VX"@"SEL$3")
USE_NL(@"SEL$335DD26A" "T2"@"SEL$3")
USE_NL(@"SEL$335DD26A" "VX"@"SEL$3")
NO_ACCESS(@"SEL$789AFB00" "V_T3T4"@"T3T4")
INDEX_RS_ASC(@"SEL$6" "T4"@"SEL$6" ("T4"."ID1"))
INDEX_RS_ASC(@"SEL$5" "T3"@"SEL$5" ("T3"."ID1"))
INDEX_RS_ASC(@"SEL$4" "DEPT"@"SEL$4" ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=:B1)
3 - filter("SUM_X1"=29)
8 - access("T1"."ID2"=2)
10 - access("T1"."ID1"="T2"."ID1")
12 - filter(COUNT(*)>0)
17 - access("ID1"="T1"."ID1")
19 - access("ID1"="T1"."ID1")
--不得不承认rownum使用的很巧妙。