爱悠闲 > group by hash join优化案例

group by hash join优化案例

分类: SQL优化案例  |  作者: haoxiaoyan 相关  |  发布日期 : 2014-06-27  |  热度 : 1585°

  原本有两个表,其中一个表的数据是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     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   100 | 10900 |    11  (19)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |   100 | 10900 |    11  (19)    | 00:00:01 |
|*  2 |   HASH JOIN         |      |   100 | 10900 |    10  (10)           | 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |   100 |  9600 |     2   (0) | 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |  2000 | 26000 |     7  (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         92  recursive calls
          0  db block gets
         74  consistent gets
         27  physical reads
          0  redo size
       3466  bytes sent via SQL*Net to client
        535  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        100  rows processed

 

 

SQL> select a.owner,a.object_name,a.object_id from t1 a  where  exists (select 1 from 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     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   100 | 10900 |    11  (19)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |   100 | 10900 |    11  (19)| 00:00:01 |
|*  2 |   HASH JOIN         |      |   100 | 10900 |    10  (10)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |   100 |  9600 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |  2000 | 26000 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         66  consistent gets
          0  physical reads
          0  redo size
       3466  bytes sent via SQL*Net to client
        535  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        100  rows processed

明显少了不少的一致读,索引hash join有的时候改下成hash join半连接性能提示还是很明显的