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

colin_lyse wrote:
> 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?

http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=283897.1&p_showHeader=1&p_showHelp=1#QPERF maybe bug 3400412 (or you never know, sometimes things similar get mysteriously fixed... or broken).

jg

--
@home.com is bogus.  "One name we rejected was Grid. We thought it had
great engineering connotations, but customers felt it meant gridlock."
- Lori Sullivan, Saturn Business Planning Manager.