ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE HASH JOIN被探测表的选择

ORACLE HASH JOIN被探测表的选择

原创 Linux操作系统 作者:wsbupt 时间:2009-11-22 15:31:44 0 删除 编辑

ORACLE hash join首先选择一个表采用hash结构放入内存,这个表我们通常称为被探测表,然后读取第个表,计算关联列的hash值,看是否能够找到关联到记录。通常应该选用小表作为被探测表优先放入内存中。

下面看这个例子:

SQL> select count(*) from  endss.en_ppc_effect_sumdt0 where yyyymmdd = trunc(sysdate-2)   ;

  COUNT(*)
----------
    191430

Elapsed: 00:00:02.27
SQL> select count(*) from enlog.session_fatdt0  where yyyymmdd = trunc(sysdate-2) ;

  COUNT(*)
----------
   1913790

下面这个SQL在一分钟就执行完成。

SQL>    SELECT  /*+ordered use_hash(a b c d e) parallel(b 4)*/trunc(sysdate-2),
  2             c.modifier AS account,d.top_category AS campaign,a.product_name as adgroup,a.ppc_keyword AS keyword,e.match_type,
  3             SUM(a.session_cnt),COUNT(DISTINCT b.cookie_id),SUM(a.sap_cnt),SUM(a.pv),SUM(a.nsnc_new_fb_cnt),SUM(a.os_atm_inquiry_cnt),SUM(a.os_inquiry_cnt),
  4             COUNT(DISTINCT a.active_buyer),COUNT(DISTINCT a.os_atm_inquiry_sender),COUNT(DISTINCT a.os_inquiry_sender),'DAILY'
  5     FROM    enlog.ppc_modifier_dimt0 c,
  6             enlog.ppc_top_cate_dimt0 d,
  7             enlog.ppc_match_type_dimt0 e,
  8             endss.en_ppc_effect_sumdt0 a,
  9             enlog.session_fatdt0 b

 10     WHERE   a.step = 1
 11     AND     a.yyyymmdd = b.yyyymmdd
 12     AND     a.session_id = b.session_id
 13     AND     a.put_in_modifier_id = c.modifier_id
 14     AND     a.top_category_id = d.top_category_id
 15     AND     a.match_type_id = e.match_type_id
 16     AND     a.yyyymmdd = trunc(sysdate-2)
 17     GROUP BY c.modifier,d.top_category,a.product_name,a.ppc_keyword,e.match_type;

39394 rows selected.

Elapsed: 00:01:09.17

Execution Plan
----------------------------------------------------------
Plan hash value: 4192880306

