ITPub博客
首页 博客 专栏 学院 社区 技术栈
发博

首页 > Linux操作系统 > Linux操作系统 > index_join index_combine and_equal比较 及示例分析

index_join index_combine and_equal比较 及示例分析

原创 Linux操作系统 作者：xpj0515 时间：2011-05-01 20:51:00 0 删除 编辑

SYS@standby1/2011-05-01 20:45:13>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.


SYS@standby1/2002-01-01 00:58:27>create table  test_tab as select rownum id,trunc(dbms_random.value(100,999)) t1,trunc(dbms_random.value(10000,99999)) t2,dbms_random.string('U',3) str1,dbms_random.string('U',5) str2 from dual connect by level <=1000000;

Table created.

Elapsed: 00:01:06.42


SYS@standby1/2002-01-01 01:02:35>col id for 9999999
SYS@standby1/2002-01-01 01:02:47>col t1 for 999999
SYS@standby1/2002-01-01 01:02:56>col t2 for 999999
SYS@standby1/2002-01-01 01:03:01>col str1 for a7
SYS@standby1/2002-01-01 01:03:09>col str2 for a7
SYS@standby1/2002-01-01 01:03:13>select * from test_tab where rownum<=10;

      ID      T1      T2 STR1    STR2
-------- ------- ------- ------- -------
       1     108   80943 JXB     AIRJK
       2     773   59153 KEM     MOMLT
       3     796   89751 HXU     QUULO
       4     703   17214 QFH     ZBQPZ
       5     603   60430 PJJ     LCMJP
       6     104   62811 RNN     ULDSM
       7     619   99953 UAP     BNMUC
       8     509   20605 AAD     DIKJD
       9     330   81171 KDC     EWBIA
      10     518   67222 QJZ     EPDVD

10 rows selected.


Elapsed: 00:00:00.01

index_join:


index join通过hash index join的方式实现了避免对表的访问.所有的数据都从索引
中直接获得.它不受查询条件影响,可以是唯一索引,也可以是多列索引.但返回的列中
必须全部包含在指定的索引列才有效！

Index_join

    Can be used to join any two indexes – no restrictions on number of columns, uniqueness, or predicates. Used to derive results without visiting the table at all. Each index is used to supply a (sub)set of its columns plus the relevant rowids. Oracle then performs a hash join on the two sets of data – using the rowid as the join column. Any data surviving the data is the answer set. Having joined two indexes by hash join, it is possible for Oracle to join a third, and so on until all the required columns have been join into the final result set

index_join可以连接任意两个索引（没有任何限制，如列数量，索引唯一性，以及谓词等），以实现完全不需要访问表就得到结果集的查询。每个索引都提供结果集的一个完整或部分列的集合以及对应的rowid。Oracle接着对这两个数据集进行散列连接-使用rowid作为连接条件。连接得到的结果就是所需的结果集。对Oracle来讲, 可以对两个索引做散列连接, 自然也可以对三个索引进行连接, 一直到所有出现在select list的列都已经取到，从而得到最后需要的结果集。


SYS@standby1/2011-05-01 20:02:44>@index_statistics.sql
Enter value for tablename: test_tab

TYPE     INDEX          TABLE            BLOCKS  NUM_ROWS    DIS_KEY   KEY_ROWS LEAFBLK CLU_FACTOR
-------- -------------- ------------ ---------- --------- ---------- ---------- ------- ----------
NORMAL   IND_TEST_STR2  TEST_TAB           4087   1000000     959277          1    2370     999730
NORMAL   IND_TEST_T2    TEST_TAB           4087   1000000      89998         11    2231     998605
NORMAL   IND_TEST_T1    TEST_TAB           4087   1000000        899       1112    2091     874010
NORMAL   PK_TEST_ID     TEST_TAB           4087   1000000    1000000          1    2088       4015

6 rows selected.

Elapsed: 00:00:00.10


SYS@standby1/2011-05-01 20:01:09>explain plan for select /*+ index_join(test_tab pk_test_id ind_test_t1)*/ id,t1 from test_tab where id between 100 and 2000 and t1 in(100,101);

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2011-05-01 20:02:40>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3138037976

-------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |     4 |    28 |  6640   (1)| 00:01:20 |
|*  1 |  VIEW                  | index$_join$_001 |     4 |    28 |  6640   (1)| 00:01:20 |
|*  2 |   HASH JOIN            |                  |       |       |            |          |
|*  3 |    INDEX FAST FULL SCAN| PK_TEST_ID       |     4 |    28 |  2628   (1)| 00:00:32 |
|*  4 |    INDEX FAST FULL SCAN| IND_TEST_T1      |     4 |    28 |  2631   (1)| 00:00:32 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"=100 OR "T1"=101)
   2 - access(ROWID=ROWID)
   3 - filter("ID"<=2000 AND "ID">=100)
   4 - filter("T1"=100 OR "T1"=101)

