use_hash的使用实例

59 阅读 0 评论 39 点赞

我是靠谱客的博主自信项链，最近开发中收集的这篇文章主要介绍use_hash的使用实例，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

SELECT count(*)
FROM FEEDBACK f, company r, feedback_detail d, category c
WHERE f.feedback_status = 2
   and f.feedback_target <> 5
   and f.feedback_target <> 6
   and f.receiver_company_id = r.id
   and r.is_validate = 'enabled'
   and f.id = d.feedback_id(+)
   and f.FEEDBACK_CATEGORY = c.id(+)
   and f.gmt_create >
       to_date('2008-03-05 14:30:00', 'yyyy-mm-dd hh24:mi:ss')
   and f.gmt_create <
       to_date('2008-09-05 14:30:00', 'yyyy-mm-dd hh24:mi:ss')

执行计划如下：
Execution Plan
----------------------------------------------------------
   0    SELECT STATEMENT ptimizer=FIRST_ROWS (Cost=5278 Card=1 Bytes=57)
   1 0   SORT (AGGREGATE)
   2 1     HASH JOIN (Cost=5278 Card=2843 Bytes=162051)
   3 2 NESTED LOOPS (OUTER) (Cost=5220 Card=2977 Bytes=128011)
   4 3   HASH JOIN (OUTER) (Cost=5220 Card=2977 Bytes=116103)
   5 4     TABLE ACCESS (FULL) OF 'FEEDBACK' (Cost=5217 Card=2977 Bytes=77402)
   6 4     TABLE ACCESS (FULL) OF 'FEEDBACK_DETAIL' (Cost=2 Card=82 Bytes=1066)
   7 3   INDEX (RANGE SCAN) OF 'CATEGORY_PK' (NON-UNIQUE)
   8 2 INDEX (FAST FULL SCAN) OF 'COMPANY_VLD_CTY_ID_IND' (NON-UNIQUE) (Cost=55 Card=25367 Bytes=355138)

Statistics
----------------------------------------------------------
      0 recursive calls
      0 db block gets
      79616 consistent gets
      0 physical reads
      0 redo size
    520 bytes sent via SQL*Net to client
    655 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed


走的是nested loop

增加hint走 hash join

SELECT /*+use_hash(f,r,d,c) */
count(*)
FROM FEEDBACK f,company r,feedback_detail d,category c
WHERE f.feedback_status = 2 and f.feedback_target <> 5 and f.feedback_target <> 6
and f.receiver_company_id = r.id and r.is_validate = 'enabled' and f.id=d.feedback_id(+)
and f.FEEDBACK_CATEGORY = c.id(+)
and f.gmt_create > to_date('2008-03-05 14:30:00','yyyy-mm-dd hh24:mi:ss')
and f.gmt_create < to_date('2008-09-05 14:30:00','yyyy-mm-dd hh24:mi:ss')

执行计划如下：

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=FIRST_ROWS (Cost=5281 Card=1 Byte
s=57)

   1 0   SORT (AGGREGATE)
   2 1     HASH JOIN (OUTER) (Cost=5281 Card=2843 Bytes=162051)
   3 2 HASH JOIN (Cost=5277 Card=2843 Bytes=125092)
   4 3   HASH JOIN (OUTER) (Cost=5220 Card=2977 Bytes=89310)
   5 4     TABLE ACCESS (FULL) OF 'FEEDBACK' (Cost=5217 Card=
      2977 Bytes=77402)

6 4 INDEX (FAST FULL SCAN) OF 'CATEGORY_PK' (NON-UNIQU
E) (Cost=2 Card=836 Bytes=3344)

7 3 INDEX (FAST FULL SCAN) OF 'COMPANY_VLD_CTY_ID_IND' (
NON-UNIQUE) (Cost=55 Card=25367 Bytes=355138)

8 2 TABLE ACCESS (FULL) OF 'FEEDBACK_DETAIL' (Cost=2 Card=
82 Bytes=1066)

Statistics
----------------------------------------------------------
      0 recursive calls
      0 db block gets
      64638 consistent gets
      0 physical reads
      0 redo size
    520 bytes sent via SQL*Net to client
    655 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed


一致性读减少了，，由于数据量比较小，若数据量很大的话，一致性读的减少量更大。如

japan@JAPAN>SELECT
2 count(*)
3 FROM FEEDBACK f,company r,feedback_detail d,category c
4 WHERE f.feedback_status = 2 and f.feedback_target <> 5 and f.feedback_target <> 6
5 and f.receiver_company_id = r.id and r.is_validate = 'enabled' and f.id=d.feedback_id(+)
6 and f.FEEDBACK_CATEGORY = c.id(+)
7 /

COUNT(*)
----------
267142

Elapsed: 00:00:00.99

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=FIRST_ROWS (Cost=5305 Card=1 Byte
s=50)

   1 0   SORT (AGGREGATE)
   2 1     NESTED LOOPS (OUTER) (Cost=5305 Card=52351 Bytes=2617550
      )

   3 2 HASH JOIN (Cost=5305 Card=52351 Bytes=2408146)
   4 3   INDEX (FAST FULL SCAN) OF 'COMPANY_VLD_CTY_ID_IND' (
      NON-UNIQUE) (Cost=55 Card=25367 Bytes=355138)

5 3 HASH JOIN (OUTER) (Cost=5233 Card=54835 Bytes=175472
0)

6 5 TABLE ACCESS (FULL) OF 'FEEDBACK' (Cost=5217 Card=
54835 Bytes=1041865)

7 5 TABLE ACCESS (FULL) OF 'FEEDBACK_DETAIL' (Cost=2 C
ard=82 Bytes=1066)

8 2 INDEX (RANGE SCAN) OF 'CATEGORY_PK' (NON-UNIQUE)

Statistics
----------------------------------------------------------
      0 recursive calls
      0 db block gets
     254410 consistent gets
      0 physical reads
      0 redo size
    520 bytes sent via SQL*Net to client
    655 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed

nested loop 的一致性读为 254410

japan@JAPAN>SELECT /*+use_hash(f,r,d,c) */
2 count(*)
3 FROM FEEDBACK f,company r,feedback_detail d,category c
4 WHERE f.feedback_status = 2 and f.feedback_target <> 5 and f.feedback_target <> 6
5 and f.receiver_company_id = r.id and r.is_validate = 'enabled' and f.id=d.feedback_id(+)
6 and f.FEEDBACK_CATEGORY = c.id(+)
7 /

COUNT(*)
----------
267251

Elapsed: 00:00:00.67

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=FIRST_ROWS (Cost=5325 Card=1 Byte
s=50)

   1 0   SORT (AGGREGATE)
   2 1     HASH JOIN (Cost=5325 Card=52351 Bytes=2617550)
   3 2 INDEX (FAST FULL SCAN) OF 'COMPANY_VLD_CTY_ID_IND' (NO
      N-UNIQUE) (Cost=55 Card=25367 Bytes=355138)

   4 2 HASH JOIN (OUTER) (Cost=5251 Card=54835 Bytes=1974060)
   5 4   HASH JOIN (OUTER) (Cost=5233 Card=54835 Bytes=126120
      5)

6 5 TABLE ACCESS (FULL) OF 'FEEDBACK' (Cost=5217 Card=
54835 Bytes=1041865)

7 5 INDEX (FAST FULL SCAN) OF 'CATEGORY_PK' (NON-UNIQU
E) (Cost=2 Card=836 Bytes=3344)

8 4 TABLE ACCESS (FULL) OF 'FEEDBACK_DETAIL' (Cost=2 Car
d=82 Bytes=1066)