| Perfil de haiOracle & StarcraftBlogListas |  Herramientas  Ayuda |
|
23 junio and_equal,index_join,index_combineand_equal,index_join,index_combine这三种都是oracle利用索引关联获得数据的方法,三者的目的都是为了最大限度的利用索引,减少回表的代价.但是三者的实现方法是有区别的,下面一一来分析.
and_equal:
这种方式需要查询条件里面包括所有索引列,然后取得每个索引中得到的rowid列表,然后对这些列表做merge join,过滤出相同的rowid后再去表中获取数据或者直接从索引中获得数据.and_equal有一些限制,比如它只对单列索引有效,只对非唯一索引有效,使用到的索引不能超过5个,查询条件只能是"=".在10g中,and_equal已经被废弃了,只能通过hint才能生效. create table test as select * from dba_objects;
create index ind_test_owner on test(owner);
create index ind_test_object_name on test(object_name);
SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name from test where owner='test' and object_name='test'; Execution Plan
---------------------------------------------------------- -------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| |* 1 | AND-EQUAL | | | | | |* 2 | INDEX RANGE SCAN| IND_TEST_OWNER | 1 | | 1 (0)| |* 3 | INDEX RANGE SCAN| IND_TEST_OBJECT_NAME | 2 | | 1 (0)| ------------------------------------------------------------------------------- 如果查询条件只包含owner
SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name from test where owner='test'; Execution Plan
---------------------------------------------------------- -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 29 | 2 (0)| |* 2 | INDEX RANGE SCAN | IND_TEST_OWNER | 1 | | 1 (0)| ----------------------------------------------------------------------------------- 修改owner,object_name为非空
alter table test modify(owner not null);
alter table test modify(object_name not null); SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name from test where owner='test'; Execution Plan
---------------------------------------------------------- -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 29 | 2 (0)| |* 2 | INDEX RANGE SCAN | IND_TEST_OWNER | 1 | | 1 (0)| ----------------------------------------------------------------------------------- 效果一样
查询条件是">"的情况
SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name,object_type from test where owner>'test' and object_name='test';
Execution Plan
---------------------------------------------------------- -----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)| |* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 40 | 2 (0)| |* 2 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | 2 | | 1 (0)| ----------------------------------------------------------------------------------------- 查询条件是in的情况
SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name,object_type from test where owner in('test','dba') and object_name='test'; Execution Plan
---------------------------------------------------------- -----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)| |* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 40 | 2 (0)| |* 2 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | 2 | | 1 (0)| ----------------------------------------------------------------------------------------- 再来看一下回表的情况 SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name,object_type from test where owner='test' and object_name='test';
Execution Plan
---------------------------------------------------------- -----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)| |* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 40 | 2 (0)| | 2 | AND-EQUAL | | | | | |* 3 | INDEX RANGE SCAN | IND_TEST_OWNER | 1 | | 1 (0)| |* 4 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | 2 | | 1 (0)| ----------------------------------------------------------------------------------------- 先通过and_equal取得rowid列表,然后从表中返回数据.
index_join:
index join顾名思义是对index进行关联,oracle通过hash index join的方式实现了避免对表的访问.所有的数据都从索引中直接获得.它不受查询条件影响,可以是唯一索引,也可以是多列索引. SQL 10G>select/*+ index_join(test ind_test_owner ind_test_object_name)*/ owner,object_name from test where owner='test' and object_name='test';
Execution Plan
---------------------------------------------------------- --------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 29 | 3 (34)| |* 1 | VIEW | index$_join$_001 | 1 | 29 | 3 (34)| |* 2 | HASH JOIN | | | | | |* 3 | INDEX RANGE SCAN| IND_TEST_OWNER | 1 | 29 | 1 (0)| |* 4 | INDEX RANGE SCAN| IND_TEST_OBJECT_NAME | 1 | 29 | 1 (0)| -------------------------------------------------------------------------------- 可以不带查询条件,只不过由index range scan变成了index fast full scan
SQL 10G>select/*+ index_join(test ind_test_owner ind_test_object_name)*/ owner,object_name from test
2 ; Execution Plan
---------------------------------------------------------- ------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 51984 | 1472K| 478 (2)| | 1 | VIEW | index$_join$_001 | 51984 | 1472K| 478 (2)| |* 2 | HASH JOIN | | | | | | 3 | INDEX FAST FULL SCAN| IND_TEST_OWNER | 51984 | 1472K| 153 (2)| | 4 | INDEX FAST FULL SCAN| IND_TEST_OBJECT_NAME | 51984 | 1472K| 322 (1)| ------------------------------------------------------------------------------------ 如果不是所有数据都能从索引获得,那么将不会使用index join SQL 10G>select/*+ index_join(test ind_test_owner ind_test_object_name)*/ owner,object_name,object_type from test where owner='test' and object_name='test';
Execution Plan
---------------------------------------------------------- -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)| |* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 40 | 2 (0)| |* 2 | INDEX RANGE SCAN | IND_TEST_OWNER | 1 | | 1 (0)| ----------------------------------------------------------------------------------- index_combine: index combine最早是用在bitmap index上的,在9i开始oracle默认可以使用在btree索引上,这是由_b_tree_bitmap_plans参数来控制的.oracle将btree索引中获得的rowid信息通过BITMAP CONVERSION FROM ROWIDS的步骤转换成bitmap进行匹配,然后匹配完成后通过BITMAP CONVERSION TO ROWIDS再转换出rowid获得数据或者回表获得数据. SQL 10G>select/*+ index_combine(test ind_test_owner ind_test_object_name)*/ owner,object_name from test where owner='test' and object_name='test';
Execution Plan
---------------------------------------------------------- ---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| | 1 | BITMAP CONVERSION TO ROWIDS | | 1 | 29 | 2 (0)| | 2 | BITMAP AND | | | | | | 3 | BITMAP CONVERSION FROM ROWIDS| | | | | |* 4 | INDEX RANGE SCAN | IND_TEST_OWNER | | | 1 (0)| | 5 | BITMAP CONVERSION FROM ROWIDS| | | | | |* 6 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | | | 1 (0)| --------------------------------------------------------------------------------------------- 回表取数据的情况
SQL 10G>select/*+ index_combine(test ind_test_owner ind_test_object_name)*/ owner,object_name,object_type from test where owner='test' and object_name='test';
Execution Plan
---------------------------------------------------------- ----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)| | 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 40 | 2 (0)| | 2 | BITMAP CONVERSION TO ROWIDS | | | | | | 3 | BITMAP AND | | | | | | 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |* 5 | INDEX RANGE SCAN | IND_TEST_OWNER | | | 1 (0)| | 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |* 7 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | | | 1 (0)| ---------------------------------------------------------------------------------------------- 不带查询条件的情况,index combine将不被使用
SQL 10G>select/*+ index_combine(test ind_test_owner ind_test_object_name)*/ owner,object_name from test
2 ; Execution Plan
---------------------------------------------------------- -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 51984 | 1472K| 1480 (1)| | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 51984 | 1472K| 1480 (1)| | 2 | INDEX FULL SCAN | IND_TEST_OWNER | 51984 | | 123 (2)| ----------------------------------------------------------------------------------- index_combine会是and_equal的很好的替代者,随着and_equal的退出,index_combine将更多得被我们看到. Comentarios (9)Para agregar un comentario, inicia sesión con tu cuenta de Windows Live ID (si utilizas Hotmail, Messenger o Xbox LIVE, ya tienes una cuenta de Windows Live ID). Iniciar sesión ¿No tienes una cuenta de Windows Live ID? Regístrate
Vínculos de referenciaLa dirección URL del vínculo de referencia de esta entrada es: http://wzwanghai.spaces.live.com/blog/cns!56626E237AFBD116!319.trak Weblogs que hacen referencia a esta entrada
|
|
|
