Why is partition pruning not being done in the following query?

From: colin_lyse <colin_lyse_at_98fgfgs.com>
Date: 10 May 2005 13:32:04 -0500
Message-ID: <4280fdcd$0$50285$bb4e3ad8@newscene.com>

running 9.2.0.4.0 on Sun Starfire 15k Sun OS 5

tables have 36 partitions on DT_REV_MB_YYYYMM column in both tables

have the following query (note the line a.DT_REV_MB_YYYYMM in (200505) ) and it does one partition per table

SELECT
a.DT_REV_MB_YYYYMM,

CD_GL_TRAN_TYPE_DESC, 
CD_TAR_TYPE_DESC, 
CD_DEMAND_CLASS_DESC, 
TX_SUB_ACCOUNT

FROM
  tf_non_revenue_je e,
  tf_financial_audit_trail a
WHERE

  a.ky_ba = e.ky_ba(+)   AND
  a.ts_grp = e.ts_grp(+)   and
  a.DT_REV_MB_YYYYMM = e.DT_REV_MB_YYYYMM(+)

  and

  a.DT_REV_MB_YYYYMM  in (200505)
  AND  (NO_JE_SEQUENCE  =  1
  OR NO_JE_SEQUENCE  IS NULL  )

  AND CD_GL_TRAN_TYPE IN ('C')
   GROUP BY
a.DT_REV_MB_YYYYMM,

CD_GL_TRAN_TYPE_DESC, 
CD_TAR_TYPE_DESC, 
CD_DEMAND_CLASS_DESC, 
TX_SUB_ACCOUNT

plan (this one does only the 2 partitions)

SELECT STATEMENT Optimizer Mode=CHOOSE

   SORT GROUP BY

    FILTER           
      HASH JOIN OUTER                                 
         TABLE ACCESS FULL       TF_FINANCIAL_AUDIT_TRAIL       584k   63m
         TABLE ACCESS FULL       TF_NON_REVENUE_JE      269k    11m

when we want to include 2 partitions 200505 and 200501 and we just change the line to
 a.DT_REV_MB_YYYYMM in (200505, 200501)

the plan changes to doing all partitions on the 2nd table

SELECT STATEMENT Optimizer Mode=CHOOSE          15 K            19035          
                          
  SORT GROUP BY
    FILTER                               
      HASH JOIN OUTER
        PARTITION RANGE INLIST
          TABLE ACCESS FULL     TF_FINANCIAL_AUDIT_TRAIL      139k 14m   
        PARTITION RANGE ALL     
          TABLE ACCESS FULL     TF_NON_REVENUE_JE       26m  1G

why is VERY expensive and takes 2 hours verus other one takeing 7 secs.. Note the change of TF_NON_REVENUE_JE to a range all on partition. Why is the outer join causing such a problem with two dates rather than just one? Received on Tue May 10 2005 - 13:32:04 CDT