本文共 20739 字，大约阅读时间需要 69 分钟。
[20150803]使用函数索引注意的问题.txt
--昨天在10g下优化时遇到一个奇怪的问题,做一个记录:
--首先说明一下,最近一段时间在做一个项目的优化,这个项目实际上ETL项目:ETL，是英文 Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至
目的端的过程。ETL一词较常用在数据仓库，但其对象并不限于数据仓库。ETL是构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗,最终按照预先定义好的数据仓库模型，将数据加载到数
据仓库中去。信息是现代企业的重要资源，是企业运用科学管理、决策分析的基础。目前，大多数企业花费大量的资金和时间来构建联机事务处理OLTP
的业务系统和办公自动化系统，用来记录事务处理的各种相关数据。据统计，数据量每2～3年时间就会成倍增长，这些数据蕴含着巨大的 商业价值，而企业所关注的通常只占在总数据量的2%～4%左右。因此，企业仍然没有最大化地利用已存在的数据资源，以致于浪费了更多 的时间和资金，也失去制定关键商业决策的最佳契机。于是，企业如何通过各种技术手段，并把数据转换为信息、知识，已经成了提高其 核心竞争力的主要瓶颈。而ETL则是主要的一个技术手段。--我个人对应这些不是非常熟悉,里面存在大量视图,标量子查询.我现在主要是优化抽取这部分的sql语句,但是我觉得开发在处理存在问
--题,应该在前端处理的信息,而不应该放在抽取以后再处理.好了,先不说这里,看看例子来说明问题:1.建立测试环境:
SCOTT@test01p> @ ver1PORT_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 selectedSCOTT@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 6262626262SYS_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 6262626262T2 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 6262626262T3 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 selectedSCOTT@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$1Predicate 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.690000SCOTT@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 6262626262T3 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不会选择这样的执行计划.
转载地址：http://fcxax.baihongyu.com/