select count(*) from CLM_APPLC_DTL = 3621394
select count(*) from CLM_APPLC_DTL_ADT = 4373738
select count(*) from COM_PARAM_SYSTEM_M = 1503
select count(*) from PRD_KEY_INFO = 70
select count(*) from PRD_EVENT_MAP = 809 
select count(*) from PRD_EVENT_BEN_MAP = 1364

statistic method for table dbms_stats
statistic method for index  dbms_stats

select table_name,index_name,column_position from user_ind_columns where TABLE_NAME IN('CLM_APPLC_DTL_ADT',
'COM_PARAM_SYSTEM_M',
'PRD_KEY_INFO',
'PRD_EVENT_MAP', 
'PRD_EVENT_BEN_MAP') order by table_name,index_name,column_position

CLM_APPLC_DTL_ADT	CADA_STAGEDATAID_INDX	1
CLM_APPLC_DTL_ADT	DM_CLM_APPLC_DTL_ADT	1
CLM_APPLC_DTL_ADT	DM_CLM_APPLC_DTL_ADT	2
CLM_APPLC_DTL_ADT	INDX_CLM_ADT	1
CLM_APPLC_DTL_ADT	INDX_CLM_ADT	2
CLM_APPLC_DTL_ADT	SYS_C006151	1
COM_PARAM_SYSTEM_M	CPSM_STRPARAM1_IDX	1
COM_PARAM_SYSTEM_M	XPKCOM_PARAM_SYSTEM_M	1
COM_PARAM_SYSTEM_M	XPKCOM_PARAM_SYSTEM_M	2
PRD_EVENT_BEN_MAP	INDX_FOLLOWUPNIGHT_IDX	1
PRD_EVENT_BEN_MAP	PRD_EVENTMAP_LEVENTMAPSEQ	1
PRD_EVENT_BEN_MAP	PRD_EVENTMAP_NBENTYPE	1
PRD_EVENT_BEN_MAP	XPKPRD_EVENT_BEN_MAP	1
PRD_EVENT_MAP	        INDX_PEM_PRDVEREVTSAC	1
PRD_EVENT_MAP	        INDX_PEM_PRDVEREVTSAC	2
PRD_EVENT_MAP	        INDX_PEM_PRDVEREVTSAC	3
PRD_EVENT_MAP	        INDX_PEM_PRDVEREVTSAC	4
PRD_EVENT_MAP	        XPKPRD_EVENT_MAP	1
PRD_KEY_INFO	        PKI_PROD_CURRCD_NMKTSEG	1
PRD_KEY_INFO	        PKI_PROD_CURRCD_NMKTSEG	2
PRD_KEY_INFO	        PKI_PROD_CURRCD_NMKTSEG	3
PRD_KEY_INFO	        PKI_PROD_CURRCD_NMKTSEG	4
PRD_KEY_INFO	        PKI_PROD_CURRCD_NMKTSEG	5
PRD_KEY_INFO	        PKI_PROD_NMKTSEGMENT	1
PRD_KEY_INFO	        PKI_PROD_NMKTSEGMENT	2
PRD_KEY_INFO	        PKI_PROD_NMKTSEGMENT	3
PRD_KEY_INFO	        PKI_PROD_NMKTSEGMENT	4
PRD_KEY_INFO	        XPKPRD_KEY_INFO	1
PRD_KEY_INFO	        XPKPRD_KEY_INFO	2


[b]Query[/b]
 

