Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Oct 2008
    Posts
    82

    Cool Unanswered: Performance Issue

    Os: AIX
    Version : 9.1

    Select query taking nearly 7 - 8 hrs. to complete however same query was taking less than hour in same environment and with same amount of data.

    Estimated Cost = 141769.031250


    can any one please suggest on this.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Obviously something changed. Maybe if you supply more info like the query, access plan, etc, you might get a better response.

    Andy

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rameshj6
    can any one please suggest on this.
    I'd suggest that you try to make it run faster.

    Adding to ARWinner's comment, if you provide more information about your problem then we can probably provide more information or suggestions for a solution.

    -PatP

  4. #4
    Join Date
    Oct 2008
    Posts
    82

    query and access plan

    Please find the Explain Plan..............
    Last edited by rameshj6; 03-18-09 at 14:20.

  5. #5
    Join Date
    Oct 2008
    Posts
    82

    Cool

    Code:
    ==================== STATEMENT ==========================================
    
    	Isolation Level          = Cursor Stability
    	Blocking                 = Block Unambiguous Cursors
    	Query Optimization Class = 7
    
    	Partition Parallel       = No
    	Intra-Partition Parallel = No
    
    	SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", 
                                       "CRIDWE"
    
    
    Statement Isolation Level = Uncommitted Read
    
    Section Code Page = 1208
    
    Estimated Cost = 141769.031250
    Estimated Cardinality = 202583.484375
    
    Access Table Name = ANLYSIS.DIM_GEO_RME  ID = 14,31
    |  #Columns = 3
    |  Relation Scan
    |  |  Prefetch: Eligible
    Isolation Level: Uncommitted Read
    |  Lock Intents
    |  |  Table: Intent None
    |  |  Row  : None
    |  Sargable Predicate(s)
    |  |  #Predicates = 1
    |  |  Process Build Table for Hash Join
    Hash Join
    |  Estimated Build Size: 8000   
    |  Estimated Probe Size: 1776000
    |  Access Table Name = ANLYSIS.DIM_CST_LCL  ID = 14,19
    |  |  Index Scan:  Name = ANLYSIS.SRC_SYS_ID_DIM_CST_LCL_IDX  ID = 3
    |  |  |  Regular Index (Not Clustered)
    |  |  |  Index Columns:
    |  |  |  |  1: SRC_SYS_ID (Ascending)
    |  |  #Columns = 0
    |  |  #Key Columns = 1
    |  |  |  Start Key: Inclusive Value
    |  |  |  |  |  1: '1'
    |  |  |  Stop Key: Inclusive Value
    |  |  |  |  |  1: '1'
    |  |  Index-Only Access
    |  |  Index Prefetch: Eligible 15
    Isolation Level: Uncommitted Read
    |  |  Lock Intents
    |  |  |  Table: Intent None
    |  |  |  Row  : None
    |  |  Sargable Index Predicate(s)
    |  |  |  Insert Into Sorted Temp Table  ID = t1
    |  |  |  |  #Columns = 1
    |  |  |  |  #Sort Key Columns = 1
    |  |  |  |  |  Key 1: (Ascending)
    |  |  |  |  Sortheap Allocation Parameters:
    |  |  |  |  |  #Rows     = 27058.000000
    |  |  |  |  |  Row Width = 16
    |  |  |  |  Piped
    |  |  |  |  Duplicate Elimination
    |  Sorted Temp Table Completion  ID = t1
    |  List Prefetch Preparation
    |  |  Access Table Name = ANLYSIS.DIM_CST_LCL  ID = 14,19
    |  |  |  #Columns = 6
    |  |  |  Fetch Using Prefetched List
    |  |  |  |  Prefetch: 1414 Pages
    Isolation Level: Uncommitted Read
    |  |  |  Lock Intents
    |  |  |  |  Table: Intent None
    |  |  |  |  Row  : None
    |  |  |  Sargable Predicate(s)
    |  |  |  |  #Predicates = 1
    |  |  |  |  Process Probe Table for Hash Join
    |  Residual Predicate(s)
    |  |  #Predicates = 2
    Hash Join
    |  Estimated Build Size: 2016000
    |  Estimated Probe Size: 72752000
    |  Access Table Name = ANLYSIS.DIM_PD_LCL  ID = 14,14
    |  |  Index Scan:  Name = ANLYSIS.SRC_SYS_ID_DIM_PD_LCL_IDX  ID = 6
    |  |  |  Regular Index (Not Clustered)
    |  |  |  Index Columns:
    |  |  |  |  1: SRC_SYS_ID (Ascending)
    |  |  #Columns = 10
    |  |  #Key Columns = 1
    |  |  |  Start Key: Inclusive Value
    |  |  |  |  |  1: '1'
    |  |  |  Stop Key: Inclusive Value
    |  |  |  |  |  1: '1'
    |  |  Data Prefetch: Eligible 3534
    |  |  Index Prefetch: Eligible 3534
    Isolation Level: Uncommitted Read
    |  |  Lock Intents
    |  |  |  Table: Intent None
    |  |  |  Row  : None
    |  |  Sargable Predicate(s)
    |  |  |  #Predicates = 1
    |  |  |  Process Build Table for Hash Join
    |  Hash Join
    |  |  Early Out: Single Match Per Outer Row
    |  |  Estimated Build Size: 640000 
    |  |  Estimated Probe Size: 835008000
    |  |  Access Table Name = ANLYSIS.ANL_SALES_WTD  ID = 5,42
    |  |  |  #Columns = 10
    |  |  |  Relation Scan
    |  |  |  |  Prefetch: Eligible
    Isolation Level: Uncommitted Read
    |  |  |  Lock Intents
    |  |  |  |  Table: Intent None
    |  |  |  |  Row  : None
    |  |  |  Sargable Predicate(s)
    |  |  |  |  Process Probe Table for Hash Join
    Insert Into Sorted Temp Table  ID = t2
    |  #Columns = 17
    |  #Sort Key Columns = 12
    |  |  Key 1: CNTRY_CD (Ascending)
    |  |  Key 2: (Ascending)
    |  |  Key 3: EASI_PD_GRP (Ascending)
    |  |  Key 4: EASI_PD_CAT (Ascending)
    |  |  Key 5: EASI_SUB_CAT (Ascending)
    |  |  Key 6: PD_FRNT_TRTMNT_CD (Ascending)
    |  |  Key 7: EASI_MKT (Ascending)
    |  |  Key 8: PD_FRNT_CD (Ascending)
    |  |  Key 9: (Ascending)
    |  |  Key 10: EXCT_IND (Ascending)
    |  |  Key 11: FISCAL_YEAR (Ascending)
    |  |  Key 12: FISCAL_WEEK_NBR (Ascending)
    |  Sortheap Allocation Parameters:
    |  |  #Rows     = 202584.000000
    |  |  Row Width = 304
    |  Piped
    Access Temp Table  ID = t2
    |  #Columns = 17
    |  Relation Scan
    |  |  Prefetch: Eligible
    Return Data to Application
    |  #Columns = 22
    
    End of section
    
    
    Optimizer Plan:
    
                                                  RETURN 
                                                  (   1) 
                                                    |    
                                                  TBSCAN 
                                                  (   2) 
                                                    |    
                                                   SORT  
                                                  (   3) 
                                                    |    
                                                  HSJOIN 
                                                  (   4) 
                               /-----------------/      \-----------------\
                         HSJOIN                                            HSJOIN 
                         (   5)                                            (   9) 
               /--------/      \--------\                               /-/      \
         TBSCAN                          FETCH                    FETCH            TBSCAN 
         (   6)                          (   7)                   (----)           (  14) 
           |                        /---/      \                 /      \            |    
     Table:                   IXSCAN           Table:      RIDSCN  Table:       Table:      
     ANLYSIS                  (   7)           ANLYSIS     (  11)  ANLYSIS      ANLYSIS     
     ANL_SALES_WTD              |              DIM_PD_LCL    |     DIM_CST_LCL  DIM_GEO_RME 
                    Index:                                  SORT  
                    ANLYSIS                                (  12) 
                    SRC_SYS_ID_DIM_PD_LCL_IDX                |    
                                                           IXSCAN 
                                                           (  13) 
                                                          /      \---\
                                          Index:                      Table:      
                                          ANLYSIS                     ANLYSIS     
                                          SRC_SYS_ID_DIM_CST_LCL_IDX  DIM_CST_LCL
    Last edited by Pat Phelan; 03-18-09 at 15:04. Reason: Added code blocks to improve formatting

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use the db2advis tool - it may suggest some indexes.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm sorry, I was too late.
    You already used [code] tag.

  8. #8
    Join Date
    Oct 2008
    Posts
    82
    Quote Originally Posted by n_i
    Use the db2advis tool - it may suggest some indexes.
    I done evrything...........Is there any chance to change the parameters for increasing the performance of a query

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by rameshj6
    I done evrything...........Is there any chance to change the parameters for increasing the performance of a query
    Not really, it is all about the access plan. You access plan is showing 2 table-scans. With the little information you gave, I would look how you are accessing those tables.

    Andy

  10. #10
    Join Date
    Oct 2008
    Posts
    82

    Cool

    select "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" "Country_Code" , "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM" "Customer" , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" "Product_Group_Code" , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT" "Product_Category_1_Code" , "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT" "Product_Category_2_Code" , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD" "Treatment_Code" , "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT" "Marketing_Code" , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD" "Front_Code" , "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM" "Product" , "ANL_SALES_WTD"."EXCT_IND" "Exact_Indicator" , "ANL_SALES_WTD"."FISCAL_YEAR" "Year11" , "ANL_SALES_WTD"."FISCAL_WEEK_NBR" "Week_Number" , (coalesce("ANL_SALES_WTD"."SAL_VOL_SQM", 0)) "Sales_Volume_SQM" , (coalesce("ANL_SALES_WTD"."SALE_VAL_EUR", 0)) "Sales_Value_Euro" , (coalesce("ANL_SALES_WTD"."SALE_VAL_LV_CNTRY", 0)) "Sales_Value_LV_Country" , (coalesce("ANL_SALES_WTD"."MAT_COST_EUR", 0)) "Material_Cost_Euro" , (coalesce("ANL_SALES_WTD"."MAT_COST_LV_CNTRY", 0)) "Material_Cost_LV_Country" , sum((coalesce("ANL_SALES_WTD"."SAL_VOL_SQM", 0))) over (partition by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD", "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT", "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM", "ANL_SALES_WTD"."EXCT_IND", "ANL_SALES_WTD"."FISCAL_YEAR"
    order by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" asc , "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM" asc , "ANL_SALES_WTD"."EXCT_IND" asc , "ANL_SALES_WTD"."FISCAL_YEAR" asc , "ANL_SALES_WTD"."FISCAL_WEEK_NBR" asc rows unbounded preceding) "Sales_Volume_SQM_YTD" , sum((coalesce("ANL_SALES_WTD"."SALE_VAL_EUR", 0))) over (partition by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD", "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT", "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM", "ANL_SALES_WTD"."EXCT_IND", "ANL_SALES_WTD"."FISCAL_YEAR"
    order by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" asc , "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM" asc , "ANL_SALES_WTD"."EXCT_IND" asc , "ANL_SALES_WTD"."FISCAL_YEAR" asc , "ANL_SALES_WTD"."FISCAL_WEEK_NBR" asc rows unbounded preceding) "Sales_Value_Euro_YTD" , sum((coalesce("ANL_SALES_WTD"."SALE_VAL_LV_CNTRY", 0))) over (partition by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD", "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT", "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM", "ANL_SALES_WTD"."EXCT_IND", "ANL_SALES_WTD"."FISCAL_YEAR"
    order by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" asc , "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM" asc , "ANL_SALES_WTD"."EXCT_IND" asc , "ANL_SALES_WTD"."FISCAL_YEAR" asc , "ANL_SALES_WTD"."FISCAL_WEEK_NBR" asc rows unbounded preceding) "Sales_Value_LV_Country_YTD" , sum((coalesce("ANL_SALES_WTD"."MAT_COST_EUR", 0))) over (partition by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD", "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT", "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM", "ANL_SALES_WTD"."EXCT_IND", "ANL_SALES_WTD"."FISCAL_YEAR"
    order by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" asc , "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM" asc , "ANL_SALES_WTD"."EXCT_IND" asc , "ANL_SALES_WTD"."FISCAL_YEAR" asc , "ANL_SALES_WTD"."FISCAL_WEEK_NBR" asc rows unbounded preceding) "Material_Cost_Euro_YTD" , sum((coalesce("ANL_SALES_WTD"."MAT_COST_LV_CNTRY", 0))) over (partition by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD", "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP", "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT", "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT", "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD", "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM", "ANL_SALES_WTD"."EXCT_IND", "ANL_SALES_WTD"."FISCAL_YEAR"
    order by "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" asc , "DIM_CST_LCL_VW_Anl_Sales"."CST_CODE" || '-' || "DIM_CST_LCL_VW_Anl_Sales"."CST_NM" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_SUB_CAT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_TRTMNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."EASI_MKT" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_FRNT_CD" asc , "DIM_PD_LCL_VW_Anl_Sales"."PD_CD" || '-' || "DIM_PD_LCL_VW_Anl_Sales"."PD_NM" asc , "ANL_SALES_WTD"."EXCT_IND" asc , "ANL_SALES_WTD"."FISCAL_YEAR" asc , "ANL_SALES_WTD"."FISCAL_WEEK_NBR" asc rows unbounded preceding) "Material_Cost_LV_Country_YTD"
    from "ANLYSIS"."DIM_GEO_RME_VW" "DIM_GEO_RME_VW_Cust_Anl_Sales", "ANLYSIS"."DIM_CST_LCL_VW" "DIM_CST_LCL_VW_Anl_Sales", "ANLYSIS"."DIM_PD_LCL_VW" "DIM_PD_LCL_VW_Anl_Sales", "ANLYSIS"."ANL_SALES_WTD" "ANL_SALES_WTD"
    where "DIM_PD_LCL_VW_Anl_Sales"."SRC_SYS_ID" = '1' and "DIM_CST_LCL_VW_Anl_Sales"."SRC_SYS_ID" = '1' and "DIM_GEO_RME_VW_Cust_Anl_Sales"."SRC_SYS_ID" = '1' and NOT ("DIM_CST_LCL_VW_Anl_Sales"."CST_CMPNY_TP" in ('I', 'S') and "DIM_GEO_RME_VW_Cust_Anl_Sales"."FINCL_SLS_AREA_CD " = 'A') and "DIM_PD_LCL_VW_Anl_Sales"."EASI_PD_GRP" <> 'P' and "DIM_CST_LCL_VW_Anl_Sales"."EASI_CNTRY_CD" = "DIM_GEO_RME_VW_Cust_Anl_Sales"."CNTRY_CD" and "DIM_CST_LCL_VW_Anl_Sales"."CST_ID" = "ANL_SALES_WTD"."CST_ID" and "DIM_PD_LCL_VW_Anl_Sales"."PD_ID" = "ANL_SALES_WTD"."PD_ID"
    order by "Country_Code" asc , "Customer" asc , "Product_Group_Code" asc , "Product_Category_1_Code" asc , "Product_Category_2_Code" asc , "Treatment_Code" asc , "Marketing_Code" asc , "Front_Code" asc , "Product" asc , "Exact_Indicator" asc , "Year11" asc , "Week_Number" asc with ur @

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How about making it easier on those of us who are trying to help you. Format the query so that it is easy to read.

    Andy

  12. #12
    Join Date
    Oct 2008
    Posts
    82

    Cool

    Please find the attachment for query
    Attached Files Attached Files

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by rameshj6
    Please find the attachment for query
    It is still not very readable. Hint: keep expressions together and try to keep each line to under 80 characters and use indentation and use explicit joins.

    Andy

  14. #14
    Join Date
    Oct 2008
    Posts
    82

    Cool

    Can you please check the attachment now,I think it is good now
    Attached Files Attached Files

  15. #15
    Join Date
    Oct 2008
    Posts
    82

    Cool

    In addition i ran db2advise tool, Please find it below


    Using user id as default schema name. Use -n option to specify schema
    execution started at timestamp 2009-03-19-08.02.21.841283
    found [1] SQL statements from the input file
    Recommending indexes...
    total disk space needed for initial set [ 4.459] MB
    total disk space constrained to [17346.194] MB
    Trying variations of the solution set.
    Optimization finished.
    2 indexes in current solution
    [141769.0000] timerons (without recommendations)
    [140863.0000] timerons (with current solution)
    [0.64%] improvement


    --
    --
    -- LIST OF RECOMMENDED INDEXES
    -- ===========================
    -- index[1], 3.251MB
    CREATE INDEX "CRIDWE "."IDX903191503580000" ON "ANLYSIS "."DIM_PD_LCL"
    ("SRC_SYS_ID" ASC, "EASI_PD_GRP" ASC, "EASI_PD_CAT"
    ASC, "EASI_SUB_CAT" ASC, "PD_FRNT_TRTMNT_CD" ASC,
    "EASI_MKT" ASC, "PD_FRNT_CD" ASC, "PD_NM" ASC, "PD_CD"
    ASC, "PD_ID" ASC) ALLOW REVERSE SCANS ;
    COMMIT WORK ;
    RUNSTATS ON TABLE "ANLYSIS "."DIM_PD_LCL" FOR INDEX "CRIDWE "."IDX903191503580000" ;
    COMMIT WORK ;
    -- index[2], 1.208MB
    CREATE INDEX "CRIDWE "."IDX903191502490000" ON "ANLYSIS "."DIM_CST_LCL"
    ("SRC_SYS_ID" ASC, "EASI_CNTRY_CD" ASC, "CST_CMPNY_TP"
    ASC, "CST_CODE" ASC, "CST_NM" ASC, "CST_ID" ASC) ALLOW
    REVERSE SCANS ;
    COMMIT WORK ;
    RUNSTATS ON TABLE "ANLYSIS "."DIM_CST_LCL" FOR INDEX "CRIDWE "."IDX903191502490000" ;
    COMMIT WORK ;

Posting Permissions

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