[20160118]提示index_join.txt
--生产系统优化，遇到1例使用index_join提示的异常情况，通过例子来说明：
1.环境：
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table tx as select rownum id , mod(rownum,100) idx , mod(rownum,100) idy,'a' c1,'b' c2 ,lpad('x',100,'x') pad from dual connect by level<=2e5;
Table created.
SCOTT@book> create index i_tx_idx_c1 on tx(idx,c1);
Index created.
SCOTT@book> create index i_tx_idx_c2 on tx(idx,c2);
Index created.
SCOTT@book> create index i_tx_idy_c2 on tx(idy,c2);
Index created.
--分析略.Method_Opt => 'FOR ALL COLUMNS SIZE 1'
2.测试:
SCOTT@book> alter session set statistics_level=all;
Session altered.
SCOTT@book> select count(1) from tx where idx=42 and (c1='a' or c2='b');
COUNT(1)
----------
1000
Plan hash value: 1635136096
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 456 (100)| | 1 |00:00:00.03 | 460 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.03 | 460 | | | |
|* 2 | VIEW | index$_join$_001 | 1 | 2000 | 14000 | 456 (1)| 00:00:06 | 2000 |00:00:00.03 | 460 | | | |
|* 3 | HASH JOIN | | 1 | | | | | 2000 |00:00:00.03 | 460 | 1557K| 1557K| 1602K (0)|
|* 4 | INDEX RANGE SCAN | I_TX_IDX_C1 | 1 | 2000 | 14000 | 6 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 | | | |
|* 5 | INDEX FAST FULL SCAN| I_TX_IDX_C2 | 1 | 2000 | 14000 | 561 (1)| 00:00:07 | 2000 |00:00:00.03 | 454 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2AEE34FF / TX@SEL$1
3 - SEL$2AEE34FF
4 - SEL$2AEE34FF / indexjoin$_alias$_001@SEL$2AEE34FF
5 - SEL$2AEE34FF / indexjoin$_alias$_002@SEL$2AEE34FF
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("C1"='a' OR "C2"='b'))
3 - access(ROWID=ROWID)
4 - access("IDX"=42)
5 - filter("IDX"=42)
--在没有看执行计划以前，我一直认为不会使用index_join,使用它的连接条件是rowid=rowid，而且不会回表查询。而我的查询是存在or
--条件，什么可能会使用index_join呢？
--仔细看上面的执行计划可以发现id=4 access("IDX"=42)，没有包括"C1"='a'的条件。才明白通过两个索引扫描IDX=42的条件，在同
--rowid=rowid连接，再过滤("C1"='a' OR "C2"='b')。
--但是如果再仔细看执行计划可以发现，id=5选择 INDEX FAST FULL SCAN,而使用filter("IDX"=42)，这样造成逻辑读达到454.实际上这
--里应该选择access("IDX"=42)，不知道为什么oracle选择错误的执行路径。
--我改写如下：select count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
--执行计划依旧。
3.我有改写如下：把后面的idx换成idy，我的测试例子这两个字段是相等的。
select count(1) from tx where (idx=42 and c1='a') or (idy=42 and c2='b');
Plan hash value: 2850621532
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 10 (100)| | 1 |00:00:00.01 | 12 |
| 1 | SORT AGGREGATE | | 1 | 1 | 10 | | | 1 |00:00:00.01 | 12 |
| 2 | BITMAP CONVERSION COUNT | | 1 | 3980 | 39800 | 10 (0)| 00:00:01 | 1 |00:00:00.01 | 12 |
| 3 | BITMAP OR | | 1 | | | | | 1 |00:00:00.01 | 12 |
| 4 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 6 |
|* 5 | INDEX RANGE SCAN | I_TX_IDX_C1 | 1 | | | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 6 |
|* 7 | INDEX RANGE SCAN | I_TX_IDY_C2 | 1 | | | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 |
------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TX@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
BITMAP_TREE(@"SEL$1" "TX"@"SEL$1" OR(1 1 ("TX"."IDX" "TX"."C1") 2 ("TX"."IDY" "TX"."C2")))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("IDX"=42 AND "C1"='a')
7 - access("IDY"=42 AND "C2"='b')
--这个就比较复合我原来自己的想象，很奇怪使用idx就不行，我根据上面的提示，修改查询语句如下：
--修改提示后面的部分，IDY替换成IDX，查询条件修改回来(idx=42 and c2='b')：
select
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
BITMAP_TREE(@"SEL$1" "TX"@"SEL$1" OR(1 1 ("TX"."IDX" "TX"."C1") 2 ("TX"."IDX" "TX"."C2")))
END_OUTLINE_DATA
*/ count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
--执行计划有回到了前面的使用index_join的情况：
Plan hash value: 1635136096
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 456 (100)| | 1 |00:00:00.03 | 460 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.03 | 460 | | | |
|* 2 | VIEW | index$_join$_001 | 1 | 2000 | 14000 | 456 (1)| 00:00:06 | 2000 |00:00:00.03 | 460 | | | |
|* 3 | HASH JOIN | | 1 | | | | | 2000 |00:00:00.03 | 460 | 1557K| 1557K| 1598K (0)|
|* 4 | INDEX RANGE SCAN | I_TX_IDX_C1 | 1 | 2000 | 14000 | 6 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 | | | |
|* 5 | INDEX FAST FULL SCAN| I_TX_IDX_C2 | 1 | 2000 | 14000 | 561 (1)| 00:00:07 | 2000 |00:00:00.03 | 454 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
4.使用use_concat提示看看：
select
/*+ use_concat index(tx i_tx_idx_c1) index(tx i_tx_idx_c2) */
count(1) from tx where idx=42 and （c1='a' or c2='b');
select
/*+ use_concat index(tx i_tx_idx_c1) index(tx i_tx_idx_c2) */
count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
Plan hash value: 802604350
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4011 (100)| | 1 |00:00:00.02 | 4012 |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.02 | 4012 |
| 2 | CONCATENATION | | 1 | | | | | 2000 |00:00:00.01 | 4012 |
| 3 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 2000 | 14000 | 2005 (0)| 00:00:25 | 2000 |00:00:00.01 | 2006 |
|* 4 | INDEX RANGE SCAN | I_TX_IDX_C2 | 1 | 2000 | | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 1 | 7 | 2005 (0)| 00:00:25 | 0 |00:00:00.01 | 2006 |
|* 6 | INDEX RANGE SCAN | I_TX_IDX_C1 | 1 | 2000 | | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1_1 / TX@SEL$1
4 - SEL$1_1 / TX@SEL$1
5 - SEL$1_2 / TX@SEL$1_2
6 - SEL$1_2 / TX@SEL$1_2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$1_1")
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(2))
OUTLINE_LEAF(@"SEL$1_2")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1_1" "TX"@"SEL$1" ("TX"."IDX" "TX"."C2"))
INDEX_RS_ASC(@"SEL$1_2" "TX"@"SEL$1_2" ("TX"."IDX" "TX"."C1"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("IDX"=42 AND "C2"='b')
5 - filter(LNNVL("C2"='b'))
6 - access("IDX"=42 AND "C1"='a')
--这样存在一个毛病，要回表，我的表的索引聚集因子很大，回表逻辑读很更高，为什么会回表，id=3为什么不好确定，实际上我认为不
--需要仅仅需要查询rowid就ok了。
--但是id = 5 存在一个filter(LNNVL("C2"='b'))条件必须回表才能知道结果。
5.使用提示INDEX_COMBINE,and_equal看看：
select
/*+ index_combine(t1 i_tx_idx_c1 i_tx_idx_c2) */
count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
select
/*+ AND_EQUAL(t1 i_tx_idx_c1 i_tx_idx_c2) */
count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
--执行计划都是使用index_join。
6.存在2个疑问：
1.使用index_join为什么选择INDEX FAST FULL SCAN，明明仅仅access就ok了，oracle确选择了过滤。
2.select count(1) from tx where (idx=42 and c1='a') or (idy=42 and c2='b'); 这样的查询逻辑读很小，为什么后面一个换成idx=42,
走BITMAP_TREE的提示走BITMAP OR就不行。
3.感觉oracle 的优化器即使发展到现在，越来越复杂，但是依旧问题多多。
4.改写如下：
select count(1) from (
select rowid from tx where idx=42 and c1='a'
union
select rowid from tx where idx=42 and c2='b');
Plan hash value: 924432443
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 12 (100)| | 1 |00:00:00.01 | 12 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | 1 |00:00:00.01 | 12 | | | |
| 2 | VIEW | | 1 | 4000 | | 12 (17)| 00:00:01 | 2000 |00:00:00.01 | 12 | | | |
| 3 | SORT UNIQUE | | 1 | 4000 | 68000 | 12 (17)| 00:00:01 | 2000 |00:00:00.01 | 12 | 115K| 115K| 102K (0)|
| 4 | UNION-ALL | | 1 | | | | | 4000 |00:00:00.01 | 12 | | | |
|* 5 | INDEX RANGE SCAN| I_TX_IDX_C1 | 1 | 2000 | 34000 | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 | | | |
|* 6 | INDEX RANGE SCAN| I_TX_IDX_C2 | 1 | 2000 | 34000 | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SET$1 / from$_subquery$_001@SEL$1
3 - SET$1
5 - SEL$2 / TX@SEL$2
6 - SEL$3 / TX@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("IDX"=42 AND "C1"='a')
6 - access("IDX"=42 AND "C2"='b')
--这样才能获得比较好的查询计划。
5.还可以做如下测试：
select count(rowid) from tx where idx=42 and (c1='a' or c2='b');
select rowid from tx where idx=42 and (c1='a' or c2='b');
--你可以发现oracle会使用全表扫描。但是如果后面的换成idy，写成如下：
select count(rowid) from tx where (idx=42 and c1='a') or (idy=42 and c2='b');
select count(1) from tx where (idx=42 and c1='a') or (idy=42 and c2='b');
Plan hash value: 2850621532
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 10 (100)| | 1 |00:00:00.01 | 12 |
| 1 | SORT AGGREGATE | | 1 | 1 | 10 | | | 1 |00:00:00.01 | 12 |
| 2 | BITMAP CONVERSION COUNT | | 1 | 3980 | 39800 | 10 (0)| 00:00:01 | 1 |00:00:00.01 | 12 |
| 3 | BITMAP OR | | 1 | | | | | 1 |00:00:00.01 | 12 |
| 4 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 6 |
|* 5 | INDEX RANGE SCAN | I_TX_IDX_C1 | 1 | | | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 6 |
|* 7 | INDEX RANGE SCAN | I_TX_IDY_C2 | 1 | | | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 |
------------------------------------------------------------------------------------------------------------------------------------------
6.是否是聚集因子太大的问题呢？
--exec DBMS_STATS.set_index_stats(ownname=>user,indname=>'I_TX_IDX_C1',CLSTFCT => 3394, force=>true);
exec DBMS_STATS.set_index_stats(ownname=>user,indname=>'I_TX_IDX_C2',CLSTFCT => 3394, force=>true);
SelecT
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2AEE34FF")
OUTLINE_LEAF(@"SEL$1")
INDEX_JOIN(@"SEL$1" "TX"@"SEL$1" ("TX"."IDX" "TX"."C1") ("TX"."IDX" "TX"."C2"))
END_OUTLINE_DATA
*/
count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
Plan hash value: 1635136096
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 456 (100)| | 1 |00:00:00.03 | 460 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.03 | 460 | | | |
|* 2 | VIEW | index$_join$_001 | 1 | 2000 | 14000 | 456 (1)| 00:00:06 | 2000 |00:00:00.03 | 460 | | | |
|* 3 | HASH JOIN | | 1 | | | | | 2000 |00:00:00.03 | 460 | 1557K| 1557K| 1600K (0)|
|* 4 | INDEX RANGE SCAN | I_TX_IDX_C1 | 1 | 2000 | 14000 | 6 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 | | | |
|* 5 | INDEX FAST FULL SCAN| I_TX_IDX_C2 | 1 | 2000 | 14000 | 561 (1)| 00:00:07 | 2000 |00:00:00.03 | 454 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
--问题依旧，感觉oracle优化器依旧问题多多。
7.补充如果要倒过来连接，因为2个索引统计基本相似，可以改名：
SCOTT@book> alter index i_tx_idx_c1 rename to i_tx_idx_z1;
Index altered.
SCOTT@book> select count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
COUNT(1)
----------
2000
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 167tfyfdcrxyn, child number 0
-------------------------------------
select count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b')
Plan hash value: 3304306490
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 456 (100)| | 1 |00:00:00.04 | 460 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.04 | 460 | | | |
|* 2 | VIEW | index$_join$_001 | 1 | 2000 | 14000 | 456 (1)| 00:00:06 | 2000 |00:00:00.04 | 460 | | | |
|* 3 | HASH JOIN | | 1 | | | | | 2000 |00:00:00.04 | 460 | 1557K| 1557K| 1616K (0)|
|* 4 | INDEX RANGE SCAN | I_TX_IDX_C2 | 1 | 2000 | 14000 | 6 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 | | | |
|* 5 | INDEX FAST FULL SCAN| I_TX_IDX_Z1 | 1 | 2000 | 14000 | 561 (1)| 00:00:07 | 2000 |00:00:00.03 | 454 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2AEE34FF / TX@SEL$1
3 - SEL$2AEE34FF
4 - SEL$2AEE34FF / indexjoin$_alias$_001@SEL$2AEE34FF
5 - SEL$2AEE34FF / indexjoin$_alias$_002@SEL$2AEE34FF
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2AEE34FF")
OUTLINE_LEAF(@"SEL$1")
INDEX_JOIN(@"SEL$1" "TX"@"SEL$1" ("TX"."IDX" "TX"."C2") ("TX"."IDX" "TX"."C1"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("C1"='a' OR "C2"='b'))
3 - access(ROWID=ROWID)
4 - access("IDX"=42)
5 - filter("IDX"=42)
8.10.2.0.4上测试问题依旧。
--生产系统优化，遇到1例使用index_join提示的异常情况，通过例子来说明：
1.环境：
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table tx as select rownum id , mod(rownum,100) idx , mod(rownum,100) idy,'a' c1,'b' c2 ,lpad('x',100,'x') pad from dual connect by level<=2e5;
Table created.
SCOTT@book> create index i_tx_idx_c1 on tx(idx,c1);
Index created.
SCOTT@book> create index i_tx_idx_c2 on tx(idx,c2);
Index created.
SCOTT@book> create index i_tx_idy_c2 on tx(idy,c2);
Index created.
--分析略.Method_Opt => 'FOR ALL COLUMNS SIZE 1'
2.测试:
SCOTT@book> alter session set statistics_level=all;
Session altered.
SCOTT@book> select count(1) from tx where idx=42 and (c1='a' or c2='b');
COUNT(1)
----------
1000
Plan hash value: 1635136096
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 456 (100)| | 1 |00:00:00.03 | 460 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.03 | 460 | | | |
|* 2 | VIEW | index$_join$_001 | 1 | 2000 | 14000 | 456 (1)| 00:00:06 | 2000 |00:00:00.03 | 460 | | | |
|* 3 | HASH JOIN | | 1 | | | | | 2000 |00:00:00.03 | 460 | 1557K| 1557K| 1602K (0)|
|* 4 | INDEX RANGE SCAN | I_TX_IDX_C1 | 1 | 2000 | 14000 | 6 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 | | | |
|* 5 | INDEX FAST FULL SCAN| I_TX_IDX_C2 | 1 | 2000 | 14000 | 561 (1)| 00:00:07 | 2000 |00:00:00.03 | 454 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2AEE34FF / TX@SEL$1
3 - SEL$2AEE34FF
4 - SEL$2AEE34FF / indexjoin$_alias$_001@SEL$2AEE34FF
5 - SEL$2AEE34FF / indexjoin$_alias$_002@SEL$2AEE34FF
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("C1"='a' OR "C2"='b'))
3 - access(ROWID=ROWID)
4 - access("IDX"=42)
5 - filter("IDX"=42)
--在没有看执行计划以前，我一直认为不会使用index_join,使用它的连接条件是rowid=rowid，而且不会回表查询。而我的查询是存在or
--条件，什么可能会使用index_join呢？
--仔细看上面的执行计划可以发现id=4 access("IDX"=42)，没有包括"C1"='a'的条件。才明白通过两个索引扫描IDX=42的条件，在同
--rowid=rowid连接，再过滤("C1"='a' OR "C2"='b')。
--但是如果再仔细看执行计划可以发现，id=5选择 INDEX FAST FULL SCAN,而使用filter("IDX"=42)，这样造成逻辑读达到454.实际上这
--里应该选择access("IDX"=42)，不知道为什么oracle选择错误的执行路径。
--我改写如下：select count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
--执行计划依旧。
3.我有改写如下：把后面的idx换成idy，我的测试例子这两个字段是相等的。
select count(1) from tx where (idx=42 and c1='a') or (idy=42 and c2='b');
Plan hash value: 2850621532
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 10 (100)| | 1 |00:00:00.01 | 12 |
| 1 | SORT AGGREGATE | | 1 | 1 | 10 | | | 1 |00:00:00.01 | 12 |
| 2 | BITMAP CONVERSION COUNT | | 1 | 3980 | 39800 | 10 (0)| 00:00:01 | 1 |00:00:00.01 | 12 |
| 3 | BITMAP OR | | 1 | | | | | 1 |00:00:00.01 | 12 |
| 4 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 6 |
|* 5 | INDEX RANGE SCAN | I_TX_IDX_C1 | 1 | | | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 6 |
|* 7 | INDEX RANGE SCAN | I_TX_IDY_C2 | 1 | | | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 |
------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TX@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
BITMAP_TREE(@"SEL$1" "TX"@"SEL$1" OR(1 1 ("TX"."IDX" "TX"."C1") 2 ("TX"."IDY" "TX"."C2")))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("IDX"=42 AND "C1"='a')
7 - access("IDY"=42 AND "C2"='b')
--这个就比较复合我原来自己的想象，很奇怪使用idx就不行，我根据上面的提示，修改查询语句如下：
--修改提示后面的部分，IDY替换成IDX，查询条件修改回来(idx=42 and c2='b')：
select
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
BITMAP_TREE(@"SEL$1" "TX"@"SEL$1" OR(1 1 ("TX"."IDX" "TX"."C1") 2 ("TX"."IDX" "TX"."C2")))
END_OUTLINE_DATA
*/ count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
--执行计划有回到了前面的使用index_join的情况：
Plan hash value: 1635136096
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 456 (100)| | 1 |00:00:00.03 | 460 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.03 | 460 | | | |
|* 2 | VIEW | index$_join$_001 | 1 | 2000 | 14000 | 456 (1)| 00:00:06 | 2000 |00:00:00.03 | 460 | | | |
|* 3 | HASH JOIN | | 1 | | | | | 2000 |00:00:00.03 | 460 | 1557K| 1557K| 1598K (0)|
|* 4 | INDEX RANGE SCAN | I_TX_IDX_C1 | 1 | 2000 | 14000 | 6 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 | | | |
|* 5 | INDEX FAST FULL SCAN| I_TX_IDX_C2 | 1 | 2000 | 14000 | 561 (1)| 00:00:07 | 2000 |00:00:00.03 | 454 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
4.使用use_concat提示看看：
select
/*+ use_concat index(tx i_tx_idx_c1) index(tx i_tx_idx_c2) */
count(1) from tx where idx=42 and （c1='a' or c2='b');
select
/*+ use_concat index(tx i_tx_idx_c1) index(tx i_tx_idx_c2) */
count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
Plan hash value: 802604350
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4011 (100)| | 1 |00:00:00.02 | 4012 |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.02 | 4012 |
| 2 | CONCATENATION | | 1 | | | | | 2000 |00:00:00.01 | 4012 |
| 3 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 2000 | 14000 | 2005 (0)| 00:00:25 | 2000 |00:00:00.01 | 2006 |
|* 4 | INDEX RANGE SCAN | I_TX_IDX_C2 | 1 | 2000 | | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 1 | 7 | 2005 (0)| 00:00:25 | 0 |00:00:00.01 | 2006 |
|* 6 | INDEX RANGE SCAN | I_TX_IDX_C1 | 1 | 2000 | | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1_1 / TX@SEL$1
4 - SEL$1_1 / TX@SEL$1
5 - SEL$1_2 / TX@SEL$1_2
6 - SEL$1_2 / TX@SEL$1_2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$1_1")
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(2))
OUTLINE_LEAF(@"SEL$1_2")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1_1" "TX"@"SEL$1" ("TX"."IDX" "TX"."C2"))
INDEX_RS_ASC(@"SEL$1_2" "TX"@"SEL$1_2" ("TX"."IDX" "TX"."C1"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("IDX"=42 AND "C2"='b')
5 - filter(LNNVL("C2"='b'))
6 - access("IDX"=42 AND "C1"='a')
--这样存在一个毛病，要回表，我的表的索引聚集因子很大，回表逻辑读很更高，为什么会回表，id=3为什么不好确定，实际上我认为不
--需要仅仅需要查询rowid就ok了。
--但是id = 5 存在一个filter(LNNVL("C2"='b'))条件必须回表才能知道结果。
5.使用提示INDEX_COMBINE,and_equal看看：
select
/*+ index_combine(t1 i_tx_idx_c1 i_tx_idx_c2) */
count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
select
/*+ AND_EQUAL(t1 i_tx_idx_c1 i_tx_idx_c2) */
count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
--执行计划都是使用index_join。
6.存在2个疑问：
1.使用index_join为什么选择INDEX FAST FULL SCAN，明明仅仅access就ok了，oracle确选择了过滤。
2.select count(1) from tx where (idx=42 and c1='a') or (idy=42 and c2='b'); 这样的查询逻辑读很小，为什么后面一个换成idx=42,
走BITMAP_TREE的提示走BITMAP OR就不行。
3.感觉oracle 的优化器即使发展到现在，越来越复杂，但是依旧问题多多。
4.改写如下：
select count(1) from (
select rowid from tx where idx=42 and c1='a'
union
select rowid from tx where idx=42 and c2='b');
Plan hash value: 924432443
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 12 (100)| | 1 |00:00:00.01 | 12 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | 1 |00:00:00.01 | 12 | | | |
| 2 | VIEW | | 1 | 4000 | | 12 (17)| 00:00:01 | 2000 |00:00:00.01 | 12 | | | |
| 3 | SORT UNIQUE | | 1 | 4000 | 68000 | 12 (17)| 00:00:01 | 2000 |00:00:00.01 | 12 | 115K| 115K| 102K (0)|
| 4 | UNION-ALL | | 1 | | | | | 4000 |00:00:00.01 | 12 | | | |
|* 5 | INDEX RANGE SCAN| I_TX_IDX_C1 | 1 | 2000 | 34000 | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 | | | |
|* 6 | INDEX RANGE SCAN| I_TX_IDX_C2 | 1 | 2000 | 34000 | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SET$1 / from$_subquery$_001@SEL$1
3 - SET$1
5 - SEL$2 / TX@SEL$2
6 - SEL$3 / TX@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("IDX"=42 AND "C1"='a')
6 - access("IDX"=42 AND "C2"='b')
--这样才能获得比较好的查询计划。
5.还可以做如下测试：
select count(rowid) from tx where idx=42 and (c1='a' or c2='b');
select rowid from tx where idx=42 and (c1='a' or c2='b');
--你可以发现oracle会使用全表扫描。但是如果后面的换成idy，写成如下：
select count(rowid) from tx where (idx=42 and c1='a') or (idy=42 and c2='b');
select count(1) from tx where (idx=42 and c1='a') or (idy=42 and c2='b');
Plan hash value: 2850621532
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 10 (100)| | 1 |00:00:00.01 | 12 |
| 1 | SORT AGGREGATE | | 1 | 1 | 10 | | | 1 |00:00:00.01 | 12 |
| 2 | BITMAP CONVERSION COUNT | | 1 | 3980 | 39800 | 10 (0)| 00:00:01 | 1 |00:00:00.01 | 12 |
| 3 | BITMAP OR | | 1 | | | | | 1 |00:00:00.01 | 12 |
| 4 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 6 |
|* 5 | INDEX RANGE SCAN | I_TX_IDX_C1 | 1 | | | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 6 |
|* 7 | INDEX RANGE SCAN | I_TX_IDY_C2 | 1 | | | 5 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 |
------------------------------------------------------------------------------------------------------------------------------------------
6.是否是聚集因子太大的问题呢？
--exec DBMS_STATS.set_index_stats(ownname=>user,indname=>'I_TX_IDX_C1',CLSTFCT => 3394, force=>true);
exec DBMS_STATS.set_index_stats(ownname=>user,indname=>'I_TX_IDX_C2',CLSTFCT => 3394, force=>true);
SelecT
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2AEE34FF")
OUTLINE_LEAF(@"SEL$1")
INDEX_JOIN(@"SEL$1" "TX"@"SEL$1" ("TX"."IDX" "TX"."C1") ("TX"."IDX" "TX"."C2"))
END_OUTLINE_DATA
*/
count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
Plan hash value: 1635136096
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 456 (100)| | 1 |00:00:00.03 | 460 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.03 | 460 | | | |
|* 2 | VIEW | index$_join$_001 | 1 | 2000 | 14000 | 456 (1)| 00:00:06 | 2000 |00:00:00.03 | 460 | | | |
|* 3 | HASH JOIN | | 1 | | | | | 2000 |00:00:00.03 | 460 | 1557K| 1557K| 1600K (0)|
|* 4 | INDEX RANGE SCAN | I_TX_IDX_C1 | 1 | 2000 | 14000 | 6 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 | | | |
|* 5 | INDEX FAST FULL SCAN| I_TX_IDX_C2 | 1 | 2000 | 14000 | 561 (1)| 00:00:07 | 2000 |00:00:00.03 | 454 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
--问题依旧，感觉oracle优化器依旧问题多多。
7.补充如果要倒过来连接，因为2个索引统计基本相似，可以改名：
SCOTT@book> alter index i_tx_idx_c1 rename to i_tx_idx_z1;
Index altered.
SCOTT@book> select count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
COUNT(1)
----------
2000
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 167tfyfdcrxyn, child number 0
-------------------------------------
select count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b')
Plan hash value: 3304306490
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 456 (100)| | 1 |00:00:00.04 | 460 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.04 | 460 | | | |
|* 2 | VIEW | index$_join$_001 | 1 | 2000 | 14000 | 456 (1)| 00:00:06 | 2000 |00:00:00.04 | 460 | | | |
|* 3 | HASH JOIN | | 1 | | | | | 2000 |00:00:00.04 | 460 | 1557K| 1557K| 1616K (0)|
|* 4 | INDEX RANGE SCAN | I_TX_IDX_C2 | 1 | 2000 | 14000 | 6 (0)| 00:00:01 | 2000 |00:00:00.01 | 6 | | | |
|* 5 | INDEX FAST FULL SCAN| I_TX_IDX_Z1 | 1 | 2000 | 14000 | 561 (1)| 00:00:07 | 2000 |00:00:00.03 | 454 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2AEE34FF / TX@SEL$1
3 - SEL$2AEE34FF
4 - SEL$2AEE34FF / indexjoin$_alias$_001@SEL$2AEE34FF
5 - SEL$2AEE34FF / indexjoin$_alias$_002@SEL$2AEE34FF
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2AEE34FF")
OUTLINE_LEAF(@"SEL$1")
INDEX_JOIN(@"SEL$1" "TX"@"SEL$1" ("TX"."IDX" "TX"."C2") ("TX"."IDX" "TX"."C1"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("C1"='a' OR "C2"='b'))
3 - access(ROWID=ROWID)
4 - access("IDX"=42)
5 - filter("IDX"=42)
8.10.2.0.4上测试问题依旧。