----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                        |     1 |   690 |  1956K  (1)| 09:46:50 |       |       |        |      |            |
|   1 |  PX COORDINATOR                    |                        |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10003               |     1 |   690 |  1956K  (1)| 09:46:50 |       |       |  Q1,03 | P->S | QC (RAND)  |
|   3 |    SORT GROUP BY                   |                        |     1 |   690 |  1956K  (1)| 09:46:50 |       |       |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                     |                        |     1 |   690 |  1956K  (1)| 09:46:50 |       |       |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH                  | :TQ10002               |     1 |   690 |  1956K  (1)| 09:46:50 |       |       |  Q1,02 | P->P | HASH       |
|*  6 |       HASH JOIN BUFFERED           |                        |     1 |   690 |  1956K  (1)| 09:46:50 |       |       |  Q1,02 | PCWP |            |
|   7 |        BUFFER SORT                 |                        |       |       |            |          |       |       |  Q1,02 | PCWC |            |
|   8 |         PX RECEIVE                 |                        |     1 |   616 |  1956K  (1)| 09:46:50 |       |       |  Q1,02 | PCWP |            |
|   9 |          PX SEND HASH              | :TQ10000               |     1 |   616 |  1956K  (1)| 09:46:50 |       |       |        | S->P | HASH       |
|* 10 |           HASH JOIN                |                        |     1 |   616 |  1956K  (1)| 09:46:50 |       |       |        |      |            |
|  11 |            PARTITION RANGE SINGLE  |                        |     1 |   554 |     2   (0)| 00:00:01 |   KEY |   KEY |        |      |            |
|* 12 |             TABLE ACCESS FULL      | EN_PPC_EFFECT_SUMDT0   |     1 |   554 |     2   (0)| 00:00:01 |   KEY |   KEY |        |      |            |
|  13 |            MERGE JOIN CARTESIAN    |                        |    24M|  1459M|  1955K  (1)| 09:46:48 |       |       |        |      |            |
|  14 |             MERGE JOIN CARTESIAN   |                        |   601K|    27M|  7645   (1)| 00:02:18 |       |       |        |      |            |
|  15 |              VIEW                  | index$_join$_001       |   199 |  5373 |     3   (0)| 00:00:01 |       |       |        |      |            |
|* 16 |               HASH JOIN            |                        |       |       |            |          |       |       |        |      |            |
|  17 |                INDEX FAST FULL SCAN| IDX_PPC_MODIFIER_DIMT0 |   199 |  5373 |     1   (0)| 00:00:01 |       |       |        |      |            |
|  18 |                INDEX FAST FULL SCAN| PPC_MODIFIER_PK        |   199 |  5373 |     1   (0)| 00:00:01 |       |       |        |      |            |
|  19 |              BUFFER SORT           |                        |  3025 | 63525 |  7642   (1)| 00:02:18 |       |       |        |      |            |
|  20 |               TABLE ACCESS FULL    | PPC_TOP_CATE_DIMT0     |  3025 | 63525 |    38   (0)| 00:00:01 |       |       |        |      |            |
|  21 |             BUFFER SORT            |                        |    41 |   574 |  1955K  (1)| 09:46:47 |       |       |        |      |            |
|  22 |              TABLE ACCESS FULL     | PPC_MATCH_TYPE_DIMT0   |    41 |   574 |     3   (0)| 00:00:01 |       |       |        |      |            |
|  23 |        PX RECEIVE                  |                        |     1 |    74 |     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  24 |         PX SEND HASH               | :TQ10001               |     1 |    74 |     2   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |
|  25 |          PX BLOCK ITERATOR         |                        |     1 |    74 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWC |            |
|* 26 |           TABLE ACCESS FULL        | SESSION_FATDT0         |     1 |    74 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------------------

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

   6 - access("A"."YYYYMMDD"="B"."YYYYMMDD" AND "A"."SESSION_ID"="B"."SESSION_ID")
  10 - access("A"."PUT_IN_MODIFIER_ID"="C"."MODIFIER_ID" AND "A"."TOP_CATEGORY_ID"="D"."TOP_CATEGORY_ID" AND
              "A"."MATCH_TYPE_ID"="E"."MATCH_TYPE_ID")
  12 - filter("A"."STEP"=1 AND "A"."YYYYMMDD"=TRUNC(SYSDATE@!-2))
  16 - access(ROWID=ROWID)
  26 - filter("B"."YYYYMMDD"=TRUNC(SYSDATE@!-2))


Statistics
----------------------------------------------------------
         53  recursive calls
          5  db block gets
      10695  consistent gets
       9322  physical reads
      16024  redo size
    2357194  bytes sent via SQL*Net to client
      29374  bytes received via SQL*Net from client
       2628  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
      39394  rows processed

在我调换了成这样后,这个sql在半小时都没有执行完成。

SELECT  /*+ordered use_hash(a b c d e) parallel(b 4)*/trunc(sysdate-2),                                                                                                   
        c.modifier AS account,d.top_category AS campaign,a.product_name as adgroup,a.ppc_keyword AS keyword,e.match_type,                                              
        SUM(a.session_cnt),COUNT(DISTINCT b.cookie_id),SUM(a.sap_cnt),SUM(a.pv),SUM(a.nsnc_new_fb_cnt),SUM(a.os_atm_inquiry_cnt),SUM(a.os_inquiry_cnt),                
        COUNT(DISTINCT a.active_buyer),COUNT(DISTINCT a.os_atm_inquiry_sender),COUNT(DISTINCT a.os_inquiry_sender),'DAILY'                                             
FROM    enlog.ppc_modifier_dimt0 c,                                                                                                                                    
        enlog.ppc_top_cate_dimt0 d,                                                                                                                                    
        enlog.ppc_match_type_dimt0 e,
        enlog.session_fatdt0 b,                                                                                                                                 
        endss.en_ppc_effect_sumdt0 a
                                                                                                                                                                                                                                                                                           
WHERE   a.step = 1                                                                                                                                                     
AND     a.yyyymmdd = b.yyyymmdd                                                                                                                                        
AND     a.session_id = b.session_id                                                                                                                                    
AND     a.put_in_modifier_id = c.modifier_id                                                                                                                           
AND     a.top_category_id = d.top_category_id                                                                                                                          
AND     a.match_type_id = e.match_type_id                                                                                                                              
AND     a.yyyymmdd = trunc(sysdate-2)                                                                                                                                  
GROUP BY c.modifier,d.top_category,a.product_name,a.ppc_keyword,e.match_type;

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

全部评论

注册时间:2009-01-20

  • 博文量
    16
  • 访问量
    32917