Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: Cost of an NLJOIN from db2exfmt

    DB2 v8.2/AIX 5.3 (Non Partitioned)

    Why the cost of NLJOIN(11) suddenly goes too high(102969) in the below plan?

    Costs of the input streams are respectively 134 & 51.

    reorgchk flags only F6 for the index XPKECMBUSINESSSTP as below and the clusterratio is very good.

    HTML Code:
    SCHEMA    NAME                  CARD    OV    NP    FP ACTBLK    TSIZE  F1  F2  F3 REORG
    ----------------------------------------------------------------------------------------
    Table: CCSOWNER.ECMBUSINESSSTEP
    CCSOWNER  ECMBUSINESSSTEP    1.4e+08     0 2e+06 2e+06      - 3.29e+10   0  99 100 ---
    ----------------------------------------------------------------------------------------
    SCHEMA   NAME                 CARD  LEAF ELEAF  LVLS ISIZE  NDEL   KEYS  F4  F5  F6  F7  F8 REORG
    -------------------------------------------------------------------------------------------------
    Table: CCSOWNER.ECMBUSINESSSTEP
    CCSOWNER XPKECMBUSINESSSTP   1e+08 2e+05     0     4    12     0  1e+08 100  81 190   0   0 --*--

    HTML Code:
                          2000                       
                         ^NLJOIN                     
                         (  11)                      
                        102969                
                         8054.03                     
                    /-------+------\                 
                2000                  1              
               TBSCAN              FETCH             
               (  12)              (  18)            
              134.387             51.4365 
               54.0295                4              
                 |                /---+---\          
                2000            1       1.36401e+08  
               SORT          IXSCAN   TABLE: CCSOWNER
               (  13)        (  19)   ECMBUSINESSSTEP
               134.209       38.5812        Q3       
               54.0295          3                    
                 |             |                     
                2000       1.36401e+08               
               FETCH     INDEX: CCSOWNER             
               (  14)   XPKECMBUSINESSSTP            
               132.402         Q3                    
               54.0295
              /---+---\
          2000      1.36401e+08
         RIDSCN   TABLE: INTXN
         (  15)        TASK
         33.3465        Q2
         2.43003
           |
          2000
         SORT
         (  16)
         33.346
         2.43003
           |
          2000
         IXSCAN
         (  17)
         32.4777
         2.43003
           |
       1.36401e+08
     INDEX: INTXN
         TASK_I3
           Q2

  2. #2
    Join Date
    Feb 2009
    Posts
    114
    Because cost_of_11 = number_of_qualifying_rows_from_12 X cost_of_18 + cost_of_12 = 2000 X 51.4365 + 134.387 = 103007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •