날씨만큼 기운이 없는 아침입니다. ㅋ

개발DB를 설치하고 쿼리를 돌리는데 이상한 현상이 발생하고 있습니다.

운영DB와 동일하게 환경을 세팅하였구요. 다만 운영DB가 서버사양은 조금 좋습니다.

현상은...

맨 처음 select 절을 제외하고 돌리면 결과가 1초이내에 나오는데, 포함해서 돌리면 2분이 넘어갑니다.

단지 전체리스트에서 한페이지의 목록수만큼 자르는 작업인데 말이죠.(운영DB에서는 잘 돕니다.)

플랜을 비교해 보니 아래와 같이 운영DB에서는 두단계를 더 거칩니다.(굵은 글씨 31번, 52번)

혹시 이런 경험해보신 분 있으시면 답변 부탁합니다.

 

[쿼리]

SELECT *
FROM (  

 

 

        SELECT ROWNUM AS sys_capsulation_innerfrom_rn, sys_capsulation_innerfrom.* 
        FROM ( 
SELECT
    SBJCT_ID, RCPT_DATE, RCPT_CNCL_DATE, KK, KK1, OFFCL_SBJCT_NM,
    GROSS_DVLP_SDATE, GROSS_DVLP_EDATE, AVAIL_EDATE, GNRL_SBJCT_ID,
    RECP_BUSI_ID, MNGR_MBR_ID, CHRGR_MBR_ID, MAIN_DEPT_ID, ANCM_ID,
    BCLSF_CD, RANK, SBJCT_CLSF, RCV_STATUS_CLSF,IS_CHONG_PRINT, CONTACT_PNT,
    FDUTY_CD, REG_MBR_ID, CHRGR_BUSI_ID,
    SBJCT_NOW_STEP, SBJCT_NOW_DEGREE, AGRE_DATE, LAST_HSTR_NUM, ISEDIT, ISEDIT3,
    SBJCT_STEP, ORDR, RCV_STATUS_CLSF_NM,
    CASE WHEN TO_CASH_MNY = WI_CASH_MNY AND TO_ARTCL = WI_ARTCL THEN 1 ELSE 0 END IS_EDIT2,
    (SELECT COUNT(*) FROM RSUBJECT_TBI_SRVY WHERE SBJCT_ID = K.SBJCT_ID) CNT
FROM(
    SELECT /*+ INDEX(CX RSBJCT_BFUND_PRGS_PK) */
        SUM(CASE WHEN CX.IS_CMMT = 2 AND CX.BITEM_ALLOT_CD LIKE 'R__00' THEN CX.CASH_MNY ELSE 0 END) TO_CASH_MNY,
        SUM(CASE WHEN CX.IS_CMMT = 2 AND CX.BITEM_ALLOT_CD LIKE 'R__00' THEN CX.ARTCL ELSE 0 END) TO_ARTCL,
        SUM(DECODE(CX.BITEM_ALLOT_CD, 'R1400', CX.CASH_MNY, 0)) WI_CASH_MNY,
        SUM(DECODE(CX.BITEM_ALLOT_CD, 'R1400', CX.ARTCL, 0)) WI_ARTCL,
        CY.SBJCT_ID, CY.RCPT_DATE, CY.RCPT_CNCL_DATE, CY.KK, CY.KK1, CY.OFFCL_SBJCT_NM,
        CY.GROSS_DVLP_SDATE, CY.GROSS_DVLP_EDATE, CY.AVAIL_EDATE, CY.GNRL_SBJCT_ID,
        CY.RECP_BUSI_ID, CY.MNGR_MBR_ID, CY.CHRGR_MBR_ID, CY.MAIN_DEPT_ID, CY.ANCM_ID,
        CY.BCLSF_CD, CY.RANK, CY.SBJCT_CLSF, CY.RCV_STATUS_CLSF, CY.IS_CHONG_PRINT, CY.CONTACT_PNT,
        CY.FDUTY_CD, CY.REG_MBR_ID, CY.CHRGR_BUSI_ID,
        CY.SBJCT_NOW_STEP, CY.SBJCT_NOW_DEGREE, CY.AGRE_DATE, CY.LAST_HSTR_NUM, CY.ISEDIT, CY.ISEDIT3,
        CY.SBJCT_STEP, CY.ORDR, CY.RCV_STATUS_CLSF_NM
    FROM (
        SELECT /*+ ORDERED INDEX(SUB RSUBJECT_IDX2) */
            SUB.SBJCT_ID, SUB.RCPT_DATE, SUB.RCPT_CNCL_DATE, P1.AVAIL_SDATE,
            DECODE(SUB.GNRL_SBJCT_ID, NULL, SUB.SBJCT_ID, SUB.GNRL_SBJCT_ID) KK,
            DECODE(SUB.SBJCT_CLSF, '1002', 1, 2) KK1,
            --DECODE(SUB.SBJCT_CLSF, '1003', '' || SUB.OFFCL_SBJCT_NM, SUB.OFFCL_SBJCT_NM) OFFCL_SBJCT_NM,
            (DECODE(SUB.SBJCT_CLSF, '1002', '[총괄]', '1003', '[세부]', '') || SUB.OFFCL_SBJCT_NM) OFFCL_SBJCT_NM,
            SUB.GROSS_DVLP_SDATE, SUB.GROSS_DVLP_EDATE, SUB.AVAIL_EDATE, SUB.GNRL_SBJCT_ID,
            SUB.RECP_BUSI_ID, SUB.MNGR_MBR_ID, SUB.CHRGR_MBR_ID, SUB.MAIN_DEPT_ID, SUB.ANCM_ID,
            SUB.BCLSF_CD, SUB.RANK, SUB.SBJCT_CLSF, SUB.RCV_STATUS_CLSF,
      (CASE WHEN SUB.SBJCT_CLSF = '1002'
                    THEN (SELECT /*+ index_asc(isub rsubject_pk) */ RCV_STATUS_CLSF
                       FROM RSUBJECT ISUB
                     WHERE   ISUB.gnrl_SBJCT_ID = SUB.SBJCT_ID
                     AND iSUB.SBJCT_CLSF = '1003'
                     AND ISUB.RCV_STATUS_CLSF = (SELECT MIN(RCV_STATUS_CLSF) FROM RSUBJECT WHERE GNRL_SBJCT_ID = ISUB.GNRL_SBJCT_ID) -- 20081120
                     AND ROWNUM = 1
                      )
          ELSE  SUB.RCV_STATUS_CLSF
          END ) IS_CHONG_PRINT,
      SUB.CONTACT_PNT,
            SUB.FDUTY_CD, SUB.REG_MBR_ID, SUB.CHRGR_BUSI_ID,
            SUB.SBJCT_NOW_STEP, SUB.SBJCT_NOW_DEGREE, SUB.AGRE_DATE, SUB.LAST_HSTR_NUM,
            (SELECT CODE_NAME FROM RCODE WHERE MAIN_CODE = '7097'
                AND SUB_CODE = DECODE(P1.DUTY_CD, '2220', SUB.RCV_STATUS_CLSF, '07')) RCV_STATUS_CLSF_NM,
            (SELECT RCPT_EDATE FROM RANCM_BUSI WHERE ANCM_ID = SUB.ANCM_ID AND BCLSF_CD = SUB.BCLSF_CD) EDATE,
            (CASE WHEN
                (SELECT RCPT_EDATE FROM RANCM_BUSI
                    WHERE ANCM_ID = SUB.ANCM_ID
                        AND BCLSF_CD = SUB.BCLSF_CD) >= TO_CHAR(SYSDATE, 'YYYYMMDD')
                        AND P1.DUTY_CD = '2220'
                        --AND SUB.CHRGR_MBR_ID = ''
                    THEN '1'
                ELSE '2'
            END) ISEDIT, -- 총괄책임자인 경우 세부과제포함하여 상태구분, 비고, 참고를 나타내기 위한 필드
            (CASE WHEN
                (SELECT RCPT_EDATE FROM RANCM_BUSI
                    WHERE ANCM_ID = SUB.ANCM_ID
                        AND BCLSF_CD = SUB.BCLSF_CD) >= TO_CHAR(SYSDATE, 'YYYYMMDD')
                        AND P1.DUTY_CD = '2220'
                        AND SUB.CHRGR_MBR_ID = /*[#1=10013971]*/ '10013971'
                    THEN '1'
                ELSE '2'
            END) ISEDIT3,
            P1.SBJCT_STEP , P1.ORDR
        FROM RSUBJECT SUB, RSBJCT_PRGS P1
        WHERE SUB.GNRL_SBJCT_ID IN (
            SELECT /*+ INDEX(S RSUBJECT_IDX3) INDEX(P RSBJCT_PRGS_PK) */
                S.SBJCT_ID
            FROM RSUBJECT S, RSBJCT_PRGS P
            WHERE
                S.AVAIL_EDATE='99991231'
                AND S.RCPT_CNCL_DATE='99991231'
                --AND S.CHRGR_MBR_ID = ''
                AND s.chrgr_mbr_id IN (SELECT s1.chrgr_mbr_id FROM RSUBJECT s1
                    WHERE s1.gnrl_sbjct_id = s.sbjct_id --and s.sbjct_clsf = '1002'
                    AND s.chrgr_mbr_id = /*[#2=10013971]*/ '10013971' UNION ALL SELECT /*[#3=10013971]*/ '10013971' FROM dual)
                AND S.SBJCT_CLSF IN ('1001','1002','1003','1004','1006')
                AND S.SBJCT_ID = P.SBJCT_ID
                AND P.SEQ = (SELECT /*+ INDEX_DESC(RSBJCT_PRGS RSBJCT_PRGS_PK) */ SEQ FROM RSBJCT_PRGS
                    WHERE SBJCT_ID = P.SBJCT_ID AND ROWNUM = 1 AND SBJCT_STEP <= 1)
        )
            AND P1.SBJCT_ID = SUB.SBJCT_ID
            AND P1.SEQ = (SELECT /*+ INDEX_DESC(P2 RSBJCT_PRGS_PK) */ P2.SEQ FROM RSBJCT_PRGS P2
                    WHERE P2.SBJCT_ID = P1.SBJCT_ID AND ROWNUM = 1 AND P2.SBJCT_STEP <= 1)
            AND SUB.RCPT_CNCL_DATE='99991231'
    ) CY, RSBJCT_BFUND_PRGS CX
    WHERE
        CX.SBJCT_ID(+) = CY.SBJCT_ID
        AND CX.AVAIL_SDATE(+) <= CY.AVAIL_SDATE
        AND CX.AVAIL_EDATE(+) >= CY.AVAIL_SDATE
        AND CX.SBJCT_STEP(+) = CY.SBJCT_STEP
        AND CX.BITEM_ALLOT_CD(+) LIKE 'R__00'
    GROUP BY
        CY.SBJCT_ID, CY.RCPT_DATE, CY.RCPT_CNCL_DATE, CY.KK, CY.KK1, CY.OFFCL_SBJCT_NM,
        CY.GROSS_DVLP_SDATE, CY.GROSS_DVLP_EDATE, CY.AVAIL_EDATE, CY.GNRL_SBJCT_ID,
        CY.RECP_BUSI_ID, CY.MNGR_MBR_ID, CY.CHRGR_MBR_ID, CY.MAIN_DEPT_ID, CY.ANCM_ID,
        CY.BCLSF_CD, CY.RANK, CY.SBJCT_CLSF, CY.RCV_STATUS_CLSF, CY.IS_CHONG_PRINT, CY.CONTACT_PNT,
        CY.FDUTY_CD, CY.REG_MBR_ID, CY.CHRGR_BUSI_ID,
        CY.SBJCT_NOW_STEP, CY.SBJCT_NOW_DEGREE, CY.AGRE_DATE, CY.LAST_HSTR_NUM, CY.ISEDIT, CY.ISEDIT3,
        CY.SBJCT_STEP, CY.ORDR, CY.RCV_STATUS_CLSF_NM
) K
UNION
SELECT
    SBJCT_ID, RCPT_DATE, RCPT_CNCL_DATE, KK, KK1, OFFCL_SBJCT_NM,
    GROSS_DVLP_SDATE, GROSS_DVLP_EDATE, AVAIL_EDATE, GNRL_SBJCT_ID,
    RECP_BUSI_ID, MNGR_MBR_ID, CHRGR_MBR_ID, MAIN_DEPT_ID, ANCM_ID,
    BCLSF_CD, RANK, SBJCT_CLSF, RCV_STATUS_CLSF, IS_CHONG_PRINT, CONTACT_PNT,
    FDUTY_CD, REG_MBR_ID, CHRGR_BUSI_ID,
    SBJCT_NOW_STEP, SBJCT_NOW_DEGREE, AGRE_DATE, LAST_HSTR_NUM, ISEDIT, ISEDIT3,
    SBJCT_STEP, ORDR, RCV_STATUS_CLSF_NM,
    CASE WHEN TO_CASH_MNY = WI_CASH_MNY AND TO_ARTCL = WI_ARTCL THEN 1 ELSE 0 END IS_EDIT2,
    (SELECT COUNT(*) FROM RSUBJECT_TBI_SRVY WHERE SBJCT_ID = K.SBJCT_ID) CNT
FROM(
    SELECT /*+ INDEX(CX RSBJCT_BFUND_PRGS_PK) */
        SUM(CASE WHEN CX.IS_CMMT = 2 AND CX.BITEM_ALLOT_CD LIKE 'R__00' THEN CX.CASH_MNY ELSE 0 END) TO_CASH_MNY,
        SUM(CASE WHEN CX.IS_CMMT = 2 AND CX.BITEM_ALLOT_CD LIKE 'R__00' THEN CX.ARTCL ELSE 0 END) TO_ARTCL,
        SUM(DECODE(CX.BITEM_ALLOT_CD, 'R1400', CX.CASH_MNY, 0)) WI_CASH_MNY,
        SUM(DECODE(CX.BITEM_ALLOT_CD, 'R1400', CX.ARTCL, 0)) WI_ARTCL,
        CY.SBJCT_ID, CY.RCPT_DATE, CY.RCPT_CNCL_DATE, CY.KK, CY.KK1, CY.OFFCL_SBJCT_NM,
        CY.GROSS_DVLP_SDATE, CY.GROSS_DVLP_EDATE, CY.AVAIL_EDATE, CY.GNRL_SBJCT_ID,
        CY.RECP_BUSI_ID, CY.MNGR_MBR_ID, CY.CHRGR_MBR_ID, CY.MAIN_DEPT_ID, CY.ANCM_ID,
        CY.BCLSF_CD, CY.RANK, CY.SBJCT_CLSF, CY.RCV_STATUS_CLSF, CY.IS_CHONG_PRINT, CY.CONTACT_PNT,
        CY.FDUTY_CD, CY.REG_MBR_ID, CY.CHRGR_BUSI_ID, CY.RCV_STATUS_CLSF_NM,
        CY.SBJCT_NOW_STEP, CY.SBJCT_NOW_DEGREE, CY.AGRE_DATE, CY.LAST_HSTR_NUM, CY.ISEDIT, CY.ISEDIT3,
        CY.SBJCT_STEP, CY.ORDR
    FROM (
        SELECT /*+ INDEX(S RSUBJECT_IDX3) INDEX(P RSBJCT_PRGS_PK) */
            SUB.SBJCT_ID, SUB.RCPT_DATE, SUB.RCPT_CNCL_DATE, P.AVAIL_SDATE,
            DECODE(SUB.GNRL_SBJCT_ID, NULL, SUB.SBJCT_ID, SUB.GNRL_SBJCT_ID) KK,
            DECODE(SUB.SBJCT_CLSF, '1002', 1, 2) KK1,
            --DECODE(SUB.SBJCT_CLSF, '1003', '' || SUB.OFFCL_SBJCT_NM, SUB.OFFCL_SBJCT_NM) OFFCL_SBJCT_NM,
            (DECODE(SUB.SBJCT_CLSF, '1002', '[총괄]', '1003', '[세부]', '') || SUB.OFFCL_SBJCT_NM) OFFCL_SBJCT_NM,
            SUB.GROSS_DVLP_SDATE, SUB.GROSS_DVLP_EDATE, SUB.AVAIL_EDATE, SUB.GNRL_SBJCT_ID,
            SUB.RECP_BUSI_ID, SUB.MNGR_MBR_ID, SUB.CHRGR_MBR_ID, SUB.MAIN_DEPT_ID, SUB.ANCM_ID,
            SUB.BCLSF_CD, SUB.RANK, SUB.SBJCT_CLSF, SUB.RCV_STATUS_CLSF,
      (CASE WHEN SUB.SBJCT_CLSF = '1002'
                    THEN (SELECT /*+ index_asc(isub rsubject_pk) */ RCV_STATUS_CLSF
                       FROM RSUBJECT ISUB
                     WHERE   ISUB.gnrl_SBJCT_ID = SUB.SBJCT_ID
                     AND iSUB.SBJCT_CLSF = '1003'
                   AND ISUB.RCV_STATUS_CLSF = (SELECT MIN(RCV_STATUS_CLSF) FROM RSUBJECT WHERE GNRL_SBJCT_ID = ISUB.GNRL_SBJCT_ID)-- 20081120
                     AND ROWNUM = 1
                      )
          ELSE  SUB.RCV_STATUS_CLSF
          END ) IS_CHONG_PRINT,
      SUB.CONTACT_PNT,
            SUB.FDUTY_CD, SUB.REG_MBR_ID, SUB.CHRGR_BUSI_ID,
            SUB.SBJCT_NOW_STEP, SUB.SBJCT_NOW_DEGREE, SUB.AGRE_DATE, SUB.LAST_HSTR_NUM,
            (SELECT CODE_NAME FROM RCODE WHERE MAIN_CODE='7097'
                AND SUB_CODE = DECODE(P.DUTY_CD, '2220', SUB.RCV_STATUS_CLSF, '07')) RCV_STATUS_CLSF_NM,
            (SELECT RCPT_EDATE FROM RANCM_BUSI WHERE ANCM_ID = SUB.ANCM_ID AND BCLSF_CD = SUB.BCLSF_CD) EDATE,
            (CASE WHEN
                (SELECT RCPT_EDATE FROM RANCM_BUSI
                    WHERE ANCM_ID = SUB.ANCM_ID
                        AND BCLSF_CD = SUB.BCLSF_CD) >= TO_CHAR(SYSDATE, 'YYYYMMDD')
                        AND P.DUTY_CD = '2220'
                        --AND SUB.CHRGR_MBR_ID = ''
                THEN '1'
                ELSE '2'
            END) ISEDIT,
            (CASE WHEN
                (SELECT RCPT_EDATE FROM RANCM_BUSI
                    WHERE ANCM_ID = SUB.ANCM_ID
                        AND BCLSF_CD = SUB.BCLSF_CD) >= TO_CHAR(SYSDATE, 'YYYYMMDD')
                        AND P.DUTY_CD = '2220'
                        AND SUB.CHRGR_MBR_ID = /*[#4=10013971]*/ '10013971'
                THEN '1'
                ELSE '2'
            END) ISEDIT3,
            P.SBJCT_STEP , P.ORDR
        FROM RSUBJECT SUB, RSBJCT_PRGS P
        WHERE
            SUB.AVAIL_EDATE='99991231'
                        AND SUB.RCPT_CNCL_DATE='99991231'
            --AND SUB.CHRGR_MBR_ID = ''
                AND sub.chrgr_mbr_id IN (SELECT s1.chrgr_mbr_id FROM RSUBJECT s1
                    WHERE s1.gnrl_sbjct_id = sub.sbjct_id --and sub.sbjct_clsf = '1002'
                    AND sub.chrgr_mbr_id = /*[#5=10013971]*/ '10013971' UNION ALL SELECT /*[#6=10013971]*/ '10013971' FROM dual)
            AND SUB.SBJCT_CLSF IN ('1001','1002','1003','1004','1006')
            AND SUB.SBJCT_ID = P.SBJCT_ID
            AND P.SEQ = (SELECT /*+ INDEX_DESC(RSBJCT_PRGS RSBJCT_PRGS_PK) */ SEQ FROM RSBJCT_PRGS
                WHERE SBJCT_ID = P.SBJCT_ID AND ROWNUM = 1 AND SBJCT_STEP <= 1)
    ) CY, RSBJCT_BFUND_PRGS CX
    WHERE
        CX.SBJCT_ID(+) = CY.SBJCT_ID
        AND CX.AVAIL_SDATE(+) <= CY.AVAIL_SDATE
        AND CX.AVAIL_EDATE(+) >= CY.AVAIL_SDATE
        AND CX.SBJCT_STEP(+) = CY.SBJCT_STEP
        AND CX.BITEM_ALLOT_CD(+) LIKE 'R__00'
    GROUP BY
        CY.SBJCT_ID, CY.RCPT_DATE, CY.RCPT_CNCL_DATE, CY.KK, CY.KK1, CY.OFFCL_SBJCT_NM,
        CY.GROSS_DVLP_SDATE, CY.GROSS_DVLP_EDATE, CY.AVAIL_EDATE, CY.GNRL_SBJCT_ID,
        CY.RECP_BUSI_ID, CY.MNGR_MBR_ID, CY.CHRGR_MBR_ID, CY.MAIN_DEPT_ID, CY.ANCM_ID,
        CY.BCLSF_CD, CY.RANK, CY.SBJCT_CLSF, CY.RCV_STATUS_CLSF,  CY.IS_CHONG_PRINT, CY.CONTACT_PNT,
        CY.FDUTY_CD, CY.REG_MBR_ID, CY.CHRGR_BUSI_ID,
        CY.SBJCT_NOW_STEP, CY.SBJCT_NOW_DEGREE, CY.AGRE_DATE, CY.LAST_HSTR_NUM, CY.ISEDIT, CY.ISEDIT3,
        CY.SBJCT_STEP, CY.ORDR, RCV_STATUS_CLSF_NM
) K
ORDER BY KK DESC, KK1
        ) sys_capsulation_innerfrom 
        WHERE ROWNUM <=10

) 
WHERE sys_capsulation_innerfrom_rn >= 1

 

