Outer Join reads all rows from underlying tables

From: Matt <mccmx_at_hotmail.com>
Date: 7 May 2004 05:57:40 -0700
Message-ID: <cfee5bcf.0405070457.44eb2f28@posting.google.com>

Hello,

I have an outer join which runs very slowly... I've traced the SQL to determine the root cause and I've noticed that the query reads 'every' row from both of the row sets returned from one of the views in the statement.

Is this to be expected with the outer-join implementation in 8i...? E.g.

The following query:

select * from ps_rr_flat_vw
where business_unit = 'TMMF'
and tm_shop_code = 'QP1'
and tm_date = '20-FEB-03'

Produces the following plan and associated row counts:

Rows Row Source Operation

-------  ---------------------------------------------------
     10  NESTED LOOPS OUTER 
     11   NESTED LOOPS 
     20    VIEW PS_TM_PEFF_V_CC_VW 
     20     SORT UNIQUE 
     19      NESTED LOOPS 
    317       NESTED LOOPS 
    317        NESTED LOOPS 
    113         NESTED LOOPS 
      2          INDEX UNIQUE SCAN (object id 14431)
    113          TABLE ACCESS BY INDEX ROWID PS_CMS_DPTDPTS 
    408           INDEX RANGE SCAN (object id 16285)
    428         TABLE ACCESS BY INDEX ROWID PS_CMS_DPTSTRU 
    428          INDEX RANGE SCAN (object id 16287)
    632        TABLE ACCESS BY INDEX ROWID PS_CMS_DPTDPTS 
    632         INDEX RANGE SCAN (object id 16285)
     19       INDEX FAST FULL SCAN (object id 14444)
     29    TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_GPQTWT 
     29     INDEX UNIQUE SCAN (object id 22061)
     10   VIEW PS_TM_PEFF_V_CCEH 
1197650    SORT GROUP BY 
2920935     NESTED LOOPS 
  74182      VIEW PS_TM_PEFF_BMRKVW2 
  74182       SORT UNIQUE 
  74181        FILTER 
  74182         HASH JOIN 
   4575          TABLE ACCESS FULL PS_CMS_DPTSTRU 
  26942          TABLE ACCESS FULL PS_TM_PEFF_BNCHMRK 
2920935      TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_GPQ 
3120306       INDEX RANGE SCAN (object id 22067)
     20      SORT AGGREGATE 
     10       FILTER 
     20        FILTER 
     10         NESTED LOOPS 
     20          MERGE JOIN CARTESIAN 
     20           NESTED LOOPS 
     20            NESTED LOOPS 
     20             INDEX UNIQUE SCAN (object id 14431)
     20             TABLE ACCESS BY INDEX ROWID PS_CMS_DPTSTRU 
     20              INDEX RANGE SCAN (object id 16288)
     20            TABLE ACCESS BY INDEX ROWID PS_CMS_DPTDPTS 
     20             INDEX RANGE SCAN (object id 16285)
     20           SORT JOIN 
     10            TABLE ACCESS BY INDEX ROWID PS_CMS_DPTDPTS 
     20             INDEX RANGE SCAN (object id 16285)
     10          INDEX RANGE SCAN (object id 14444)
     10              SORT AGGREGATE 
      5               FIRST ROW 
      5                INDEX RANGE SCAN (MIN/MAX) (object id 16285)
      4              SORT AGGREGATE 
      2               FIRST ROW 
      2                INDEX RANGE SCAN (MIN/MAX) (object id 16285)
     10        SORT AGGREGATE 
      5         FIRST ROW 
      5          INDEX RANGE SCAN (MIN/MAX) (object id 16287)
  87330         SORT AGGREGATE 
  43665          FIRST ROW 
  43665           INDEX RANGE SCAN (MIN/MAX) (object id 16287)
    632          SORT AGGREGATE 
    316           FIRST ROW 
    316            INDEX RANGE SCAN (MIN/MAX) (object id 16285)
    814            SORT AGGREGATE 
    407             FIRST ROW 
    407              INDEX RANGE SCAN (MIN/MAX) (object id 16285)
    632           SORT AGGREGATE 
    316            FIRST ROW 
    316             INDEX RANGE SCAN (MIN/MAX) (object id 16287)
----------------------------------------------------

The main concern is the row counts in these 2 operations:

  74182 VIEW PS_TM_PEFF_BMRKVW2 and  

 2920935      TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_GPQ 
 3120306       INDEX RANGE SCAN (object id 22067)

These correspond approximately to the total row counts in each of the underlying tables/views...(so its reading every row)

 select count(*) from PS_TM_PEFF_BMRKVW2 ==> 74181 and
 select count(*) from PS_TM_PEFF_GPQ ==> 3252032

Both of these operations are part of the PS_TM_PEFF_V_CCEH view which uses an outer-join, i.e.:

SELECT a.business_unit , c.tm_shop_code , a.deptid , a.production_dt, nvl(b.tm_earned_hours ,0) , a.total_work_time , c.tm_eff_start_date, c.tm_contribution , c.tm_contrib_type , c.cms_labor_type FROM
ps_tm_peff_gpqtwt a , ps_tm_peff_v_cceh b, ps_tm_peff_v_cc_vw c
WHERE
a.business_unit = b.business_unit (+)

AND a.deptid = b.deptid (+) 
AND a.production_dt = b.tm_date (+) 
AND a.business_unit = c.business_unit 
AND a.deptid = c.deptid 
AND c.tm_eff_start_date = ( 

SELECT MAX(c1.tm_eff_start_date)
FROM ps_tm_peff_v_cc_vw c1
WHERE c1.business_unit = c.business_unit

AND c1.deptid = c.deptid 
AND c1.tm_contrib_type = c.tm_contrib_type 
AND c1.cms_labor_type = c.cms_labor_type 
AND c1.tm_eff_start_date <= a.production_dt)

Anyone know why the optimizer would retrieve all rows for each row set despite having a very restrictive WHERE clause..?

Appologies for the long post.!

Matt Received on Fri May 07 2004 - 07:57:40 CDT