Can someone help me tune this query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Can someone help me tune this query

  1. #1
    Join Date
    Feb 2003
    Posts
    1

    Question Can someone help me tune this query

    I am trying to optimize the query below and I am having no luck. Currently, the query runs for 2 mins. 23 secs. as written. I noticed that the query is being slowed down because when looking for c.COMP_NM (c is table alias for COMPONENT) that table has 1,435,820 records. The OR Statement at the bottom of the query is slowing it down, but that OR statement is needed. This query is supposed to find all records that have cu_phases, components, or component assemblies with the name of COMPLETION REPORTS. Can someone tell me how to optimize this query?

    select /*+ Rule */
    ' ' INDICATOR,
    c.SHIP_SYS_ID,
    c.SHIP_TYPE_CD,
    c.HULL_NUM_ID,
    c.PLANT_NUM_ID,
    c.NUC_FLAG_CD COMP_NUC_FLAG_CD,
    cp.STD_PHASE_CD,
    cp.PHASE_REPEAT_QY,
    cp.CU_PHASE_SA_ID,
    cp.PROJ_ID,
    cp.ICN,
    cp.KO,
    cp.TITLE_TX,
    cp.TITLE_TX REMARKS_TX_ALIAS,
    cp.COMP_NM,
    cp.COMP_TYPE_CD,
    cp.VERSION_ID CP_VERSION_ID,
    cp.CU_SA_ID,
    cph.APPROVAL_STATUS_CD,
    cph.WORKING_STATUS_CD,
    cph.STATUS_CHANGE_DT,
    js.SWLIN_SYS_ID||js.FUND_ACT_CD||js.SWLIN_SERIAL_ID||js.SSI_SERIAL_ID JobSummaryID,
    js.SWLIN_SYS_ID||js.FUND_ACT_CD||js.SWLIN_SERIAL_ID SWLIN,
    js.JOB_SUMM_SA_ID,
    DECODE(ASSIGNED_USER_SA_ID, null, '', p.LAST_NM||', '||p.FIRST_NM||' '||p.MIDDLE_NM) ASSIGNED_TO,
    i.INSTR_TYPE_CD,
    i.CHANGE_ID,
    i.ORIGINATOR_ORG_CD,
    i.ORG_EXTENSION_CD,
    i.ORIGINATOR_ORG_CD || '.' || i.ORG_EXTENSION_CD ORG_CODE,
    i.TGI_STATUS_CD,
    i.TGI_STATUS_CD ORIG_TGI_STATUS_CD,
    i.NUC_FLAG_CD,
    i.NOFORN_FLAG_CD,
    i.INSTR_SA_ID,
    DECODE(i.INSTR_COPY_CD, 'E', 'F', i.INSTR_COPY_CD) INSTR_COPY_CD,
    nnd.NET_NODE_DT - (pr.TIME_FACTOR_QY * 7) PLANNED_PACKAGE_DATE,
    DECODE(i.last_mod_dt,null,DECODE(i.instr_copy_cd,'S','S','H','H','E','F',' '),'F') DISPLAY_INSTR_COPY_CD,
    i.DUE_DT,
    RPAD(NVL(i.PARA_TYPE_CD, ' '), 2, ' ') PARA_TYPE_CD,
    i.ASSIGNED_USER_SA_ID,
    i.VERSION_ID INSTR_VERSION_ID,
    i.CHECKOUT_USER_SA_ID,
    i.CHECKOUT_DT,
    i.PREV_CHECKOUT_USER_SA_ID,
    i.PREV_CHECKOUT_DT,
    i.LAST_MOD_DT,
    i.LAST_MOD_USER_SA_ID,
    i.TGI_LOCAL_FILE_NM,
    i.RELEASED_TGI_CHG_CD,
    i.SHAPEC_TGI_CD,
    tih.CURRENT_DT,
    tih.CREATE_DT,
    tih.REMARKS_TX,
    tih.ITEM_SA_ID,
    tih.MOD_USER_SA_ID,
    tih.ITEM_STATUS_CD,
    tih.CURRENT_FLAG_CD,
    c.SHIP_SYS_ID||' '||c.COMP_NM||' '|| c.COMP_TYPE_CD||' '|| cp.STD_PHASE_CD||' '|| cp.PHASE_REPEAT_QY TI_TITLE_TX,
    s.SHIP_NM SHIP
    from
    PERSONNEL p,
    COMPONENT c,
    NET_NODE_DATE nnd,
    PROJECT pr,
    TRACKING_ITEM ti,
    TRACKING_ITEM_HIST tih,
    JOB_SUMMARY js,
    JS_CU_PHASE jscp,
    CU_PHASE_HIST cph,
    INSTRUCTION i,
    CU_PHASE cp,
    SHIP s
    where
    i.CU_PHASE_SA_ID = cp.CU_PHASE_SA_ID
    and i.ASSIGNED_USER_SA_ID = p.USER_SA_ID(+)
    and i.INSTR_SA_ID = TO_NUMBER(ti.ITEM_ID)
    and cp.CU_SA_ID = c.COMP_SA_ID
    and cp.CU_PHASE_SA_ID = jscp.CU_PHASE_SA_ID
    and cp.CU_PHASE_SA_ID = cph.CU_PHASE_SA_ID
    and cp.CU_PHASE_SA_ID = nnd.NET_NODE_ID(+)
    and cp.PROJ_ID = pr.PROJ_ID
    and ti.PROJ_ID = cp.PROJ_ID
    and pr.DEL_FLAG_CD <> 'Y'
    and pr.SHIP_TYPE_CD = s.SHIP_TYPE_CD
    and pr.HULL_NUM_ID = s.HULL_NUM_ID
    and ti.ITEM_SA_ID = tih.ITEM_SA_ID
    and jscp.JOB_SUMM_SA_ID = js.JOB_SUMM_SA_ID
    and cp.ICN = js.ICN
    and cph.CURRENT_FLAG_CD = 'Y'
    and nnd.DATE_TYPE_CD(+) = 'STS'
    and tih.CURRENT_FLAG_CD = 'Y'
    and ti.ITEM_TYPE_CD = 'TGI'
    and cp.COMP_NM = 'COMPLETION REPORTS'
    and c.NUC_FLAG_CD = 'N'
    and i.CHANGE_ID = (
    select
    MAX(i2.CHANGE_ID)
    from
    instruction i2
    where
    i2.CU_PHASE_SA_ID = cp.CU_PHASE_SA_ID
    )
    union
    select
    ' ' INDICATOR,
    null SHIP_SYS_ID,
    c.SHIP_TYPE_CD,
    c.HULL_NUM_ID,
    0 PLANT_NUM_ID,
    ca.NUC_FLAG_CD COMP_NUC_FLAG_CD,
    cp.STD_PHASE_CD,
    cp.PHASE_REPEAT_QY,
    cp.CU_PHASE_SA_ID,
    cp.PROJ_ID,
    cp.ICN,
    cp.KO,
    cp.TITLE_TX,
    cp.TITLE_TX REMARKS_TX_ALIAS,
    cp.COMP_NM,
    cp.COMP_TYPE_CD,
    cp.VERSION_ID CP_VERSION_ID,
    cp.CU_SA_ID,
    cph.APPROVAL_STATUS_CD,
    cph.WORKING_STATUS_CD,
    cph.STATUS_CHANGE_DT,
    js.SWLIN_SYS_ID||js.FUND_ACT_CD||js.SWLIN_SERIAL_ID||js.SSI_SERIAL_ID JobSummaryID,
    js.SWLIN_SYS_ID||js.FUND_ACT_CD||js.SWLIN_SERIAL_ID SWLIN,
    js.JOB_SUMM_SA_ID,
    DECODE(ASSIGNED_USER_SA_ID, null, '', p.LAST_NM||', '||p.FIRST_NM||' '||p.MIDDLE_NM) ASSIGNED_TO,
    i.INSTR_TYPE_CD,
    i.CHANGE_ID,
    i.ORIGINATOR_ORG_CD,
    i.ORG_EXTENSION_CD,
    i.ORIGINATOR_ORG_CD || '.' || i.ORG_EXTENSION_CD ORG_CODE,
    i.TGI_STATUS_CD,
    i.TGI_STATUS_CD ORIG_TGI_STATUS_CD,
    i.NUC_FLAG_CD,
    i.NOFORN_FLAG_CD,
    i.INSTR_SA_ID,
    DECODE(i.INSTR_COPY_CD, 'E', 'F', i.INSTR_COPY_CD) INSTR_COPY_CD,
    nnd.NET_NODE_DT - (pr.TIME_FACTOR_QY * 7) PLANNED_PACKAGE_DATE,
    DECODE(i.last_mod_dt,null,DECODE(i.instr_copy_cd,'S','S','H','H','E','F',' '),'F') DISPLAY_INSTR_COPY_CD,
    i.DUE_DT,
    RPAD(NVL(i.PARA_TYPE_CD, ' '), 2, ' ') PARA_TYPE_CD,
    i.ASSIGNED_USER_SA_ID,
    i.VERSION_ID INSTR_VERSION_ID,
    i.CHECKOUT_USER_SA_ID,
    i.CHECKOUT_DT,
    i.PREV_CHECKOUT_USER_SA_ID,
    i.PREV_CHECKOUT_DT,
    i.LAST_MOD_DT,
    i.LAST_MOD_USER_SA_ID,
    i.TGI_LOCAL_FILE_NM,
    i.RELEASED_TGI_CHG_CD,
    i.SHAPEC_TGI_CD,
    tih.CURRENT_DT,
    tih.CREATE_DT,
    tih.REMARKS_TX,
    tih.ITEM_SA_ID,
    tih.MOD_USER_SA_ID,
    tih.ITEM_STATUS_CD,
    tih.CURRENT_FLAG_CD,
    null ||ca.COMP_ASSY_NM||' '|| ca.COMP_TYPE_CD||' '|| cp.STD_PHASE_CD||' '|| cp.PHASE_REPEAT_QY TI_TITLE_TX,
    s.SHIP_NM SHIP
    from
    PERSONNEL p,
    COMPONENT c,
    COMPONENT_ASSY ca,
    GROUPING_COMP gc,
    NET_NODE_DATE nnd,
    PROJECT pr,
    TRACKING_ITEM ti,
    TRACKING_ITEM_HIST tih,
    JOB_SUMMARY js,
    JS_CU_PHASE jscp,
    CU_PHASE_HIST cph,
    INSTRUCTION i,
    CU_PHASE cp,
    SHIP s
    where
    i.CU_PHASE_SA_ID = cp.CU_PHASE_SA_ID
    and i.ASSIGNED_USER_SA_ID = p.USER_SA_ID(+)
    and i.INSTR_SA_ID = TO_NUMBER(ti.ITEM_ID)
    and cp.CU_SA_ID = ca.COMP_ASSY_SA_ID
    and gc.COMP_SA_ID = c.COMP_SA_ID
    and gc.GROUPING_SA_ID = ca.GROUPING_SA_ID
    and cp.CU_PHASE_SA_ID = jscp.CU_PHASE_SA_ID
    and cp.CU_PHASE_SA_ID = cph.CU_PHASE_SA_ID
    and cp.CU_PHASE_SA_ID = nnd.NET_NODE_ID(+)
    and cp.PROJ_ID = pr.PROJ_ID
    and ti.PROJ_ID = cp.PROJ_ID
    and pr.DEL_FLAG_CD <> 'Y'
    and pr.SHIP_TYPE_CD = s.SHIP_TYPE_CD
    and pr.HULL_NUM_ID = s.HULL_NUM_ID
    and ti.ITEM_SA_ID = tih.ITEM_SA_ID
    and jscp.JOB_SUMM_SA_ID = js.JOB_SUMM_SA_ID
    and cp.ICN = js.ICN
    and cph.CURRENT_FLAG_CD = 'Y'
    and nnd.DATE_TYPE_CD(+) = 'STS'
    and tih.CURRENT_FLAG_CD = 'Y'
    and ti.ITEM_TYPE_CD = 'TGI'
    and (c.NUC_FLAG_CD = 'N' or ca.NUC_FLAG_CD = 'N')
    and i.CHANGE_ID = (
    select
    MAX(i2.CHANGE_ID)
    from
    instruction i2
    where
    i2.CU_PHASE_SA_ID = cp.CU_PHASE_SA_ID
    )
    and (ca.COMP_ASSY_NM = 'COMPLETION REPORTS'
    or c.COMP_NM = 'COMPLETION REPORTS')
    order by
    1
    ,
    COMP_TYPE_CD asc,
    STD_PHASE_CD asc,
    PHASE_REPEAT_QY asc,
    CHANGE_ID asc

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Query plan?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    "The OR Statement at the bottom of the query is slowing it down, but that OR statement is needed" sounds very likely - OR's that involve different tables are hard to optimize. One thing to try is to re-write it using a UNION instead.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

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



Click Here to Expand Forum to Full Width