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/,如需转载,请注明出处,否则将追究法律责任。