with t1  as (

    select cbm.STRBRANDCD,

       'Same' STRBRANDNAME,

       DECODE(cpsm.nparamcd,17,1,14,2,13,3,15,4,12,5) sort_order,

          cpsm.STRCDDESC stat_desc,

          case when cad.nbentype is null then

          0 else

          cad.nbentype end  nbentype,

          nvl(cben.STRCDDESC, 'Unknown Benefit') ben_des,

          cad.NCLAIMSTATCD,

          case when (cben.NPARAMCD =  cad.NBENTYPE ) or (cad.nbentype is null) then 

           case when (cpsm.NPARAMCD = cad.NCLAIMSTATCD ) then 

           cad.STRCLAIMNBR

           else null

          end

            else Null

        end        STRCLAIMNBR

            

from 

(SELECT DISTINCT strclaimnbr ,NCLAIMSTATCD ,dtcreated,NBENTYPE, nclaimtype, strpolnbr

      FROM

      ( SELECT strclaimnbr  ,NCLAIMSTATCD ,dtcreated,NBENTYPE, nclaimtype, strpolnbr, 'hist'

        FROM  clm_applc_dtl_adt cada 

      WHERE  cada.NCLAIMSTATCD IN (17,14,13,15,12)

        AND  strcreatedby = 'ACT'

      UNION ALL

      SELECT strclaimnbr  ,NCLAIMSTATCD , dtcreated,NBENTYPE,nclaimtype, strpolnbr, 'curr'

      FROM  clm_applc_dtl cad 

      WHERE cad.NCLAIMSTATCD IN (17,14,13,15,12)   

      AND strcreatedby = 'ACT'

      ) 

      )  cad,

       CLM_PRODUCT_BEN cpb,

       PRD_KEY_INFO pki,

      com_policy_m cpm,

        COM_BRAND_M cbm,

       (select NPARAMCD, STRCDDESC

        from COM_PARAM_SYSTEM_M

        where IPARAMTYPECD =  3502 

      and   STRCDDESC like 'ACT%' 

       AND nparamcd <> 16  )cpsm,

       ( select a.NPARAMCD, a.STRCDDESC

       from  COM_PARAM_SYSTEM_M A

       where A.IPARAMTYPECD =   1022  )cben

where 

 trunc(cad.dtcreated) between '01-JAN-89' and '30-JAN-89'

and  CAD.STRCLAIMNBR  = CPB.STRCLAIMNBR

and  cad.NCLAIMTYPE   = cpb.NCLAIMTYPE

and cad.strpolnbr = cpm.strpolnbr

and  cpb.STRPRODCD   = pki.STRPRODCD 

and  cpb.NPRODVER     = pki.NPRODVER 

and  cpm.STRBRANDCD  = CBm.STRBRANDCD

and cbm.strbrandcd = 'LHF'

and cad.NCLAIMSTATCD(+) = cpsm.NPARAMCD

and cad.NBENTYPE  = cben.NPARAMCD(+)

and cad.nbentype is not null

UNION ALL

select cbm.STRBRANDCD,

      'Same' STRBRANDNAME,

      DECODE(cpsm.nparamcd,17,1,14,2,13,3,15,4,12,5) sort_order,

         cpsm.STRCDDESC stat_desc,

         case when cad.nbentype is null then

         0 else

         cad.nbentype end  nbentype,

         nvl(cben.STRCDDESC, 'Unknown Benefit') ben_des,

         cad.NCLAIMSTATCD,

         case when (cben.NPARAMCD =  cad.NBENTYPE ) or (cad.nbentype is null) then 

           case when (cpsm.NPARAMCD = cad.NCLAIMSTATCD ) then 

                cad.STRCLAIMNBR

           else null

           end

               else Null

        end        STRCLAIMNBR      

from 

(SELECT DISTINCT strclaimnbr ,NCLAIMSTATCD ,dtcreated,NBENTYPE, nclaimtype, strpolnbr, strclaimon, nsaccd

      FROM

                                    ( SELECT strclaimnbr  ,NCLAIMSTATCD ,dtcreated,NBENTYPE, nclaimtype, strpolnbr, strclaimon, nsaccd ,'hist'

                                          FROM  clm_applc_dtl_adt cada 

                                          WHERE  cada.NCLAIMSTATCD IN (17,14,13,15,12)

                                          AND  strcreatedby = 'ACT'

                                                                                                                AND nclaimtype = 58

                                                                                                                  UNION ALL

                                          SELECT strclaimnbr  ,NCLAIMSTATCD , dtcreated,NBENTYPE,nclaimtype, strpolnbr,  strclaimon, nsaccd, 'curr'

                                          FROM  clm_applc_dtl cad 

                                          WHERE cad.NCLAIMSTATCD IN (17,14,13,15,12)   

                                          AND strcreatedby = 'ACT'

                                                                                                                AND nclaimtype = 58

                                                                                                               

                              ) 

                        )  cad,

(select strpolnbr, strprodcd, nprodver from   com_pol_prod_dtl where nsaccd = 1 )  cppd,

    ---     clm_product_ben cpb,

        PRD_KEY_INFO pki,

       com_policy_m cpm,

        COM_BRAND_M cbm,

       (select NPARAMCD, STRCDDESC

        from COM_PARAM_SYSTEM_M

        where IPARAMTYPECD =  3502 

      and   STRCDDESC like 'ACT%' 

       AND nparamcd <> 16  )cpsm,

       ( select a.NPARAMCD, a.STRCDDESC

       from  COM_PARAM_SYSTEM_M A

       where A.IPARAMTYPECD =   1022  )cben

where 

 trunc(cad.dtcreated) between '01-JAN-89' and '30-JAN-89'

----- and  CAD.STRCLAIMNBR  = CPB.STRCLAIMNBR

----- and  cad.NCLAIMTYPE   = cpb.NCLAIMTYPE

and cad.strpolnbr = cppd.strpolnbr 

and cad.strpolnbr = cpm.strpolnbr

and cppd.strprodcd = pki.strprodcd

and cppd.nprodver = pki.nprodver 

and  cpm.STRBRANDCD  = CBm.STRBRANDCD

and cbm.strbrandcd = 'LHF'

and cad.NCLAIMSTATCD(+) = cpsm.NPARAMCD

and cad.NBENTYPE  = cben.NPARAMCD(+)

and cad.nbentype is null),

 t2 as  (select strbrandcd strbrandcd, strbrandname strbrandname,DECODE(yy.nparamcd,17,1,14,2,13,3,15,4,12,5) sort_order, yy.strcddesc Stat_desc , nbentype,  ben_des, null claimstatcd, null strclaimnbr

from

(select pki.strbrandcd strbrandcd, 'Same' strbrandname, 0 sort_order, Null Stat_desc , pebm.nbentype nbentype, om_Get_param_desc_fnc(1022, pebm.nbentype) ben_des, null claimstatcd, null strclaimnbr

from prd_key_info pki,

     prd_event_map pem,

       prd_event_ben_map pebm,

       com_brand_m cbm

       where pki.strprodcd = pem.strprodcd

       and pki.nprodver = pem.nprodver

      and pki.strbrandcd = cbm.strbrandcd

      and cbm.strbrandcd = 'LHF'

    and pebm.nbentype not in (26, 70)

      and pem.leventmapseq = pebm.leventmapseq 

      group by pki.strbrandcd, cbm.strbrandname, pebm.nbentype

union all

select distinct pki.strbrandcd strbrandcd, 'Same' strbrandname, 0 sort_order, Null stat_desc, 0 nbentype, 'Unknown Benefit' ben_desc,  null claimstatcd, null strclaimnbr from 

       prd_key_info pki,

     prd_event_map pem,

       prd_event_ben_map pebm,

       com_brand_m cbm

       where pki.strprodcd = pem.strprodcd

       and pki.nprodver = pem.nprodver

      and pki.strbrandcd = cbm.strbrandcd

      and cbm.strbrandcd = 'LHF'

      and pebm.nbentype not in (26, 70)

    and pem.leventmapseq = pebm.leventmapseq 

) y,

    ( select NPARAMCD, STRCDDESC

        from COM_PARAM_SYSTEM_M

        where IPARAMTYPECD =  3502 

      and  STRCDDESC like 'ACT%' 

     AND nparamcd <> 16) yy)    

   select distinct nvl(t1.strbrandcd, t2.strbrandcd) strbrandcd, nvl(t1.strbrandname, t2.strbrandname) strbrandname , nvl(t1.sort_order, t2.sort_order) sort_order, nvl(t1.stat_desc, t2.stat_desc) stat_desc, '  '||nvl(t1.ben_des, t2.ben_des) ben_des, t1.nclaimstatcd, nclaimstatcd, t1.strclaimnbr strclaimnbr  from t1 full outer join t2

