ITPub博客

首页 > 数据库 > Oracle > [20160118]提示index_join.txt

[20160118]提示index_join.txt

原创 Oracle 作者:lfree 时间:2016-01-18 12:22:40 0 删除 编辑
[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上测试问题依旧。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1978263/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2488
  • 访问量
    6293245