[20190111]執行計劃走位與.txt

--//要修改生產系統一條執行計劃走bitmap and,遇到一些問題,做一個記錄:

1.環境:

SCOTT@book> @ 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 t as select rownum id1,rownum+1 id2, 'test' name from dual connect by level<=2e5;

Table created.

SCOTT@book> create index i_t_id1 on t(id1);

Index created.

SCOTT@book> create index i_t_id2 on t(id2);

Index created.

--//分析略.

2.測試:

SCOTT@book> alter session set statistics_level=all ;

Session altered.

SCOTT@book> select /*+ index(t i_t_id1) index(t i_t_id2) */* from t where id1=42 and id2=43;

ID1 ID2 NAME

---------- ---------- --------------------

42 43 test

Plan hash value: 1183254286

---------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 4 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 15 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |

|* 2 | INDEX RANGE SCAN | I_T_ID1 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |

---------------------------------------------------------------------------------------------------------------------------------

--//執行計劃並沒有選擇2個索引,google發現要使用提示INDEX_COMBINE.

select /*+ INDEX_COMBINE(t i_t_id1 i_t_id2) */* from t where id1=42 and id2=43;

SCOTT@book> select /*+ INDEX_COMBINE(t i_t_id1 i_t_id2) */* from t where id1=42 and id2=43;

ID1 ID2 NAME

---------- ---------- ----------------------------------------

42 43 test

Plan hash value: 619742204

--------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 5 |

| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1 | 15 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 5 |

| 2 | BITMAP CONVERSION TO ROWIDS | | 1 | | | | | 1 |00:00:00.01 | 4 |

| 3 | BITMAP AND | | 1 | | | | | 1 |00:00:00.01 | 4 |

| 4 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 2 |

|* 5 | INDEX RANGE SCAN | I_T_ID1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

| 6 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 2 |

|* 7 | INDEX RANGE SCAN | I_T_ID2 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 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")

BITMAP_TREE(@"SEL$1" "T"@"SEL$1" AND(("T"."ID1") ("T"."ID2")))

END_OUTLINE_DATA

*/

3.順便記錄點陣圖或的情況:

SCOTT@book> select /*+ INDEX_COMBINE(t i_t_id1 i_t_id2) */* from t where id1=42 or id2=43;

ID1 ID2 NAME

---------- ---------- ----------------------------------------

42 43 test

Plan hash value: 3639385359

--------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 5 |

| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 2 | 30 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 5 |

| 2 | BITMAP CONVERSION TO ROWIDS | | 1 | | | | | 1 |00:00:00.01 | 4 |

| 3 | BITMAP OR | | 1 | | | | | 1 |00:00:00.01 | 4 |

| 4 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 2 |

|* 5 | INDEX RANGE SCAN | I_T_ID1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

| 6 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 2 |

|* 7 | INDEX RANGE SCAN | I_T_ID2 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 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")

BITMAP_TREE(@"SEL$1" "T"@"SEL$1" OR(1 1 ("T"."ID1") 2 ("T"."ID2")))

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

END_OUTLINE_DATA

*/

--//注意裡面的or ,前面是and.

4.記錄使用or_expand and USE_CONCAT提示的情況.

--//11g 我無法使用or_expand. 12c可以.

SCOTT@book> select /*+ USE_CONCAT */* from t where id1=42 or id2=43;

ID1 ID2 NAME

---------- ---------- ----------------------------------------

42 43 test

SCOTT@book> @ dpc '' outline

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID 0mb95w3qw5wjw, child number 0

-------------------------------------

select /*+ USE_CONCAT */* from t where id1=42 or id2=43

Plan hash value: 951918345

----------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 1 |00:00:00.01 | 7 |

| 1 | CONCATENATION | | 1 | | | | | 1 |00:00:00.01 | 7 |

| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 15 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |

|* 3 | INDEX RANGE SCAN | I_T_ID2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |

|* 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 15 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |

|* 5 | INDEX RANGE SCAN | I_T_ID1 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 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(1))

OUTLINE_LEAF(@"SEL$1_2")

OUTLINE(@"SEL$1")

INDEX_RS_ASC(@"SEL$1_1" "T"@"SEL$1" ("T"."ID2"))

INDEX_RS_ASC(@"SEL$1_2" "T"@"SEL$1_2" ("T"."ID1"))

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("ID2"=43)

4 - filter(LNNVL("ID2"=43))

5 - access("ID1"=42)

總結:

1.位與,位或使用提示index_combine.

2.謂詞出現or ,還可以使用or_expand , USE_CONCAT來控制執行計劃.

[20190111]執行計劃走位與.txt

相關文章