ITPub博客

首页 > Linux操作系统 > Linux操作系统 > hash_join优化一个简单的语句

hash_join优化一个简单的语句

原创 Linux操作系统 作者:zengzg 时间:2019-06-23 09:12:06 0 删除 编辑
    在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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 登录审计trigger
请登录后发表评论 登录
全部评论

注册时间:2009-09-09

  • 博文量
    50
  • 访问量
    38034