Re: Multiple column case/subselect advice

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Tue, 5 Dec 2006 17:07:29 +1100
Message-ID: <MPG.1fdfb7abb205f5ca9898b0@news.readfreenews.net>

damorgan_at_psoug.org says...
> Geoff Muldoon wrote:

> > Much neater, thanks Daniel. Any clues on further improvements,
> > particularly in the query in the WITH clause?
>
> Neat is nice ... but how does it perform and what does the EXPLAIN
> PLAN look like?

Performance is quite acceptable without being really wonderful.

Code posted so far has been pseudo-code, real code is:

with SUBQ as (

    select A.SSP_NO, A.AVAIL_KEY_NO, C.SSP_STG_CD, C.SSP_STTS_CD,

        C.EFFCT_START_DT
    from S1SSP_STU_SPK A, SSP_STTS_HIST_AVAIL B, S1SSP_STTS_HIST C     where A.SSP_STTS_CD = 'PLN'

    and B.SSP_NO = A.SSP_NO
    and B.AVAIL_KEY_NO = A.AVAIL_KEY_NO
    and C.SSP_NO = B.SSP_NO
    and C.SSP_STTS_NO = B.SSP_STTS_NO
    and B.SSP_STTS_NO = 
        (select max(D.SSP_STTS_NO) 
         from SSP_STTS_HIST_AVAIL D 
         where D.SSP_NO = A.SSP_NO 
         and D.AVAIL_KEY_NO = A.AVAIL_KEY_NO
         and D.SSP_STTS_NO < 
             (select max(E.SSP_STTS_NO) 
              from SSP_STTS_HIST_AVAIL E 
              where E.SSP_NO = A.SSP_NO 
              and E.AVAIL_KEY_NO = A.AVAIL_KEY_NO)) 

    and C.SSP_STTS_CD = 'WDE'
    )
select A.SSP_NO, A.STU_ID, A.SPK_NO, A.SPK_VER_NO, A.PARENT_SPK_NO,

    A.PARENT_SPK_VER_NO, A.PARENT_SSP_NO, A.PARENT_AVAIL_KEY, A.SPRD_CD, 
    A.AVAIL_YR, A.LOCATION_CD, 
    A.LIAB_CAT_CD, 
    nvl(A.STU_STTS_CD, (select B.STU_STTS_CD 
                        from S1SSP_STU_STTS_HST B 
                        where B.SSP_NO= A.SSP_NO 
                        and B.STU_STTS_CD is not null 
                        and B.STU_STTS_EFFCT_DT = 
                            (select max(C.STU_STTS_EFFCT_DT) 
                             from S1SSP_STU_STTS_HST C 
                             where C.SSP_NO = A.SSP_NO 
                             and C.STU_STTS_CD is not null) 
                        and B.STU_STTS_EFFCT_TM = 
                            (select max(D.STU_STTS_EFFCT_TM) 
                             from S1SSP_STU_STTS_HST D 
                             where D.SSP_NO = A.SSP_NO 
                             and D.STU_STTS_CD is not null 
                             and D.STU_STTS_EFFCT_DT = 
                                 (select max((E.STU_STTS_EFFCT_DT)) 
                                  from S1SSP_STU_STTS_HST E 
                                  where E.SSP_NO = A.SSP_NO 
                                  and E.STU_STTS_CD is not null)))) 

STU_STTS_CD,
    A.CR_VAL,
    nvl(G.SSP_STG_CD, A.SSP_STG_CD) SSP_STG_CD,     nvl(G.SSP_STTS_CD, A.SSP_STTS_CD) SSP_STTS_CD,     trunc(nvl(G.EFFCT_START_DT, A.EFFCT_START_DT) EFFCT_START_DT,     to_date('01-JAN-3000', 'dd-mon-yyyy') EFFCT_END_DATE,     A.AVAIL_KEY_NO, A.ATTNDC_MODE_CD,
    A.GRADE_CD, A.GRADE_TYPE_CD, A.RSLT_TYPE_CD, A.FOE_CD,     nvl((select 'Y'

         from S1SPK_AVAIL_DET F 
         where A.AVAIL_KEY_NO = F.AVAIL_KEY_NO 
         and sysdate between F.START_DT and F.END_DT), 'N') UNIT_ACTIVE 

from S1SSP_STU_SPK A, SUBQ G

where A.SSP_NO = B.SSP_NO(+)
and A.AVAIL_KEY_NO = B.AVAIL_KEY_NO 
and ((A.SPK_NO = 0 and A.SSP_STG_CD = 'COMP') 
     or (A.SPK_NO, A.SPK_VER_NO) in 
    (select SPK_NO, SPK_VER_NO 
     from VW_MIS_UNIT_NO_VER))

Explain plan (without numbers) is:

SELECT STATEMENT Optimizer Mode=ALL_ROWS   TABLE ACCESS BY INDEX ROWID S1.S1SSP_STU_STTS_HST

    INDEX UNIQUE SCAN S1.S1SSP_SST_IDX1	      	             	 
      SORT AGGREGATE		 	      	             	 
        TABLE ACCESS BY INDEX ROWID S1.S1SSP_STU_STTS_HST 
          INDEX RANGE SCAN S1.S1SSP_SST_IDX1  	
      SORT AGGREGATE
        TABLE ACCESS BY INDEX ROWID S1.S1SSP_STU_STTS_HST
          INDEX RANGE SCAN S1.S1SSP_SST_IDX1
            SORT AGGREGATE 	 	 	      	             	 
              TABLE ACCESS BY INDEX ROWID S1.S1SSP_STU_STTS_HST
                INDEX RANGE SCAN S1.S1SSP_SST_IDX1

  TABLE ACCESS BY INDEX ROWID S1.S1SPK_AVAIL_DET     INDEX UNIQUE SCAN S1.S1FAD1_IDX1

  FILTER		  	 	 	 	      	             	 
    NESTED LOOPS	             	 
      NESTED LOOPS	             	 
        HASH JOIN	             	 
          TABLE ACCESS FULL S1.S1SSP_STU_SPK	      	             	 
          TABLE ACCESS FULL S1.SSP_STTS_HIST_AVAIL	
        TABLE ACCESS BY INDEX ROWID S1.S1SSP_STTS_HIST
          INDEX UNIQUE SCAN S1.S1SSH_IDX1
      TABLE ACCESS BY INDEX ROWID S1.S1SSP_STU_SPK
        INDEX UNIQUE SCAN S1.S1FSS1_IDX1
    TABLE ACCESS BY INDEX ROWID S1.S1SPK_DET
      INDEX UNIQUE SCAN S1.S1FD15_IDX1
    SORT AGGREGATE
      TABLE ACCESS BY INDEX ROWID S1.SSP_STTS_HIST_AVAIL
        INDEX RANGE SCAN S1.SSP_STTS_HIST_AVAIL_PK
          SORT AGGREGATE
            TABLE ACCESS BY INDEX ROWID S1.SSP_STTS_HIST_AVAIL
              INDEX RANGE SCAN S1.SSP_STTS_HIST_AVAIL_PK

Geoff M Received on Tue Dec 05 2006 - 00:07:29 CST