Re: Outer Join reads all rows from underlying tables

From: Matt <mccmx_at_hotmail.com>
Date: 17 May 2004 01:16:37 -0700
Message-ID: <cfee5bcf.0405170016.65c93629@posting.google.com>

srivenu_at_hotmail.com (srivenu) wrote in message news:<1a68177.0405140307.33b57454_at_posting.google.com>...
> Matt,
> You said that the best filter is on TM_DATE column.

This is the most restrictive column but I noted that 'none' of the filter columns were being passed through to the views.

> You did not say that which table or view the TM_DATE column is coming
> from in the view ps_rr_flat_vw. Is it the tm_eff_start_date column in
> the view ps_tm_peff_v_cc_vw (which in turn comes from
> PS_TM_PEFF_CONTRIB table).

tm_date comes from the PRODUCTION_DT in PS_TM_PEFF_GPQTWT (a table).

> I dont see how this filter applies to the tables in PS_TM_PEFF_V_CCEH.
> In your plan i see this TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_GPQ,
> but in none of your view definitions, i see the table PS_TM_PEFF_GPQ.
>

Appologies, I posted the wrong version of the view definition for PS_TM_PEFF_V_CCEH: It should be... (This contains the query against PS_TM_PEFF_GPQ)

SELECT A.BUSINESS_UNIT ,A.DEPTID ,B.PRODUCTION_DT ,'A' ,SUM((B.PART_COUNT * A.BM_HOURS) /1000)
FROM
PS_TM_PEFF_BMRKVW2 A ,
PS_TM_PEFF_GPQ B
WHERE
A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND (A.DEPTID = B.DEPTID OR B.DEPTID = ' ') AND (A.SRG_CODE = B.SRG_CODE OR B.SRG_CODE = ' ')

AND (A.PART_NUM = B.PART_NUM OR B.PART_NUM= ' ') 
AND (A.PART_SFX_CD = B.PART_SFX_CD OR B.PART_SFX_CD = ' ') 
AND (A.PROCESS_CODE = B.PROCESS_CODE OR B.PROCESS_CODE = ' ') 
AND ((B.PRODUCTION_DT >= A.TM_EFFECT_DT 
AND (B.PRODUCTION_DT <= A.EFF_STOP_DATE OR A.EFF_STOP_DATE IS NULL)) 

OR B.PRODUCTION_DT IS NULL)
GROUP BY A.BUSINESS_UNIT,A.DEPTID,B.PRODUCTION_DT HTH Matt Received on Mon May 17 2004 - 03:16:37 CDT