[운영DB에서 플랜]

Plan
SELECT STATEMENT  CHOOSECost: 7,137  Bytes: 1,782  Cardinality: 2                      
 57 VIEW ITEP_RDBA. Cost: 7,137  Bytes: 1,782  Cardinality: 2                     
  56 COUNT STOPKEY                    
   55 VIEW ITEP_RDBA. Cost: 7,137  Bytes: 1,756  Cardinality: 2                   
    54 SORT UNIQUE STOPKEY  Cost: 7,135  Bytes: 1,828  Cardinality: 2                  
     53 UNION-ALL                 
      31 VIEW ITEP_RDBA. Cost: 7,081  Bytes: 914  Cardinality: 1                
       30 SORT GROUP BY  Cost: 7,081  Bytes: 620  Cardinality: 1               
        29 NESTED LOOPS OUTER  Cost: 7,079  Bytes: 620  Cardinality: 1              
         26 VIEW ITEP_RDBA. Cost: 7,076  Bytes: 584  Cardinality: 1             
          25 TABLE ACCESS BY INDEX ROWID ITEP_RDBA.RSBJCT_PRGS Cost: 2  Bytes: 26  Cardinality: 1            
           24 NESTED LOOPS  Cost: 7,076  Bytes: 228  Cardinality: 1           
            19 NESTED LOOPS  Cost: 7,074  Bytes: 202  Cardinality: 1          
             16 VIEW SYS.VW_NSO_1 Cost: 7,068  Bytes: 6  Cardinality: 1         
              15 SORT UNIQUE  Bytes: 46  Cardinality: 1        
               14 FILTER       
                7 NESTED LOOPS  Cost: 7,055  Bytes: 46  Cardinality: 1      
                 2 TABLE ACCESS BY INDEX ROWID ITEP_RDBA.RSUBJECT Cost: 7,054  Bytes: 36  Cardinality: 1     
                  1 INDEX FULL SCAN NON-UNIQUE ITEP_RDBA.RSUBJECT_IDX3 Cost: 26  Cardinality: 7,216    
                 6 INDEX RANGE SCAN UNIQUE ITEP_RDBA.RSBJCT_PRGS_PK Cost: 1  Bytes: 10  Cardinality: 1     
                  5 COUNT STOPKEY    
                   4 TABLE ACCESS BY INDEX ROWID ITEP_RDBA.RSBJCT_PRGS Cost: 11  Bytes: 108  Cardinality: 9   
                    3 INDEX RANGE SCAN DESCENDING UNIQUE ITEP_RDBA.RSBJCT_PRGS_PK Cost: 2  Cardinality: 11 
                13 UNION-ALL      
                 10 FILTER     
                  9 TABLE ACCESS BY INDEX ROWID ITEP_RDBA.RSUBJECT Cost: 2  Bytes: 11  Cardinality: 1    
                   8 INDEX RANGE SCAN NON-UNIQUE ITEP_RDBA.RSUBJECT_IDX2 Cost: 1  Cardinality: 3   
                 12 FILTER     
                  11 TABLE ACCESS FULL SYS.DUAL Cost: 11  Cardinality: 8,168    
             18 TABLE ACCESS BY INDEX ROWID ITEP_RDBA.RSUBJECT Cost: 3  Bytes: 196  Cardinality: 1         
              17 INDEX RANGE SCAN NON-UNIQUE ITEP_RDBA.RSUBJECT_IDX2 Cost: 1  Cardinality: 3        
            23 INDEX RANGE SCAN NON-UNIQUE ITEP_RDBA.RSBJCT_PRGS_IDX2 Cost: 1  Cardinality: 1          
             22 COUNT STOPKEY         
              21 TABLE ACCESS BY INDEX ROWID ITEP_RDBA.RSBJCT_PRGS Cost: 11  Bytes: 108  Cardinality: 9        
               20 INDEX RANGE SCAN DESCENDING UNIQUE ITEP_RDBA.RSBJCT_PRGS_PK Cost: 2  Cardinality: 11       
         28 TABLE ACCESS BY INDEX ROWID ITEP_RDBA.RSBJCT_BFUND_PRGS Cost: 3  Bytes: 36  Cardinality: 1             
          27 INDEX RANGE SCAN UNIQUE ITEP_RDBA.RSBJCT_BFUND_PRGS_PK Cost: 2  Cardinality: 1            
      52 VIEW ITEP_RDBA. Cost: 49  Bytes: 914  Cardinality: 1                
       51 SORT GROUP BY  Cost: 49  Bytes: 620  Cardinality: 1               
        50 NESTED LOOPS OUTER  Cost: 46  Bytes: 620  Cardinality: 1              
         47 VIEW ITEP_RDBA. Cost: 43  Bytes: 584  Cardinality: 1             
          46 FILTER            
           39 TABLE ACCESS BY INDEX ROWID ITEP_RDBA.RSBJCT_PRGS Cost: 2  Bytes: 26  Cardinality: 1           
            38 NESTED LOOPS  Cost: 30  Bytes: 222  Cardinality: 1          
             33 TABLE ACCESS BY INDEX ROWID ITEP_RDBA.RSUBJECT Cost: 28  Bytes: 196  Cardinality: 1         
              32 INDEX SKIP SCAN NON-UNIQUE ITEP_RDBA.RSUBJECT_IDX5 Cost: 27  Cardinality: 1        
             37 INDEX RANGE SCAN UNIQUE ITEP_RDBA.RSBJCT_PRGS_PK Cost: 1  Cardinality: 1         
              36 COUNT STOPKEY        
               35 TABLE ACCESS BY INDEX ROWID ITEP_RDBA.RSBJCT_PRGS Cost: 11  Bytes: 108  Cardinality: 9       
                34 INDEX RANGE SCAN DESCENDING UNIQUE ITEP_RDBA.RSBJCT_PRGS_PK Cost: 2  Cardinality: 11      
           45 UNION-ALL           
            42 FILTER          
             41 TABLE ACCESS BY INDEX ROWID ITEP_RDBA.RSUBJECT Cost: 2  Bytes: 11  Cardinality: 1         
              40 INDEX RANGE SCAN NON-UNIQUE ITEP_RDBA.RSUBJECT_IDX2 Cost: 1  Cardinality: 3        
            44 FILTER          
             43 TABLE ACCESS FULL SYS.DUAL Cost: 11  Cardinality: 8,168         
         49 TABLE ACCESS BY INDEX ROWID ITEP_RDBA.RSBJCT_BFUND_PRGS Cost: 3  Bytes: 36  Cardinality: 1             
          48 INDEX RANGE SCAN UNIQUE ITEP_RDBA.RSBJCT_BFUND_PRGS_PK Cost: 2  Cardinality: 1