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

    Red face Unanswered: LOJ - SQL Tuning

    A simple LOJ involving couple of tables is not using the index which I feel that it should use. Since it is not using the index the cost of the query is in millions. Please suggest some tips.

    All the tables involved are paritioned tables based on the same partitioning key and available in the same nodegroup.

    Attached the explain plan (q1.sql.txt) for your reference.

    Following is the reorgchk output for the big global_veh table
    SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
    ----------------------------------------------------------------------------------------
    Table: CARD.GLOBAL_VEH
    CARD GLOBAL_VEH 3.5e+07 0 4e+06 4e+06 - 1.57e+10 0 96 99 ---
    ----------------------------------------------------------------------------------------

    SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
    -------------------------------------------------------------------------------------------------
    Table: CARD.GLOBAL_VEH
    CARD CL_X1GV_NE 4e+07 3e+05 0 4 19 0 4e+07 93 79 4 0 0 -----
    CARD X1E1GV_NE 4e+07 55041 0 3 10 0 23528 80 80 0 0 0 *----
    CARD XIE2GV_NE 4e+07 54564 0 3 12 0 23658 80 81 0 0 0 *----
    CARD XIE3GV_NE 4e+07 54516 0 3 11 0 2 100 80 0 0 0 -----
    CARD XIE4GV_NE 4e+07 54516 0 3 11 0 64 99 80 0 0 0 -----
    CARD XIE5GV_NE 4e+07 54774 0 3 10 0 301 97 80 0 0 0 -----
    -------------------------------------------------------------------------------------------------

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2005
    Location
    Bangalore,India
    Posts
    39
    1.Rewrite the query as per below(remove the redunt Subqueries)

    select ETL.STG_NA_COMS_GLOBAL_VEH.VEH_IDENT_NBR ,
    ETL.STG_NA_COMS_GLOBAL_VEH.FUEL_ECONOMY_CD,
    ETL.STG_NA_COMS_GLOBAL_VEH.FUEL_ECON_LABEL,
    ETL.STG_NA_COMS_GLOBAL_VEH.TIRE_LABEL,
    ETL.STG_NA_COMS_GLOBAL_VEH.BUMPER_CD,
    ETL.STG_NA_COMS_GLOBAL_VEH.RAMP_CD,
    ETL.STG_NA_COMS_GLOBAL_VEH.SHIP_MODE_CD,
    ETL.STG_NA_COMS_GLOBAL_VEH.CARB_TEST_IND ,
    ETL.STG_NA_COMS_GLOBAL_VEH.TRANSMIT_DT,
    ETL.STG_NA_COMS_GLOBAL_VEH.VEH_SEQ_NBR,
    ETL.STG_NA_COMS_GLOBAL_VEH.SOURCE_IND , GV.VEH_IDENT_NBR AS GV_VIN,
    GVS.VEH_IDENT_NBR AS GVS_VIN
    FROM ETL.STG_NA_COMS_GLOBAL_VEH
    LEFT OUTER JOIN CARD.GLOBAL_VEH AS GV
    ON ETL.STG_NA_COMS_GLOBAL_VEH.VEH_IDENT_NBR = GV.VEH_IDENT_NBR
    LEFT OUTER JOIN SMTUMT.GLOBAL_VEH_SUSPENSE AS GVS
    ON ETL.STG_NA_COMS_GLOBAL_VEH.VEH_IDENT_NBR = GVS.VEH_IDENT_NBR
    WHERE ETL.STG_NA_COMS_GLOBAL_VEH.LOAD_STATUS = 'N'
    AND CARD.GLOBAL_VEH.SOURCE_SYSTEM_CD in (4,8)
    AND SMTUMT.GLOBAL_VEH_SUSPENSE.SOURCE_SYSTEM_CD in (4,8)
    FOR READ ONLY


    2.Create the new indexes by running db2advis tool.
    3.Collect the statistics
    4.Reorg the tables if required

Posting Permissions

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