多表关联SQL如何优化

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;

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

表TEST_TAB1：

表TEST_TAB2：

从截图中可以看到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连接方式：

解析执行计划：

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);

优化前：

优化后：

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

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

create index inx_test_tab2_d on test_tab2(data_object_id);

从上面的执行计划可以看出，整个执行计划是走的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);

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