19 rows selected.

Elapsed: 00:00:00.02

                                                  当select－list 列没有全部出现在索引列中时

SYS@standby1/2011-05-01 20:03:23>explain plan for select /*+ index_join(test_tab pk_test_id ind_test_t1)*/ id,t1,t2 from test_tab where id between 100 and 2000 and t1 in(100,101);

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2011-05-01 20:04:03>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 952433714

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     4 |    44 |    14   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB   |     4 |    44 |    14   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_TEST_ID |  1902 |       |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"=100 OR "T1"=101)
   2 - access("ID">=100 AND "ID"<=2000)



SYS@standby1/2011-05-01 20:24:07>explain plan for select /*+ index_join(test_tab pk_test_id ind_test_t1 ind_test_t2)*/ id,t1,t2 from test_tab where id between 100 and 1000 and t1 in(100) and t2 between 10000 and 10001;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2011-05-01 20:24:09>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2274524539

--------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |     1 |    11 | 14081   (1)| 00:02:49 |
|   1 |  VIEW                   | index$_join$_001 |     1 |    11 | 14081   (1)| 00:02:49 |
|*  2 |   HASH JOIN             |                  |       |       |            |          |
|*  3 |    HASH JOIN            |                  |       |       |            |          |
|*  4 |     INDEX FAST FULL SCAN| PK_TEST_ID       |     1 |    11 |  2628   (1)| 00:00:32 |
|*  5 |     INDEX FAST FULL SCAN| IND_TEST_T1      |     1 |    11 |  2631   (1)| 00:00:32 |
|*  6 |    INDEX FAST FULL SCAN | IND_TEST_T2      |     1 |    11 |  2806   (1)| 00:00:34 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(ROWID=ROWID)
   3 - access(ROWID=ROWID)
   4 - filter("ID"<=1000 AND "ID">=100)
   5 - filter("T1"=100)
   6 - filter("T2"<=10001 AND "T2">=10000)

22 rows selected.



index_combine

index_combine最早是用在bitmap index上的,在9i开始oracle默认可以使用在btree索引上,在10G上可以得验证，这是由_b_tree_bitmap_plans参数来控制的.oracle将btree索引中获得的rowid信息通BITMAPCONVERSION FROM ROWIDS的步骤转换成bitmap进行匹配,然后匹配完成后通过BITMAP CONVERSION TO ROWIDS再转换出rowid获得数据或者回表获得数据.

Index combine

    Used for bitmap operators (although you can reference b-tree indexes in the hint to indicate to Oracle
    that the index is a candidate for ‘rowid conversion to bitmap’).
    Oracle acquires bitmaps from each index, and uses the AND, OR, NOT, or MERGE operators to produce
    a result bit string that can then be converted into rowids for visiting the table.

index_combine 主要用来处理bitmap操作(虽然, 也可以在提示中引用B-Tree索引来强制Oracle将这个B-Tree索引作为
“rowid conversion to bitmap”的一个候选项). Oracle会获取每个索引的bitmap,并使用AND/OR/NOT/MERGE
等位图操作来生成一个可转换成rowid的位串, 以使用这些rowid来访问数据表.


SYS@standby1/2011-05-01 20:14:48>explain plan for select /*+ index_combine(test_tab pk_test_id ind_test_t1)*/ id,t1 from test_tab where id between 100 and 1000 and t1 in(100,101);

Explained.

Elapsed: 00:00:00.02
SYS@standby1/2011-05-01 20:28:31>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 952433714

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     2 |    14 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB   |     2 |    14 |     8   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_TEST_ID |   902 |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"=100 OR "T1"=101)
   2 - access("ID">=100 AND "ID"<=1000)

15 rows selected.

总结：上述执行计划并没有使用索引合并，一般只有在合并的索引具有相似的选择性并且返回略等的行数时 比较有效，用于位图索引中效果比较理想
 
创建相关列的位图索引 查看执行计划

SYS@standby1/2011-05-01 20:37:19>create bitmap index bmp_test_t1 on test_tab(t1);

Index created.

Elapsed: 00:00:01.67
SYS@standby1/2011-05-01 20:37:58>create bitmap index bmp_test_str1 on test_tab(str1);

Index created.

Elapsed: 00:00:01.56
SYS@standby1/2011-05-01 20:38:15>@index_statistics.sql
Enter value for tablename: test_tab

