group by hash join优化案例

  原本有两个表，其中一个表的数据是150W 另一个表的数据是15W，他们两个hash join后花费的时间很长，后改成半连接后性能明显提升不少

SQL> conn hxy/hxy as sysdba
Connected.
SQL> create table t1 as select * from dba_objects where rownum<=100;

Table created.

SQL> create table t2 as select * from dba_objects where rownum<=2000;

Table created.

SQL> set autotrace traceonly

hash join 连接的

SQL> select a.owner,a.object_name,a.object_id from t1 a ,t2 b where a.object_id=b.object_id group by a.owner,a.object_name,a.object_id;

100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3226881135

---------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------