| hai 的个人资料Oracle & Starcraft日志列表 |  工具  帮助 |
|
6月30日 first_rows_n和all_rowsfirst_rows_n和all_rows都是oracle optimizer_mode的选项,他们有什么区别呢,会对优化器产生怎么样的影响呢?让我们一起来解开迷题. all_rows模式: first_rows_n模式:
准备好测试表和索引后来看看测试脚本 all_rows模式: first_rows_1模式: first_rows_10模式: first_rows_100模式: 由于篇幅太长,所以把10053的trace文件简化了一下,只留下join这一部分的内容,并把merge join的部分去除了 all_rows: ************************** HA Join
在All Rows模式下最终优化器选择了Best join order: 1,Cost: 195.3030,
*********************** 经过重新计算后, *********************** 计算第二种join顺序的成本值,T1做驱动表,T做内部表, *********************** 在FIRST_Rows_1模式下最终优化器选择了Best join order: 1,Cost: 4.0039,
FIRST_Rows_10: Final - First K Rows Plan: Best join order: 1 FIRST_Rows_100: Final - First K Rows Plan: Best join order: 1 值得注意,FIRST_Rows_100选择了hash
ALL_ROWS: FIRST_Rows_1: FIRST_Rows_10: FIRST_Rows_100: 总结来说,first_rows_n基于成本计算,根据优先返回行数N重新计算各个对象的访问成本, 6月23日 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将更多得被我们看到. |
|
|
