使用HINT改变SQL执行计划

王文忠 2019-03-21
76
3 2
摘要:分别使用Hint改变执行计划到:1.以test_tab1为驱动表,两个表做HashJoin2.以test_tab1为驱动表,两个表做Nes...

问题描述

分别使用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。

「喜欢文章,快来给作者赞赏墨值吧」

评论

2
3
Oracle
订阅
欢迎订阅Oracle频道,订阅之后可以获取最新资讯和更新通知。
墨值排行
今日本周综合
近期活动
全部
相关课程
全部