[20160118]提示index_join.txt
[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上測試問題依舊。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1978263/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Flutter Toast、彈出提示、輕提示FlutterAST
- pycharm 每日提示PyCharm
- 【sql hint 提示】SQL
- 開源提示
- myeclipse中提示Hot Code Replace Failed提示窗錯誤EclipseAI
- tooltip提示框
- CSS 專家提示CSS
- MySQL SQL hint 提示MySql
- 【SQL 提示 之三】SQL
- sqlplus提示SQL
- 優化器提示優化
- Oracle使用提示(Hints)Oracle
- Jbuilder 每日提示 (轉)UI
- Element 利用Tooltip提示框實現動態顯示文字提示
- Metasploit新增技巧提示功能
- Promise 的 9 個提示Promise
- pl devloper的智慧提示dev
- 自定義音量提示 viewView
- SVProgressHUD提示框IOSiOS
- 命令提示符
- 伺服器的提示！伺服器
- 釋出防扒提示，
- AndroidToast提示封裝AndroidAST封裝
- hints提示總結 zt
- ALaunch 0.8 := 懸浮提示
- openai官網提示詞OpenAI
- 含動畫的java氣泡提示效果，右下角訊息提示窗動畫Java
- 登入提示框與密碼提示框，生成驗證碼密碼
- win10系統下提示Outlook提示“收件人太多”如何解決Win10
- Win10系統提示Windows安全登入提示框如何解決Win10Windows
- Dockerfile實踐小提示Docker
- Nessus提示API Disabled錯誤API
- NHibernate提示表is not mappedAPP
- 微信小程式提示框微信小程式
- SourceTree 提示 fatal: Authentication failed for..AI
- elementUI loading提示使用UI
- clickhouse智慧提示編輯器
- bootstrap提示和彈出框boot