Why is this SQL not using indexes?

From: <johnwu_at_cnct.com>
Date: 1996/07/29
Message-ID: <4thb3h$7rg_at_mars.cnct.com>#1/1

Can anyone explain to me why follwoing SQL is not using indexes except on EMPOLYEES? ENG_SUMRY has about 8M rows and others are about 1M rows. The function is a bunch of TO_DATE, TO_CHAR, TO_NUM, ADD_MONTHS. The little 3 is the deliminators, so just ignor that. Please send a copy of respond to "johnwu_at_cnct". Thanks.

explain plan set statement_id = 'john1' for SELECT

         EP_EMP.EMP_NAME,'³',
         EP_EMP.EMP_NUM,'³',
         EP_EMP.EMP_BU,'³',
         EP_ORG.BU_DESCR,'³',

nvl(sum(decode(ES.ACTV_MTH,fn_actv_mth(nvl(199512,AD.ACTV_MTH),-1),ES.YTD_CHG_EXP,0)),0),'³',
         decode(nvl(sum(decode(ES.ACTV_MTH,
fn_actv_mth(nvl(199512,AD.ACTV_MTH),-1),ES.YTD_CHG_HRS,0)),0),0,0,round(nvl(sum(decode(ES..ACTV_MTH,
fn_actv_mth(nvl(199512,AD.ACTV_MTH),-1),ES.YTD_CHG_FEE,0)),0)/nvl(sum(decode(ES..ACTV_MTH,
fn_actv_mth(nvl(199512,AD.ACTV_MTH),-1),ES.YTD_CHG_HRS,0)),0),2)),'³',
         nvl(sum(decode(ES.ACTV_MTH,
fn_actv_mth(nvl(199512,AD.ACTV_MTH),-1),ES.YTD_CHG_HRS,0)),0),'³',
         nvl(sum(decode(ES.ACTV_MTH,
fn_actv_mth(nvl(199512,AD.ACTV_MTH),-1),ES.YTD_CHG_FEE,0)),0),'³',
         decode(nvl(sum(decode(ES.ACTV_MTH,
nvl(199512,AD.ACTV_MTH),ES.ETD_CHG_HRS,0)),0),0,0,round(nvl(sum(decode(ES..ACTV_MTH,

nvl(199512,AD.ACTV_MTH),ES.ETD_CHG_FEE,0)),0)/nvl(sum(decode(ES.ACTV_MTH, nvl(199512,AD.ACTV_MTH),ES.ETD_CHG_HRS,0)),0),2)),'³' FROM

         ORG_UNIT EP_ORG,
         ACTIVITY_DATE AD,
         EMPLOYEES EP_EMP,
         ENG_SUMRY ES,
         EMP_ROLE_ENG,
         ENG
WHERE
         (ES.ACTV_MTH in (fn_actv_mth(199512,-12),

fn_actv_mth(199512,-1), 199512,
AD.ACTV_MTH,fn_actv_mth(AD.ACTV_MTH,-1),fn_actv_mth(AD.ACTV_MTH,-12)))

    AND  (ENG.ENG_SEQNUM=ES.ENG_SEQNUM)
    AND  (ENG.ENG_SEQNUM=EMP_ROLE_ENG.ENG_SEQNUM)
    AND  (EMP_ROLE_ENG.EMP_SEQNUM=EP_EMP.EMP_SEQNUM)
    AND  (EP_EMP.ORG_SEQNUM=EP_ORG.ORG_SEQNUM)
    AND  (EP_EMP.EMP_BU =  '040')
GROUP BY
         EP_EMP.EMP_NAME,
         EP_EMP.EMP_NUM,
         EP_ORG.BU_DESCR,
         EP_EMP.EMP_BU

Received on Mon Jul 29 1996 - 00:00:00 CEST