分享程序员开发的那些事...
更新时间：2022-09-13 10:58:36
[20150710]11G谓词推入问题2.txt
--生产系统遇到一个sql语句的问题.
--生产系统的sql语句比较复杂,做一个简化的例子来说明问题.来说明自己优化遇到的困惑。
--昨天看来别人的回复，加提示 /*+ push_pred(v_tallx)*/，无效。实际上如果仔细看我的帖子
--http://blog.itpub.net/267265/viewspace-1724554/， 可以发现T2表的id是字符类型，存在隐式转换，虽然我定义了函数索引，视乎
--对于这种情况谓词推入存在问题。改成相同类型测试看看。
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 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 lpad(rownum,10,'0') id1, cast('t2test' as varchar2(20)) pad2 from xmltable('1 to 100000');
--rename t2 to t2y;
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 (to_number(ID1));
--alter index pk_t2rename to pk_t2y;
create index pk_t2 on t2 (ID1);
create index pk_t2 on t2 (to_number(ID1));
create index i_t3_id1 on t3 (ID1);
create index i_t4_id1 on t4 (ID1);
--分析表忽略.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
create view v_t3t4 as select * from t3 union all select * from t4;
create or replace view v_tall as
select t1.id1,t1.id2,t1.pad1,t2.pad2,sum_x1,sum_x2
from t1,t2,
(select 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;
2.因为我的测试环境遇到cardinality feedback问题：
--参考http://blog.itpub.net/267265/viewspace-1724262/
--我在session关闭cardinality feedback。
SCOTT@test> alter session set "_optimizer_use_feedback"=false ;
Session altered.
SCOTT@test> select * from v_tall where id2=42;
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 09qkq6x7ju4x2, child number 0
-------------------------------------
select * from v_tall where id2=42
Plan hash value: 431511531
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 53 (100)| | | | |
| 1 | HASH GROUP BY | | 10 | 590 | 53 (2)| 00:00:01 | 766K| 766K| 990K (0)|
| 2 | NESTED LOOPS | | 10 | 590 | 52 (0)| 00:00:01 | | | |
| 3 | NESTED LOOPS | | 10 | 280 | 22 (0)| 00:00:01 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | T1 | 10 | 160 | 2 (0)| 00:00:01 | | | |
|* 5 | INDEX RANGE SCAN | I_T1_ID2 | 10 | | 1 (0)| 00:00:01 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 12 | 2 (0)| 00:00:01 | | | |
|* 7 | INDEX RANGE SCAN | PK_T2 | 1 | | 1 (0)| 00:00:01 | | | |
| 8 | VIEW | V_T3T4 | 1 | 31 | 3 (0)| 00:00:01 | | | |
| 9 | UNION ALL PUSHED PREDICATE | | | | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 12 | 2 (0)| 00:00:01 | | | |
|* 11 | INDEX RANGE SCAN | I_T3_ID1 | 1 | | 1 (0)| 00:00:01 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 39 | 1 (0)| 00:00:01 | | | |
|* 13 | INDEX RANGE SCAN | I_T4_ID1 | 1 | | 1 (0)| 00:00:01 | | | | [0/29090]
------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$25E2CB83
4 - SEL$25E2CB83 / T1@SEL$2
5 - SEL$25E2CB83 / T1@SEL$2
6 - SEL$25E2CB83 / T2@SEL$2
7 - SEL$25E2CB83 / T2@SEL$2
8 - SET$5715CE2E / V_T3T4@SEL$3
9 - SET$5715CE2E
10 - SEL$8E13D68A / T3@SEL$4
11 - SEL$8E13D68A / T3@SEL$4
12 - SEL$9384AC1D / T4@SEL$5
13 - SEL$9384AC1D / T4@SEL$5
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$8E13D68A")
OUTLINE_LEAF(@"SEL$9384AC1D")
OUTLINE_LEAF(@"SET$5715CE2E")
PUSH_PRED(@"SEL$25E2CB83" "V_T3T4"@"SEL$3" 3)
OUTLINE_LEAF(@"SEL$25E2CB83")
MERGE(@"SEL$3")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$25E2CB83")
MERGE(@"SEL$3")
OUTLINE(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$25E2CB83" "T1"@"SEL$2" ("T1"."ID2"))
INDEX_RS_ASC(@"SEL$25E2CB83" "T2"@"SEL$2" ("T2"."ID1"))
NO_ACCESS(@"SEL$25E2CB83" "V_T3T4"@"SEL$3")
LEADING(@"SEL$25E2CB83" "T1"@"SEL$2" "T2"@"SEL$2" "V_T3T4"@"SEL$3")
USE_NL(@"SEL$25E2CB83" "T2"@"SEL$2")
USE_NL(@"SEL$25E2CB83" "V_T3T4"@"SEL$3")
USE_HASH_AGGREGATION(@"SEL$25E2CB83")
INDEX_RS_ASC(@"SEL$9384AC1D" "T4"@"SEL$5" ("T4"."ID1"))
INDEX_RS_ASC(@"SEL$8E13D68A" "T3"@"SEL$4" ("T3"."ID1"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID2"=42)
7 - access("T1"."ID1"="T2"."ID1")
11 - access("ID1"="T1"."ID1")
13 - access("ID1"="T1"."ID1")
----使用谓词推入。 PUSH_PRED(@"SEL$25E2CB83" "V_T3T4"@"SEL$3" 3)
3.但是如果定义成：
create or replace view v_tallx 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 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 * from v_tallx where id2=2;
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8j6fp0bc8d9xd, child number 0
-------------------------------------
select * from v_tallx where id2=2
Plan hash value: 3528211314
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 976 (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 | NESTED LOOPS | | | | | | | | | |
| 4 | NESTED LOOPS | | 10 | 590 | | 976 (1)| 00:00:01 | | | |
| 5 | MERGE JOIN | | 10 | 470 | | 956 (1)| 00:00:01 | | | |
| 6 | SORT JOIN | | 100K| 3027K| | 953 (1)| 00:00:01 | 3667K| 828K| 3259K (0)|
| 7 | VIEW | | 100K| 3027K| | 953 (1)| 00:00:01 | | | |
| 8 | HASH GROUP BY | | 100K| 3027K| 4336K| 953 (1)| 00:00:01 | 8785K| 2233K| 9800K (0)|
| 9 | VIEW | V_T3T4 | 100K| 3027K| | 96 (2)| 00:00:01 | | | |
| 10 | UNION-ALL | | | | | | | | | |
| 11 | TABLE ACCESS FULL | T3 | 100K| 1171K| | 94 (2)| 00:00:01 | | | |
| 12 | TABLE ACCESS FULL | T4 | 1 | 39 | | 2 (0)| 00:00:01 | | | |
|* 13 | SORT JOIN | | 10 | 160 | | 3 (34)| 00:00:01 | 2048 | 2048 | 2048 (0)|
| 14 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 160 | | 2 (0)| 00:00:01 | | | |
|* 15 | INDEX RANGE SCAN | I_T1_ID2 | 10 | | | 1 (0)| 00:00:01 | | | |
|* 16 | INDEX RANGE SCAN | PK_T2 | 1 | | | 1 (0)| 00:00:01 | | | | [0/29105]
| 17 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 12 | | 2 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$3 / DEPT@SEL$3
2 - SEL$3 / DEPT@SEL$3
3 - SEL$F5BB74E1
7 - SEL$4 / VX@SEL$2
8 - SEL$4
9 - SET$1 / V_T3T4@SEL$4
10 - SET$1
11 - SEL$5 / T3@SEL$5
12 - SEL$6 / T4@SEL$6
14 - SEL$F5BB74E1 / T1@SEL$2
15 - SEL$F5BB74E1 / T1@SEL$2
16 - SEL$F5BB74E1 / T2@SEL$2
17 - SEL$F5BB74E1 / T2@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
NO_ACCESS(@"SEL$F5BB74E1" "VX"@"SEL$2")
INDEX_RS_ASC(@"SEL$F5BB74E1" "T1"@"SEL$2" ("T1"."ID2"))
INDEX(@"SEL$F5BB74E1" "T2"@"SEL$2" ("T2"."ID1"))
LEADING(@"SEL$F5BB74E1" "VX"@"SEL$2" "T1"@"SEL$2" "T2"@"SEL$2")
USE_MERGE(@"SEL$F5BB74E1" "T1"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "T2"@"SEL$2")
NLJ_BATCHING(@"SEL$F5BB74E1" "T2"@"SEL$2")
NO_ACCESS(@"SEL$4" "V_T3T4"@"SEL$4")
USE_HASH_AGGREGATION(@"SEL$4")
FULL(@"SEL$6" "T4"@"SEL$6")
FULL(@"SEL$5" "T3"@"SEL$5")
INDEX_RS_ASC(@"SEL$3" "DEPT"@"SEL$3" ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=:B1)
13 - access("T1"."ID1"="VX"."ID1")
filter("T1"."ID1"="VX"."ID1")
15 - access("T1"."ID2"=2)
16 - access("T1"."ID1"="T2"."ID1")
--可以发现T3,T4是全表扫描。谓词推入失效！
--如果写成这样：
select (select dname from dept where deptno=a.id1) ,a.* from v_tall a where id2=2;
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bq98puv7ac22y, child number 0
-------------------------------------
select (select dname from dept where deptno=a.id1) ,a.* from v_tall a
where id2=2
Plan hash value: 3528211314
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 976 (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 | NESTED LOOPS | | | | | | | | | |
| 4 | NESTED LOOPS | | 10 | 590 | | 976 (1)| 00:00:01 | | | |
| 5 | MERGE JOIN | | 10 | 470 | | 956 (1)| 00:00:01 | | | |
| 6 | SORT JOIN | | 100K| 3027K| | 953 (1)| 00:00:01 | 3667K| 828K| 3259K (0)|
| 7 | VIEW | | 100K| 3027K| | 953 (1)| 00:00:01 | | | |
| 8 | HASH GROUP BY | | 100K| 3027K| 4336K| 953 (1)| 00:00:01 | 8785K| 2233K| 8756K (0)|
| 9 | VIEW | V_T3T4 | 100K| 3027K| | 96 (2)| 00:00:01 | | | |
| 10 | UNION-ALL | | | | | | | | | |
| 11 | TABLE ACCESS FULL | T3 | 100K| 1171K| | 94 (2)| 00:00:01 | | | |
| 12 | TABLE ACCESS FULL | T4 | 1 | 39 | | 2 (0)| 00:00:01 | | | |
|* 13 | SORT JOIN | | 10 | 160 | | 3 (34)| 00:00:01 | 2048 | 2048 | 2048 (0)|
| 14 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 160 | | 2 (0)| 00:00:01 | | | |
|* 15 | INDEX RANGE SCAN | I_T1_ID2 | 10 | | | 1 (0)| 00:00:01 | | | |
|* 16 | INDEX RANGE SCAN | PK_T2 | 1 | | | 1 (0)| 00:00:01 | | | |
| 17 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 12 | | 2 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / DEPT@SEL$2
2 - SEL$2 / DEPT@SEL$2
3 - SEL$8976F1A6
7 - SEL$4 / VX@SEL$3
8 - SEL$4
9 - SET$1 / V_T3T4@SEL$4
10 - SET$1
11 - SEL$5 / T3@SEL$5
12 - SEL$6 / T4@SEL$6
14 - SEL$8976F1A6 / T1@SEL$3
15 - SEL$8976F1A6 / T1@SEL$3
16 - SEL$8976F1A6 / T2@SEL$3
17 - SEL$8976F1A6 / T2@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$8976F1A6")
MERGE(@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$3")
NO_ACCESS(@"SEL$8976F1A6" "VX"@"SEL$3")
INDEX_RS_ASC(@"SEL$8976F1A6" "T1"@"SEL$3" ("T1"."ID2"))
INDEX(@"SEL$8976F1A6" "T2"@"SEL$3" ("T2"."ID1"))
LEADING(@"SEL$8976F1A6" "VX"@"SEL$3" "T1"@"SEL$3" "T2"@"SEL$3")
USE_MERGE(@"SEL$8976F1A6" "T1"@"SEL$3")
USE_NL(@"SEL$8976F1A6" "T2"@"SEL$3")
NLJ_BATCHING(@"SEL$8976F1A6" "T2"@"SEL$3")
NO_ACCESS(@"SEL$4" "V_T3T4"@"SEL$4")
USE_HASH_AGGREGATION(@"SEL$4")
FULL(@"SEL$6" "T4"@"SEL$6")
FULL(@"SEL$5" "T3"@"SEL$5")
INDEX_RS_ASC(@"SEL$2" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=:B1)
13 - access("T1"."ID1"="VX"."ID1")
filter("T1"."ID1"="VX"."ID1")
15 - access("T1"."ID2"=2)
16 - access("T1"."ID1"="T2"."ID1")
--问题依旧!
4.上次做到这里，我没有继续，现在继续测试:
--改成不使用标量子查询的模式呢？
select dept.dname,a.* from v_tall a,dept where dept.deptno(+)=id1 and id2=2;
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID au6y2mvs1jx2n, child number 0
-------------------------------------
select dept.dname,a.* from v_tall a,dept where dept.deptno(+)=id1 and id2=2
Plan hash value: 2339251001
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 54 (100)| | 10 |00:00:00.01 | 42 | | | |
| 1 | HASH GROUP BY | | 1 | 10 | 790 | 54 (2)| 00:00:01 | 10 |00:00:00.01 | 42 | 751K| 751K| 1101K (0)|
| 2 | NESTED LOOPS | | 1 | 10 | 790 | 53 (0)| 00:00:01 | 10 |00:00:00.01 | 42 | | | |
| 3 | NESTED LOOPS | | 1 | 10 | 480 | 23 (0)| 00:00:01 | 10 |00:00:00.01 | 17 | | | |
| 4 | NESTED LOOPS OUTER | | 1 | 10 | 360 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 8 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 160 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 10 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 2 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 1 | 20 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
|* 8 | INDEX UNIQUE SCAN | PK_DEPT | 10 | 1 | | 0 (0)| | 1 |00:00:00.01 | 4 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | T2 | 10 | 1 | 12 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 9 | | | |
|* 10 | INDEX RANGE SCAN | PK_T2 | 10 | 1 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 8 | | | |
| 11 | VIEW | V_T3T4 | 10 | 1 | 31 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 25 | | | |
| 12 | UNION ALL PUSHED PREDICATE | | 10 | | | | | 10 |00:00:00.01 | 25 | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| T3 | 10 | 1 | 12 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 25 | | | |
|* 14 | INDEX RANGE SCAN | I_T3_ID1 | 10 | 1 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 15 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID| T4 | 10 | 1 | 39 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 16 | INDEX RANGE SCAN | I_T4_ID1 | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$25E2CB83
5 - SEL$25E2CB83 / T1@SEL$2
6 - SEL$25E2CB83 / T1@SEL$2
7 - SEL$25E2CB83 / DEPT@SEL$1
8 - SEL$25E2CB83 / DEPT@SEL$1
9 - SEL$25E2CB83 / T2@SEL$2
10 - SEL$25E2CB83 / T2@SEL$2
11 - SET$5715CE2E / V_T3T4@SEL$3
12 - SET$5715CE2E
13 - SEL$8E13D68A / T3@SEL$4
14 - SEL$8E13D68A / T3@SEL$4
15 - SEL$9384AC1D / T4@SEL$5
16 - SEL$9384AC1D / T4@SEL$5
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$8E13D68A")
OUTLINE_LEAF(@"SEL$9384AC1D")
OUTLINE_LEAF(@"SET$5715CE2E")
PUSH_PRED(@"SEL$25E2CB83" "V_T3T4"@"SEL$3" 4)
OUTLINE_LEAF(@"SEL$25E2CB83")
MERGE(@"SEL$3")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$25E2CB83")
MERGE(@"SEL$3")
OUTLINE(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$25E2CB83" "T1"@"SEL$2" ("T1"."ID2"))
INDEX_RS_ASC(@"SEL$25E2CB83" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
INDEX_RS_ASC(@"SEL$25E2CB83" "T2"@"SEL$2" ("T2"."ID1"))
NO_ACCESS(@"SEL$25E2CB83" "V_T3T4"@"SEL$3")
LEADING(@"SEL$25E2CB83" "T1"@"SEL$2" "DEPT"@"SEL$1" "T2"@"SEL$2" "V_T3T4"@"SEL$3")
USE_NL(@"SEL$25E2CB83" "DEPT"@"SEL$1")
USE_NL(@"SEL$25E2CB83" "T2"@"SEL$2")
USE_NL(@"SEL$25E2CB83" "V_T3T4"@"SEL$3")
USE_HASH_AGGREGATION(@"SEL$25E2CB83")
INDEX_RS_ASC(@"SEL$9384AC1D" "T4"@"SEL$5" ("T4"."ID1"))
INDEX_RS_ASC(@"SEL$8E13D68A" "T3"@"SEL$4" ("T3"."ID1"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("T1"."ID2"=2)
8 - access("DEPT"."DEPTNO"="T1"."ID1")
10 - access("T1"."ID1"="T2"."ID1")
14 - access("ID1"="T1"."ID1")
16 - access("ID1"="T1"."ID1")
--可以发现不用标量子查询可以很好的谓词推入。
5.我们的视图已经定义如下：
create or replace view v_tallx 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 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;
--当然可以改视图定义，问题真正的生产系统视图定义很复杂，使用标量子查询的地方存在多处，不像我的测试环境仅仅1处。
--改动视图定义看看：(准确地讲写成dept.deptno(+)=t1.id1这样才等效)
create or replace view v_tally as
select t1.id1,t1.id2,t1.pad1,t2.pad2,sum_x1,sum_x2,dname,loc
from t1,t2,dept,
(select 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 and dept.deptno(+)=t1.id1
;
select * from v_tally where id2=2
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 68thbgk9kwhna, child number 0
-------------------------------------
select * from v_tally where id2=2
Plan hash value: 2339251001
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 54 (100)| | 10 |00:00:00.01 | 42 | | | |
| 1 | HASH GROUP BY | | 1 | 10 | 790 | 54 (2)| 00:00:01 | 10 |00:00:00.01 | 42 | 751K| 751K| 1107K (0)|
| 2 | NESTED LOOPS | | 1 | 10 | 790 | 53 (0)| 00:00:01 | 10 |00:00:00.01 | 42 | | | |
| 3 | NESTED LOOPS | | 1 | 10 | 480 | 23 (0)| 00:00:01 | 10 |00:00:00.01 | 17 | | | |
| 4 | NESTED LOOPS OUTER | | 1 | 10 | 360 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 8 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 160 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 10 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 2 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 1 | 20 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
|* 8 | INDEX UNIQUE SCAN | PK_DEPT | 10 | 1 | | 0 (0)| | 1 |00:00:00.01 | 4 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | T2 | 10 | 1 | 12 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 9 | | | |
|* 10 | INDEX RANGE SCAN | PK_T2 | 10 | 1 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 8 | | | |
| 11 | VIEW | V_T3T4 | 10 | 1 | 31 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 25 | | | |
| 12 | UNION ALL PUSHED PREDICATE | | 10 | | | | | 10 |00:00:00.01 | 25 | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| T3 | 10 | 1 | 12 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 25 | | | |
|* 14 | INDEX RANGE SCAN | I_T3_ID1 | 10 | 1 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 15 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID| T4 | 10 | 1 | 39 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 16 | INDEX RANGE SCAN | I_T4_ID1 | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$25E2CB83
5 - SEL$25E2CB83 / T1@SEL$2
6 - SEL$25E2CB83 / T1@SEL$2
7 - SEL$25E2CB83 / DEPT@SEL$2
8 - SEL$25E2CB83 / DEPT@SEL$2
9 - SEL$25E2CB83 / T2@SEL$2
10 - SEL$25E2CB83 / T2@SEL$2
11 - SET$5715CE2E / V_T3T4@SEL$3
12 - SET$5715CE2E
13 - SEL$8E13D68A / T3@SEL$4
14 - SEL$8E13D68A / T3@SEL$4
15 - SEL$9384AC1D / T4@SEL$5
16 - SEL$9384AC1D / T4@SEL$5
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$8E13D68A")
OUTLINE_LEAF(@"SEL$9384AC1D")
OUTLINE_LEAF(@"SET$5715CE2E")
PUSH_PRED(@"SEL$25E2CB83" "V_T3T4"@"SEL$3" 3)
OUTLINE_LEAF(@"SEL$25E2CB83")
MERGE(@"SEL$3")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$25E2CB83")
MERGE(@"SEL$3")
OUTLINE(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$25E2CB83" "T1"@"SEL$2" ("T1"."ID2"))
INDEX_RS_ASC(@"SEL$25E2CB83" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
INDEX_RS_ASC(@"SEL$25E2CB83" "T2"@"SEL$2" ("T2"."ID1"))
NO_ACCESS(@"SEL$25E2CB83" "V_T3T4"@"SEL$3")
LEADING(@"SEL$25E2CB83" "T1"@"SEL$2" "DEPT"@"SEL$2" "T2"@"SEL$2" "V_T3T4"@"SEL$3")
USE_NL(@"SEL$25E2CB83" "DEPT"@"SEL$2")
USE_NL(@"SEL$25E2CB83" "T2"@"SEL$2")
USE_NL(@"SEL$25E2CB83" "V_T3T4"@"SEL$3")
USE_HASH_AGGREGATION(@"SEL$25E2CB83")
INDEX_RS_ASC(@"SEL$9384AC1D" "T4"@"SEL$5" ("T4"."ID1"))
INDEX_RS_ASC(@"SEL$8E13D68A" "T3"@"SEL$4" ("T3"."ID1"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("T1"."ID2"=2)
8 - access("DEPT"."DEPTNO"="T1"."ID1")
10 - access("T1"."ID1"="T2"."ID1")
14 - access("ID1"="T1"."ID1")
16 - access("ID1"="T1"."ID1")
6.加入qb_name提示,主要为了推入，我发现生成的名字不知道是@"SEL$25E2CB83"，还是@"SEL$F5BB74E1"。
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 0
-------------------------------------
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 |
----------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$3 / DEPT@SEL$3
2 - SEL$3 / DEPT@SEL$3
3 - SEL$F5BB74E1
5 - SEL$F5BB74E1 / T1@SEL$2
6 - SEL$F5BB74E1 / T1@SEL$2
7 - SEL$F5BB74E1 / T2@SEL$2
8 - SEL$F5BB74E1 / T2@SEL$2
9 - SEL$789AFB00 / VX@SEL$2
10 - SEL$789AFB00
12 - SET$1 / V_T3T4@T3T4
13 - SET$1
14 - SEL$4 / T3@SEL$4
15 - SEL$4 / T3@SEL$4
16 - SEL$5 / T4@SEL$5
17 - SEL$5 / T4@SEL$5
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$789AFB00")
PUSH_PRED(@"SEL$F5BB74E1" "VX"@"SEL$2" 3)
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"T3T4")
OUTLINE(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$F5BB74E1" "T1"@"SEL$2" ("T1"."ID2"))
INDEX_RS_ASC(@"SEL$F5BB74E1" "T2"@"SEL$2" ("T2"."ID1"))
NO_ACCESS(@"SEL$F5BB74E1" "VX"@"SEL$2")
LEADING(@"SEL$F5BB74E1" "T1"@"SEL$2" "T2"@"SEL$2" "VX"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "T2"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "VX"@"SEL$2")
NO_ACCESS(@"SEL$789AFB00" "V_T3T4"@"T3T4")
INDEX_RS_ASC(@"SEL$5" "T4"@"SEL$5" ("T4"."ID1"))
INDEX_RS_ASC(@"SEL$4" "T3"@"SEL$4" ("T3"."ID1"))
INDEX_RS_ASC(@"SEL$3" "DEPT"@"SEL$3" ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=:B1)
6 - access("T1"."ID2"=2)
8 - access("T1"."ID1"="T2"."ID1")
10 - filter(COUNT(*)>0)
15 - access("ID1"="T1"."ID1")
17 - access("ID1"="T1"."ID1")
--看PUSH_PRED(@"SEL$F5BB74E1" "VX"@"SEL$2" 3)，怪不得自己乱加的提示无效。原来我那里定义了别名vx。哎，看来许多东西自己
--没有搞清楚，后面的3，我觉得应该是id=3.我后面的测试不写也可以通过。
--而且这次第2个参数是"VX"@"SEL$2"，我自己测试过
PUSH_PRED(@"SEL$F5BB74E1" "V_T3T4"@"SEL$3" 3)
PUSH_PRED(@"SEL$25E2CB83" "V_T3T4"@"SEL$3" 3)
PUSH_PRED(@"SEL$F5BB74E1" "V_T3T4"@"SEL$4" 3)
PUSH_PRED(@"SEL$25E2CB83" "V_T3T4"@"SEL$4" 3)
PUSH_PRED(@"SEL$F5BB74E1")
PUSH_PRED(@"SEL$25E2CB83")
--后面的数字3，我改过2,4,5,6.主要还是不理解这个的含义，在哪里乱猜。^_^。
--另外注意id=10的条件10 - filter(COUNT(*)>0)。
7.知道这个提示加入推入就变得很简单了：
select /*+ PUSH_PRED(@"SEL$F5BB74E1" "VX"@"SEL$2" 3) */ * from v_tallx where id2=2;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0x80qs240wqqy, child number 0
-------------------------------------
select /*+ PUSH_PRED(@"SEL$F5BB74E1" "VX"@"SEL$2" 3) */ * from v_tallx
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 |
----------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$3 / DEPT@SEL$3
2 - SEL$3 / DEPT@SEL$3
3 - SEL$F5BB74E1
5 - SEL$F5BB74E1 / T1@SEL$2
6 - SEL$F5BB74E1 / T1@SEL$2
7 - SEL$F5BB74E1 / T2@SEL$2
8 - SEL$F5BB74E1 / T2@SEL$2
9 - SEL$8E13D68A / VX@SEL$2
10 - SEL$8E13D68A
12 - SET$1 / V_T3T4@SEL$4
13 - SET$1
14 - SEL$5 / T3@SEL$5
15 - SEL$5 / T3@SEL$5
16 - SEL$6 / T4@SEL$6
17 - 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')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$8E13D68A")
PUSH_PRED(@"SEL$F5BB74E1" "VX"@"SEL$2" 3)
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$F5BB74E1" "T1"@"SEL$2" ("T1"."ID2"))
INDEX_RS_ASC(@"SEL$F5BB74E1" "T2"@"SEL$2" ("T2"."ID1"))
NO_ACCESS(@"SEL$F5BB74E1" "VX"@"SEL$2")
LEADING(@"SEL$F5BB74E1" "T1"@"SEL$2" "T2"@"SEL$2" "VX"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "T2"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "VX"@"SEL$2")
NO_ACCESS(@"SEL$8E13D68A" "V_T3T4"@"SEL$4")
INDEX_RS_ASC(@"SEL$6" "T4"@"SEL$6" ("T4"."ID1"))
INDEX_RS_ASC(@"SEL$5" "T3"@"SEL$5" ("T3"."ID1"))
INDEX_RS_ASC(@"SEL$3" "DEPT"@"SEL$3" ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=:B1)
6 - access("T1"."ID2"=2)
8 - access("T1"."ID1"="T2"."ID1")
10 - filter(COUNT(*)>0)
15 - access("ID1"="T1"."ID1")
17 - access("ID1"="T1"."ID1")
--总结：
--总觉得标量子查询不能乱用，少用为妙。