on t1.nbentype = t2.nbentype

and t1.stat_desc = t2.stat_desc

order by sort_order


[b]Plan and Statistics[/b]

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=20 Bytes=453
          00)

   1    3     RECURSIVE EXECUTION OF 'SYS_LE_3_0'
   2    3     RECURSIVE EXECUTION OF 'SYS_LE_3_1'
   3    0   TEMP TABLE TRANSFORMATION*                                 :Q245750
                                                                       05

   4    3     SORT* (UNIQUE) (Cost=10 Card=20 Bytes=45300)             :Q245750
                                                                       05

   5    4       VIEW* (Cost=6 Card=20 Bytes=45300)                     :Q245750
                                                                       04

   6    5         UNION-ALL*                                           :Q245750
                                                                       04

   7    6           HASH JOIN* (OUTER) (Cost=3 Card=2 Bytes=4556)      :Q245750
                                                                       04

   8    7             VIEW* (Cost=1 Card=2 Bytes=362)                  :Q245750
                                                                       02

   9    8               TABLE ACCESS* (FULL) OF 'SYS_TEMP_0FD9D6619_52 :Q245750
          18C556' (Cost=1 Card=2 Bytes=274)                            02

  10    7             VIEW* (Cost=2 Card=18 Bytes=37746)               :Q245750
                                                                       00

  11   10               TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D661A_521
          8C556' (Cost=2 Card=18 Bytes=37188)

  12    6           HASH JOIN* (ANTI) (Cost=3 Card=18 Bytes=39132)     :Q245750
                                                                       04

  13   12             VIEW* (Cost=2 Card=18 Bytes=37746)               :Q245750
                                                                       01

  14   13               TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D661A_521
          8C556' (Cost=2 Card=18 Bytes=37188)

  15   12             VIEW* (Cost=1 Card=2 Bytes=154)                  :Q245750
                                                                       03

  16   15               TABLE ACCESS* (FULL) OF 'SYS_TEMP_0FD9D6619_52 :Q245750
          18C556' (Cost=1 Card=2 Bytes=274)                            03



   3 PARALLEL_COMBINED_WITH_CHILD
   4 PARALLEL_TO_SERIAL            SELECT DISTINCT C0 C0,C1 C1,C2 C2,C3 C3,C4 C
                                   4,C5 C5,C5 C6,C7 C7 FROM :Q24575004

   5 PARALLEL_TO_PARALLEL          SELECT NVL(C2,C9) C0,NVL(C1,C8) C1,NVL(C0,C7
                                   ) C2,NVL(C3,C10) C3,'  '||NVL(C4,C11

   6 PARALLEL_COMBINED_WITH_PARENT
   7 PARALLEL_COMBINED_WITH_PARENT
   8 PARALLEL_TO_PARALLEL          SELECT C3 C0,C4 C1,C0 C2,C1 C3,C2 C4,C7 C5,C
                                   6 C6,C5 C7 FROM (SELECT /*+ NO_EXPAN

   9 PARALLEL_COMBINED_WITH_PARENT
  10 PARALLEL_FROM_SERIAL
  12 PARALLEL_COMBINED_WITH_PARENT
  13 PARALLEL_FROM_SERIAL
  15 PARALLEL_TO_PARALLEL          SELECT C4 C0,C3 C1 FROM (SELECT /*+ NO_EXPAN
                                   D ROWID(A1) */ A1."C0" C0,A1."C1" C1

  16 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
        505  recursive calls
         20  db block gets
     531575  consistent gets
     531052  physical reads
       3984  redo size
       4007  bytes sent via SQL*Net to client
       5658  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
        125  rows processed

DEV13>