[20190111]执行计划bitmap and.txt

来源：这里教程网时间：2026-03-03 12:52:41 作者：

[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来控制执行计划.