使用HINT改变SQL执行计划

分别使用Hint 改变执行计划到：

1.  以test_tab1 为驱动表，两个表做Hash Join

2.  以test_tab1 为驱动表，两个表做Nested loop, 其中test_tab2表走inx2_test_tab2 索引

3.  以test_tab1 为驱动表，且test_tab1 同时使用两个index

4.  以test_tab2为驱动表，两个表做merge join

创建测试表和索引：

drop table test_tab1 ;
create table test_tab1 as select  /*+ordered*/ * from all_objects,(select * from dual connect by level < 5);
create index inx1_test_tab1 on test_tab1(object_name);
create index inx2_test_tab1 on test_tab1(object_type);
create index inx3_test_tab1 on test_tab1(object_id);
 
drop table test_tab2;
create table test_tab2 as select * from test_tab1 where rownum < 1e4;
create index inx1_test_tab2 on test_tab2 (owner);
create index inx2_test_tab2 on test_tab2 (object_id);
create index inx3_test_tab2 on test_tab2 (object_id,object_name);
 
analyze table test_tab1 compute statistics for table for all indexes for all columns;
analyze table test_tab2 compute statistics for table for all indexes for all columns;

需要改变的SQL如下：

select count(*) from test_tab1 a , test_tab2 b
where a.object_id = b.object_id and a.object_name = 'TAB$' and a.object_type = 'TABLE'
and b.owner = 'SYS';

1、以test_tab1 为驱动表，两个表做Hash Join

select /*+  use_hash(a b)*/count(*) from test_tab1 a , test_tab2 b
where a.object_id = b.object_id and a.object_name = 'TAB$' and a.object_type = 'TABLE'
and b.owner = 'SYS';

       从执行计划中可以看出，第6步做的hash join是TEST_TAB2的两个索引hash join后形成一个视图，inx1_test_tab2是OWNER列，inx2_test_tab2是object_id列，TEST_TAB2表数据过滤后，再与表TEST_TAB1通过object_id做hash join。

2、以test_tab1 为驱动表，两个表做Nested loop, 其中test_tab2表走inx2_test_tab2 索引

select /*+ leading(a b) use_nl(a b)*/count(*) from test_tab1 a , test_tab2 b
where a.object_id = b.object_id and a.object_name = 'TAB$' and a.object_type = 'TABLE'
and b.owner = 'SYS';

3、以test_tab1 为驱动表，且test_tab1 同时使用两个index

select /*+ leading(a b) index_join(a inx2_test_tab1 inx1_test_tab1 inx3_test_tab1 ) no_index(b INX2_TEST_TAB2 INX3_TEST_TAB2)*/ count(*) from test_tab1 a , test_tab2 b
where a.object_id = b.object_id and a.object_name = 'TAB$' and a.object_type = 'TABLE'
and b.owner = 'SYS';

 官方文档对index_join的解释是使用这个hint，就是做索引连接。为了使hint具有好的效果，必须存在足够少的索引，这些索引包含解析查询所需的所有列。

ORDERED hint指示Oracle按照表在FROM子句中出现的顺序联接表。Oracle建议您使用leading hint，它比ordered提示更通用。

当从需要联接的SQL语句中省略ORDERED提示时，优化器将选择联接表的顺序。如果您知道优化器不知道从每个表中选择的行数，那么您可能希望使用ORDERED提示来指定联接顺序。这样的信息可以让您比优化器更好地选择一个内部和外部表，也就是驱动表与被驱动表。

4、以test_tab2为驱动表，两个表做merge join：

select /*+ leading(b a) no_use_hash(b,a)*/count(*) from test_tab1 a , test_tab2 b
where a.object_id = b.object_id and a.object_name = 'TAB$' and a.object_type = 'TABLE'
and b.owner = 'SYS';

从官方文档可以了解到，hint指定优化器，指定的表做内连接不用hash join。