0

多表关联SQL如何优化

王文忠 2019-03-21
102
摘要:1、用dba权限的用户登陆测试系统,创建如下两张表:droptabletest_tab1;createtabletest_tab1asse...

问题描述

1、 用dba权限的用户登陆测试系统,创建如下两张表:

drop table test_tab1 ;
 create table test_tab1 as select  /*+ordered*/ * from all_objects,(select * from dual connect by level < 5)   ;
create index inx_test_tab1 on test_tab1(object_name);
 
drop table test_tab2;
create table test_tab2 as select * from test_tab1 where rownum < 1e4;
 
 
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 b.object_name from test_tab1 a ,test_tab2 b
 where a.object_name = 'TAB'
  and a.data_object_id = b.data_object_id;

专家解答

查询表的统计信息及直方图:

image.png

表TEST_TAB1:

image.png


表TEST_TAB2:

image.png

从截图中可以看到TEST_TAB1是340472行数据,表TEST_TAB2是9999行数据。

 

select b.object_name from test_tab1 a ,test_tab2 b
 where a.object_name = 'TAB'
  and a.data_object_id = b.data_object_id;

执行上面的sql获得如下sql执行计划,实际sql使用的是hash join连接方式:


image.png

解析执行计划:

1. TEST_TAB1表是在执行计划的上方,是驱动表。先走的是INX_TEST_TAB1索引过滤。

  access("A"."OBJECT_NAME"='TAB')

2. TEST_TAB1表再根据过滤后的结果集,再去过滤DATA_OBJECT_ID空值部分。

  filter("A"."DATA_OBJECT_ID" IS NOT NULL)

3. TEST_TAB2表过滤DATA_OBJECT_ID中的空值。

  filter("B"."DATA_OBJECT_ID" IS NOT NULL)

4.最后,根据两张表过滤后的结果集再做hash join 等值连接。

  access("A"."DATA_OBJECT_ID"="B"."DATA_OBJECT_ID")


优化过程:

 创建组合索引:

create index inx_test_tab1_od on test_tab1(object_name,data_object_id);

优化前:


image.png

优化后:


image.png

优化后可以看出,TEST_TAB1表是直接根据索引过滤数据。这样的效果其实不是特别明显。是否还有更优化的效果。

 

接着在表TEST_TAB2的DATA_OBJECT_ID创建索引:

create index inx_test_tab2_d on test_tab2(data_object_id);

image.png


从上面的执行计划可以看出,整个执行计划是走的NESTED LOOPS.

 

解析执行计划:

1.先是从INX_TEST_TAB1索引过滤出是"A"."OBJECT_NAME"='TEST'的数据。

  access("A"."OBJECT_NAME"='TEST')

2.接着是第三步,过滤掉A表的DATA_OBJECT_ID中的空值

filter("A"."DATA_OBJECT_ID" IS NOT NULL)

3.这一步是表TEST_TAB2根据索引INX_TEST_TAB2_D直接跟表TEST_TAB1做nested loops等值比对。索引是不存空值的,我认为那么filter过滤DATA_OBJECT_ID的空值也就是回表取数据的一个操作。

access("A"."DATA_OBJECT_ID"="B"."DATA_OBJECT_ID")

 filter("B"."DATA_OBJECT_ID" IS NOT NULL)

 

从上面的buffers可以看出,sql执行需要访问的数据减少,大部分是通过索引来读取数据。


那么还有更佳的执行计划吗?


在表TEST_TAB2创建复合索引:

create index inx_test_tab2_do on test_tab2(data_object_id,object_name);

image.png

从执行计划中可以看出Buffers少了一点点,因为无需从TEST_TAB2回表取数据,OBJECT_NAME数据可以从索引中返回结果。但是从效果来看,数据少的话没有什么提升的效果,如果数据量比较大会有些作用。

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

评论

0
0
最新发布
暂无内容,敬请期待...
数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部
暂无课程,敬请期待...