TYPE     INDEX          TABLE            BLOCKS  NUM_ROWS    DIS_KEY   KEY_ROWS LEAFBLK CLU_FACTOR
-------- -------------- ------------ ---------- --------- ---------- ---------- ------- ----------
NORMAL   IND_TEST_STR2  TEST_TAB           4087   1000000     959277          1    2370     999730
BITMAP   BMP_TEST_STR1  TEST_TAB           4087     17576      17576          1     487      17576
NORMAL   IND_TEST_T2    TEST_TAB           4087   1000000      89998         11    2231     998605
BITMAP   BMP_TEST_T1    TEST_TAB           4087       899        899          1     450        899
NORMAL   PK_TEST_ID     TEST_TAB           4087   1000000    1000000          1    2088       4015

7 rows selected.

Elapsed: 00:00:00.10



SYS@standby1/2011-05-01 20:44:35>explain plan for select /*+ index_combine(test_tab bmp_test_str1,ind_test_str2)*/ '1' from test_tab where str1 like 'A%' and str2 like 'A%';

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2011-05-01 20:44:46>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1794964662

---------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |   881 |  7048 |       |   379   (1)| 00:00:05 |
|   1 |  BITMAP CONVERSION TO ROWIDS    |               |   881 |  7048 |       |   379   (1)| 00:00:05 |
|   2 |   BITMAP AND                    |               |       |       |       |            |          |
|   3 |    BITMAP MERGE                 |               |       |       |       |            |          |
|*  4 |     BITMAP INDEX RANGE SCAN     | BMP_TEST_STR1 |       |       |       |            |          |
|   5 |    BITMAP CONVERSION FROM ROWIDS|               |       |       |       |            |          |
|   6 |     SORT ORDER BY               |               |       |       |   480K|            |          |
|*  7 |      INDEX RANGE SCAN           | IND_TEST_STR2 |       |       |       |    73   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("STR1" LIKE 'A%')
       filter("STR1" LIKE 'A%' AND "STR1" LIKE 'A%')
   7 - access("STR2" LIKE 'A%')
       filter("STR2" LIKE 'A%' AND "STR2" LIKE 'A%')

22 rows selected.

Elapsed: 00:00:00.03


如果返回的列不只是指定索引的列，则访问方式大致相似，就多了一个步骤： TABLE ACCESS BY INDEX ROWID
根据rowid去访问表。


SYS@standby1/2011-05-01 20:38:26>explain plan for select /*+ index_combine(test_tab bmp_test_str1,ind_test_str2)*/ * from test_tab where str1 like 'A%' and str2 like 'A%';

Explained.

Elapsed: 00:00:00.05
SYS@standby1/2011-05-01 20:39:55>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2799005936

----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |   881 | 16739 |       |   379   (1)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST_TAB      |   881 | 16739 |       |   379   (1)| 00:00:05 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |               |       |       |       |            |          |
|   3 |    BITMAP AND                    |               |       |       |       |            |          |
|   4 |     BITMAP MERGE                 |               |       |       |       |            |          |
|*  5 |      BITMAP INDEX RANGE SCAN     | BMP_TEST_STR1 |       |       |       |            |          |
|   6 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |       |            |          |
|   7 |      SORT ORDER BY               |               |       |       |   480K|            |          |
|*  8 |       INDEX RANGE SCAN           | IND_TEST_STR2 |       |       |       |    73   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("STR1" LIKE 'A%')
       filter("STR1" LIKE 'A%' AND "STR1" LIKE 'A%')
   8 - access("STR2" LIKE 'A%')
       filter("STR2" LIKE 'A%' AND "STR2" LIKE 'A%')

23 rows selected.


and_equal

这种方式需要查询条件里面包括所有索引列,然后取得每个索引中得到的rowid列表,然后对这些列表做merge join,过滤出相同的rowid后再去表中获取数据或者直接从索引中获得数据.and_equal有一些限制,比如它只对单列索引有效,只对非唯一索引有效,使用到的索引不能超过5个,查询条件只能是”=”.
在10g中,and_equal已经被废弃了,只能通过hint才能生效！效率在这3个中是最差的！（就不分析了）

AND_EQUAL:

    Can operate on 2 to 5 non-unique, single-column indexes with an equality condition on the indexed values. For colX = const each index produces a list of rowids which are guaranteed to be in rowid order, hence pre-sorted and ready for a merge join. rowids that survive the join are used to access the table

and-equal支持操作2-5个非唯一单列索引，这些索引都有一个基于索引值的等值查询条件。 对于colX = “常量”, 每个索引都生成一个排好了顺序的rowid列表, 可以直接进行合并连接. 符合连接条件的rowid用来访问表取得结果数据..



 

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

0
0
上一篇： Oracle Sql提示学习笔记（转载）
下一篇： 嵌套循环连接的使用原则和分析（use_nl）
请登录后发表评论 登录
全部评论
xpj0515

注册时间：2010-11-08

  • 博文量
    32
  • 访问量
    55743

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号：11010802021510

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员