hash_join优化一个简单的语句

    在statpack看到下面的语句排在第二,通过加/*+USE_HASH(a) */ 来优化.下为过程和结果,有一张表有4442M,另外一张只有0.1M,原执行计划走的NL

 

     30,287,228        3,271        9,259.3   10.2    88.14    110.27 3278019490
Module: JDBC Thin Client

select distinct b.* from serv_suit a,brand_suit b where a.brand_
suit_id=b.brand_suit_id and a.acct_code=:1 and a.billing_cycle_i
d between :2 and :3

1. 原语句:

SQL> SELECT  DISTINCT b.*
  2             FROM  pgate.brand_suit b,pgate.serv_suit a
  3            WHERE a.brand_suit_id = b.brand_suit_id
  4              AND a.acct_code = &1
  5              AND a.billing_cycle_id BETWEEN &2 AND &3
  6  /

Enter value for 1: '2750d1041190'
old   4:             AND a.acct_code = &1
new   4:             AND a.acct_code = '2750d1041190'
Enter value for 2: 200711
Enter value for 3: 200811
old   5:             AND a.billing_cycle_id BETWEEN &2 AND &3
new   5:             AND a.billing_cycle_id BETWEEN 200711 AND 200811

 

Elapsed: 00:00:00.07

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (UNIQUE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SERV_SUIT'
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'BRAND_SUIT'
   5    3         INDEX (RANGE SCAN) OF 'IDX_SERVSUIT_ACCTCODE' (NON-U
          NIQUE)

 

 

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

 

SQL>

 

 

 

2. 加/*+USE_HASH(b) */ 后:

 

SQL> SELECT /*+USE_HASH(b) */ DISTINCT b.*
  2             FROM pgate.serv_suit a  , pgate.brand_suit b
  3            WHERE a.brand_suit_id = b.brand_suit_id
  4              AND a.acct_code = &1
  5              AND a.billing_cycle_id BETWEEN &2 AND &3

  6  /
Enter value for 1: '27500e033357'
old   4:             AND a.acct_code = &1
new   4:             AND a.acct_code = '27500e033357'
Enter value for 2: 200711
Enter value for 3: 200712
old   5:             AND a.billing_cycle_id BETWEEN &2 AND &3
new   5:             AND a.billing_cycle_id BETWEEN 200711 AND 200712

 

Elapsed: 00:00:00.01

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=23 Card=14 Bytes=136
          78)

 

   1    0   SORT (UNIQUE) (Cost=23 Card=14 Bytes=13678)
   2    1     HASH JOIN (Cost=6 Card=14 Bytes=13678)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'SERV_SUIT' (Cost=2 C
          ard=14 Bytes=742)

 

   4    3         INDEX (RANGE SCAN) OF 'IDX_SERVSUIT_ACCTCODE' (NON-U
          NIQUE) (Cost=1 Card=2177)

 

   5    2       TABLE ACCESS (FULL) OF 'BRAND_SUIT' (Cost=3 Card=1307
          Bytes=1207668)

 

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

 

3.可以看到consistent gets  由22288 下降到了52.

Hash join的主要资源消耗在于CPU（在内存中创建临时的hash表，并进行hash计算）,只要对两张表扫描一次,一般用于一张小表和一张大表进行join时。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10834762/viewspace-509962/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10834762/viewspace-509962/