[20190111]执行计划bitmap and.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
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 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
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
| 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.顺便记录bitmap or的情况:
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
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
| 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
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 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.bitmap and,bitmap or,可以使用提示index_combine.
2.谓词出现or ,还可以使用or_expand , USE_CONCAT来控制执行计划.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2383016/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2383016/