안녕하세요? 다시 한번 더 부탁드립니다. ^^

최종 조회 문장과 실행계획 입니다만,

정확히 어디가 문제인지는;;; 잘 모르겠습니다.

※ 그리고, 이게 잘 나온거라면, EXTRACTVALUE 부분을 튜닝해야하는 건가요?

1. 최종 조회 문장

SELECT EXTRACTVALUE(A.XBRLDOC,
                    '/xbrl/rapm:StdDev_4W',
                    'xmlns="http://www.xbrl.org/2003/instance" xmlns:rapm="http://www.company.com/rapm/2007-10-01"') "STD_DEV",
       EXTRACTVALUE(B.XBRLDOC,
                    '/xbrl/crapm:AvgRt_4W',
                    'xmlns="http://www.xbrl.org/2003/instance" xmlns:crapm="http://www.company.com/crapm/2007-10-01"') "AVGRT"
FROM   TBL_RAPM  A,
       TBL_CRAPM B
WHERE  A.TRD_DT   = '20070616'
AND    B.PEER_CD  = 'A1111'
AND    B.TRD_DT   = A.TRD_DT
AND    (A.PROD_CD, B.CO_CD) IN (SELECT PROD_CD , CO_CD
                                FROM   (SELECT PROD_CD
                                             , EXTRACTVALUE(XBRLDOC,
                                                            '/xbrl/info:CoCd',
                                                            'xmlns="http://www.xbrl.org/2003/instance" xmlns:info="http://www.company.com/info/2007-10-01"') CO_CD
                                             , ROW_NUMBER() OVER(PARTITION BY PROD_CD ORDER BY CHG_DT DESC) CNT
                                        FROM   TBL_INFO D
                                        WHERE SUBSTR(EXTRACTVALUE(XBRLDOC,
                                                                  '/xbrl/info:CloseGb',
                                                                  'xmlns="http://www.xbrl.org/2003/instance" xmlns:info="http://www.company.com/info/2007-10-01"'), 1, 1) = '1'
                                           AND SUBSTR(EXTRACTVALUE(XBRLDOC,
                                                                   '/xbrl/info:PeerCd',
                                                                   'xmlns="http://www.xbrl.org/2003/instance" xmlns:info="http://www.company.com/info/2007-10-01"'), 1, 8) = 'A1111'
                                        )
                                WHERE  CNT = 1)
;

2. 실행계획

--------------------------------------------------------------------------------------------------------
| Operation                           |  Name                  |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| SELECT STATEMENT                    |                        |     3 |    6K|     16 |       |       |
|  NESTED LOOPS                       |                        |     3 |    6K|     16 |       |       |
|   HASH JOIN                         |                        |     2 |    4K|     14 |       |       |
|    TABLE ACCESS BY INDEX ROWID      |TBL_CRAPM               |    16 |    1K|      4 |       |       |
|     INDEX RANGE SCAN                |PK_TBL_CRAPM            |    16 |      |      2 |       |       |
|    VIEW                             |VW_NSO_1                |     7 |   13K|      9 |       |       |
|     SORT UNIQUE                     |                        |     7 |   13K|        |       |       |
|      VIEW                           |                        |     7 |   13K|      9 |       |       |
|       WINDOW SORT PUSHED RANK       |                        |     7 |  462 |      9 |       |       |
|        TABLE ACCESS BY INDEX ROWID  |TBL_INFO                |     7 |  462 |      2 |       |       |
|         INDEX RANGE SCAN            |TBL_INFO_FX4            |     1 |      |      1 |       |       |
|   TABLE ACCESS BY INDEX ROWID       |TBL_RAPM                |     1 |   97 |      1 |       |       |
|    INDEX UNIQUE SCAN                |PK_TBL_RAPM             |     1 |      |        |       |       |
--------------------------------------------------------------------------------------------------------

Plan

SELECT STATEMENT  CHOOSE
Cost : 16 Bytes : 6,561 Cardinality : 3        

 12 NESTED LOOPS  Cost : 16 Bytes : 6,561 Cardinality : 3       
  9 HASH JOIN  "B"."CO_CD"="VW_NSO_1"."$nso_col_2"Cost : 14 Bytes : 4,180 Cardinality : 2      
   2 TABLE ACCESS BY INDEX ROWID UDBA.TBL_CRAPM Cost : 4 Bytes : 1,280 Cardinality : 16     
    1 INDEX RANGE SCAN UDBA.PK_TBL_CRAPM "B"."PEER_CD"='A1111' AND "B"."TRD_DT"='20070616'Cost : 2 Cardinality : 16    
   8 VIEW SYS.VW_NSO_1 Cost : 9 Bytes : 14,070 Cardinality : 7     
    7 SORT UNIQUE  Bytes : 14,161 Cardinality : 7    
     6 VIEW UDBA. "from$_subquery$_003"."CNT"=1Cost : 9 Bytes : 14,161 Cardinality : 7   
      5 WINDOW SORT PUSHED RANK  ROW_NUMBER() OVER ( PARTITION BY "D"."PROD_CD" ORDER BY "D"."CHG_DT" DESC )<=1Cost : 9 Bytes : 462 Cardinality : 7  
       4 TABLE ACCESS BY INDEX ROWID UDBA.TBL_INFO Cost : 2 Bytes : 462 Cardinality : 7 
        3 INDEX RANGE SCAN UDBA.TBL_INFO_FX4 SUBSTR(EXTRACTVALUE(SYS_MAKEXML("D"."SYS_NC00004$"),'/xbrl/info:CloseGb','xmlns="http://www.xbrl.org/2003/instance" xmlns:info="http://www.company.com/info/2007-10-01"'),1,1)='1' AND SUBSTR(EXTRACTVALUE(SYS_MAKEXML("D"."SYS_NC00004$"),'/xbrl/info:PeerCd','xmlns="http://www.xbrl.org/2003/instance" xmlns:info="http://www.company.com/info/2007-10-01"'),1,8)='A1111'Cost : 1 Cardinality : 1
  11 TABLE ACCESS BY INDEX ROWID UDBA.TBL_RAPM Cost : 1 Bytes : 97 Cardinality : 1      
   10 INDEX UNIQUE SCAN UDBA.PK_TBL_RAPM "A"."PROD_CD"="VW_NSO_1"."$nso_col_1" AND "A"."TRD_DT"='20070616'Cardinality : 1