[20150803]使用函数索引注意的问题.txt
--昨天在10g下优化时遇到一个奇怪的问题,做一个记录:
--首先说明一下,最近一段时间在做一个项目的优化,这个项目实际上ETL项目:
ETL，是英文 Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至
目的端的过程。ETL一词较常用在数据仓库，但其对象并不限于数据仓库。
ETL是构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗,最终按照预先定义好的数据仓库模型，将数据加载到数
据仓库中去。
信息是现代企业的重要资源，是企业运用科学管理、决策分析的基础。目前，大多数企业花费大量的资金和时间来构建联机事务处理OLTP
的业务系统和办公自动化系统，用来记录事务处理的各种相关数据。据统计，数据量每2～3年时间就会成倍增长，这些数据蕴含着巨大的
商业价值，而企业所关注的通常只占在总数据量的2%～4%左右。因此，企业仍然没有最大化地利用已存在的数据资源，以致于浪费了更多
的时间和资金，也失去制定关键商业决策的最佳契机。于是，企业如何通过各种技术手段，并把数据转换为信息、知识，已经成了提高其
核心竞争力的主要瓶颈。而ETL则是主要的一个技术手段。
--我个人对应这些不是非常熟悉,里面存在大量视图,标量子查询.我现在主要是优化抽取这部分的sql语句,但是我觉得开发在处理存在问
--题,应该在前端处理的信息,而不应该放在抽取以后再处理.好了,先不说这里,看看例子来说明问题:
1.建立测试环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
--------------------- -------------- ----------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
--家里只有12c for windows 版本,问题应该能重复再现的.
create table t1 (id number ,idx number, pad varchar2(20));
insert into t1 select rownum,trunc(rownum/10),lpad('a',20,'a') from xmltable('1 to 100000');
commit ;
create table t2 (idx number, padx varchar2(20));
insert into t2 select rownum,lpad('b',10,'b') from xmltable('1 to 10000');
commit ;
update t2 set padx=null where idx=42;
commit ;
create unique index pk_t1 on t1 (id);
alter table t1 add constraint pk_t1 primary key (id);
create unique index pk_t2 on t2 (idx);
alter table t2 add constraint pk_t2 primary key (idx);
create index i_t1_idx on t1(idx);
--IDX字段在表T2:T1的数量比例是1:10.
--分析忽略.实际上的表比以上要大好几倍.
2.开始执行;
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SCOTT@test01p> alter session set optimizer_adaptive_features=false;
Session altered.
--注意关闭主要是避免出现adaptive plan计划.
select * from t1 where t1.idx in ( select idx from t2 where padx is null);
--说明一下,实际上在生产系统执行的是delete语句,我这里换成了select.
--这也是我对开发处理信息非常不理解的原因,明明这些应该过滤掉的信息,为什么在插入后来处理.这明显的不好.
Plan hash value: 3237420647
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 11 (100)| | 10 |00:00:00.01 | 36 |
| 1 | NESTED LOOPS | | 1 | | | | | 10 |00:00:00.01 | 36 |
| 2 | NESTED LOOPS | | 1 | 10 | 450 | 11 (0)| 00:00:01 | 10 |00:00:00.01 | 34 |
|* 3 | TABLE ACCESS FULL | T2 | 1 | 1 | 15 | 9 (0)| 00:00:01 | 1 |00:00:00.01 | 31 |
|* 4 | INDEX RANGE SCAN | I_T1_IDX | 1 | 10 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 3 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 10 | 300 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
3 - SEL$5DA710D3 / T2@SEL$2
4 - SEL$5DA710D3 / T1@SEL$1
5 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("PADX" IS NULL)
4 - access("T1"."IDX"="IDX")
-- T2 全表扫描,实际上padx是null很少,可以通过改写仅仅包含null的索引来避开t2的全表扫描.(我没有办法先暂时优化这个语句)
-- 注意: T1表是可以使用索引I_T1_IDX的.
3.建立索引看看.
create index if_t2_padx on t2(decode(padx,null,'0'));
--sql改写如下:
select * from t1 where t1.idx in ( select idx from t2 where decode(padx,null,'0') ='0');
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID dz5cfzwv95hrk, child number 0
-------------------------------------
select * from t1 where t1.idx in ( select idx from t2 where
decode(padx,null,'0') ='0')
Plan hash value: 3096347206
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 139 (100)| | 10 |00:00:00.02 | 503 | | | |
|* 1 | HASH JOIN | | 1 | 1000 | 36000 | 139 (1)| 00:00:01 | 10 |00:00:00.02 | 503 | 2440K| 2440K| 693K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 100 | 600 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
|* 3 | INDEX RANGE SCAN | IF_T2_PADX | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 100K| 2929K| 137 (1)| 00:00:01 | 100K|00:00:00.01 | 501 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T2@SEL$2
3 - SEL$5DA710D3 / T2@SEL$2
4 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."IDX"="IDX")
3 - access("T2"."SYS_NC00003$"='0')
--注意看连接选择了hash join,T1不再使用索引,而是选择了全表扫描.实际上在我们的生产环境更加糟糕.
--走的是nested loop,而且是先全表扫描T1,再探查T2(通过索引IF_T2_PADX).
--生产系统看到如下:
SYSTEM@192.168.100.88:1521/tyt> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID axxunnb37urzm, child number 1
-------------------------------------
select * from presc_check_detail where PRESC_CHECK_ID in (select id from presc_check where decode(diagnosis,NULL,'0')='0' )
Plan hash value: 2672249901
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 263K| 35M| 37842 (1)| 00:07:35 | 0 |00:03:42.59 | 169K| 169K|
| 2 | TABLE ACCESS FULL | PRESC_CHECK_DETAIL | 1 | 12M| 1042M| 37738 (1)| 00:07:33 | 12M|00:00:25.63 | 169K| 169K|
|* 3 | TABLE ACCESS BY INDEX ROWID| PRESC_CHECK | 12M| 1 | 55 | 0 (0)| | 0 |00:02:27.75 | 2 | 0 |
|* 4 | INDEX RANGE SCAN | IF_PRESC_CHECK_DIAG_NULL | 12M| 1 | | 0 (0)| | 0 |00:01:03.21 | 2 | 0 |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / PRESC_CHECK_DETAIL@SEL$1
3 - SEL$5DA710D3 / PRESC_CHECK@SEL$2
4 - SEL$5DA710D3 / PRESC_CHECK@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("PRESC_CHECK_ID"="ID")
4 - access("PRESC_CHECK"."SYS_NC00071$"='0')
--要3:42分钟才出结果.注:不知道为什么没有SELECT STATEMENT 部分.而让我吃惊的是id=3,4 A-rows的估计都是正确的,为什么不先探
--查PRESC_CHECK,然后是PRESC_CHECK_DETAIL.注意看A-Rows=0(ID=4).
3.为了接近真实环境.我建立另外的表T3.
create table t3 (idx number, padx varchar2(20));
insert into t3 select rownum,lpad('b',10,'b') from xmltable('1 to 10000');
commit ;
create unique index pk_t3 on t3 (idx);
alter table t3 add constraint pk_t3 primary key (idx);
--分析表T3.
create index if_t3_padx on t3(decode(padx,null,'0'));
select * from t1 where t1.idx in ( select idx from t3 where decode(padx,null,'0') ='0');
Plan hash value: 2725908636
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 137 (100)| | 0 |00:00:00.16 | 501 |
| 1 | NESTED LOOPS | | 1 | 1000 | 36000 | 137 (1)| 00:00:01 | 0 |00:00:00.16 | 501 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 100K| 2929K| 137 (1)| 00:00:01 | 100K|00:00:00.02 | 500 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 | 100K| 1 | 6 | 0 (0)| | 0 |00:00:00.10 | 1 |
|* 4 | INDEX RANGE SCAN | IF_T3_PADX | 100K| 1 | | 0 (0)| | 0 |00:00:00.04 | 1 |
---------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T3@SEL$2
4 - SEL$5DA710D3 / T3@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."IDX"="IDX")
4 - access("T3"."SYS_NC00003$"='0')
31 rows selected.
--^_^,现在完美了再现生产系统遇到的问题. 看到这样的执行计划开始实在搞不懂为什么?
--当时赶着回家,修改如下:
select * from t1 where t1.idx in ( select idx from t3 where decode(padx,null,'0') ='0' and padx is null);
Plan hash value: 2730966104
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.01 | 1 |
| 1 | NESTED LOOPS | | 1 | | | | | 0 |00:00:00.01 | 1 |
| 2 | NESTED LOOPS | | 1 | 1 | 45 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 | 1 | 1 | 15 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
|* 4 | INDEX RANGE SCAN | IF_T3_PADX | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
|* 5 | INDEX RANGE SCAN | I_T1_IDX | 0 | 10 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 10 | 300 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / T3@SEL$2
4 - SEL$5DA710D3 / T3@SEL$2
5 - SEL$5DA710D3 / T1@SEL$1
6 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("PADX" IS NULL)
4 - access("T3"."SYS_NC00003$"='0')
5 - access("T1"."IDX"="IDX")
--为什么加入padx is null条件,oracle走的执行计划正确呢? 这样推测条件 decode(padx,null,'0') ='0' 估计的返回行数不对.
4.看看如下执行计划:
SCOTT@test01p> select idx from t3 where decode(padx,null,'0') ='0';
no rows selected
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID c7rx9t731yzus, child number 0
-------------------------------------
select idx from t3 where decode(padx,null,'0') ='0'
Plan hash value: 3619030488
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 0 |00:00:00.01 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 | 1 | 100 | 600 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
|* 2 | INDEX RANGE SCAN | IF_T3_PADX | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T3@SEL$1
2 - SEL$1 / T3@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T3"."SYS_NC00003$"='0')
-- 可以看出通过查询到表的行数是E-Rows=100.我T2 总记录10000行,按1%来估算.可以修改统计来验证这个问题:
SCOTT@test01p> EXECUTE SYS.DBMS_STATS.SET_TABLE_STATS (OWNNAME=>user, TABNAME=>'T3', NUMROWS=> 1e6);
PL/SQL procedure successfully completed.
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID c7rx9t731yzus, child number 0
-------------------------------------
select idx from t3 where decode(padx,null,'0') ='0'
Plan hash value: 3619030488
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 0 |00:00:00.01 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 | 1 | 10000 | 60000 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
|* 2 | INDEX RANGE SCAN | IF_T3_PADX | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T3@SEL$1
2 - SEL$1 / T3@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T3"."SYS_NC00003$"='0')
--修改行数量1e6,1% 计算就是1e4.
5.查看字段统计信息:
SCOTT@test01p> set NULL NULL
SCOTT@test01p> select column_name,num_distinct,low_value,high_value,density,num_nulls,num_buckets from DBA_TAB_COLS where owner=user and table_name='T3';
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS
-------------------- ------------ ---------- ---------- ---------- ---------- -----------
IDX 10000 C102 C302 .0001 0 1
PADX 1 6262626262 6262626262 1 0 1
6262626262 6262626262
SYS_NC00003$ NULL NULL NULL NULL NULL NULL
--才发现在建立函数索引时实际上是建立了一个隐含列SYS_NC00003$.而建立索引并不会自动建立该列的相关统计信息.
--如果仔细看可以发现我分析T3表后在建立函数索引的.
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't3',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't2',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
SCOTT@test01p> select table_name,column_name,num_distinct,low_value,high_value,density,num_nulls,num_buckets from DBA_TAB_COLS where owner=user and table_name in ('T3','T2');
TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS
---------- -------------------- ------------ ---------- ---------- ---------- ---------- -----------
T2 IDX 10000 C102 C302 .0001 0 1
T2 PADX 1 6262626262 6262626262 1 1 1
6262626262 6262626262
T2 SYS_NC00003$ 1 30 30 1 9999 1
T3 IDX 10000 C102 C302 .0001 0 1
T3 PADX 1 6262626262 6262626262 1 0 1
6262626262 6262626262
T3 SYS_NC00003$ 0 NULL NULL 0 10000 0
6 rows selected.
--这样再执行以上语句:
SCOTT@test01p> select * from t1 where t1.idx in ( select idx from t3 where decode(padx,null,'0') ='0');
no rows selected
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fzvjhhkhjjukd, child number 0
-------------------------------------
select * from t1 where t1.idx in ( select idx from t3 where
decode(padx,null,'0') ='0')
Plan hash value: 2730966104
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.01 | 1 |
| 1 | NESTED LOOPS | | 1 | | | | | 0 |00:00:00.01 | 1 |
| 2 | NESTED LOOPS | | 1 | 1 | 36 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 | 1 | 1 | 6 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
|* 4 | INDEX RANGE SCAN | IF_T3_PADX | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
|* 5 | INDEX RANGE SCAN | I_T1_IDX | 0 | 10 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 10 | 300 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / T3@SEL$2
4 - SEL$5DA710D3 / T3@SEL$2
5 - SEL$5DA710D3 / T1@SEL$1
6 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T3"."SYS_NC00003$"='0')
5 - access("T1"."IDX"="IDX")
--这次选择了正确的执行计划.
--总结:
--在建立函数索引后,应该马上分析一下表,这样相关隐含列才建立统计信息.不然按照等值查询时按照1%估算,这样对于大表1%也是一个很
大的数值.从而导致选择错误的执行计划.
--补充一些测试:
SCOTT@test01p> column PARTITION_NAME noprint
SCOTT@test01p> column SUBPARTITION_NAME noprint
SCOTT@test01p> select * from DBA_TAB_STATS_HISTORY where owner=user and table_name='T3';
OWNER TABLE_NAME STATS_UPDATE_TIME
------ ---------- ---------------------------------------------------------------------------
SCOTT T3 2015-07-31 22:25:32.202000
SCOTT T3 2015-07-31 23:15:58.690000
SCOTT@test01p> exec dbms_stats.restore_table_stats(user,'T3','2015-07-31 22:25:33');
PL/SQL procedure successfully completed.
--返回旧统计信息:
SCOTT@test01p> select table_name,column_name,num_distinct,low_value,high_value,density,num_nulls,num_buckets from DBA_TAB_COLS where owner=user and table_name in ('T3');
TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS
---------- -------------------- ------------ ---------- ---------- ---------- ---------- -----------
T3 IDX 10000 C102 C302 .0001 0 1
T3 PADX 1 6262626262 6262626262 1 0 1
6262626262 6262626262
T3 SYS_NC00003$ NULL NULL NULL NULL NULL NULL
--加入提示看看:
SCOTT@test01p> select /*+ leading(t3 t1) index(t1 i_t1_idx) */ * from t1 where t1.idx in ( select idx from t3 where decode(padx,null,'0') ='0');
no rows selected
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID d76wr4rsprr6b, child number 0
-------------------------------------
select /*+ leading(t3 t1) index(t1 i_t1_idx) */ * from t1 where t1.idx
in ( select idx from t3 where decode(padx,null,'0') ='0')
Plan hash value: 2730966104
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 201 (100)| | 0 |00:00:00.01 | 1 |
| 1 | NESTED LOOPS | | 1 | | | | | 0 |00:00:00.01 | 1 |
| 2 | NESTED LOOPS | | 1 | 1000 | 36000 | 201 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T3 | 1 | 100 | 600 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
|* 4 | INDEX RANGE SCAN | IF_T3_PADX | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
|* 5 | INDEX RANGE SCAN | I_T1_IDX | 0 | 10 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 10 | 300 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / T3@SEL$2
4 - SEL$5DA710D3 / T3@SEL$2
5 - SEL$5DA710D3 / T1@SEL$1
6 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T3"."SYS_NC00003$"='0')
5 - access("T1"."IDX"="IDX")
--对比发现id=3,E-rows=100,cost最后=201,比137高不少.在这样的统计信息下,oracle不会选择这样的执行计划.