인라인 뷰에 ROWNUM을 추가할 때는 주의하자

인라인 뷰에 ROWNUM을 사용하면 쿼리변환이 일어나지 않게 되므로 주의해서 사용해야 된다.

-- 테이블 생성
DROP TABLE rownum_t3 PURGE ;

CREATE TABLE rownum_t3
AS
SELECT level AS c1, chr(65+mod(level,26)) AS c2, level+99999 AS c3
FROM DUAL
CONNECT BY level <= 100000 ;

-- 인덱스 생성
CREATE INDEX rownum_t3_idx_01 ON rownum_t3 (c1) ;

-- 변수 설정
var b1 varchar2(2)
var b2 number
exec :b1 := 'A';
exec :b2 := 102530;



-- 인라인 뷰 안에 rownum 이 있을 때

SELECT t3.*
     , t2.*
FROM   ROWNUM_T2 t2,
      (SELECT ROWNUM rnum
            , rownum_t3.*
       FROM   ROWNUM_T3 ) t3
WHERE  t2.c1 = t3.c1
AND    t2.c2 = :b1
AND    t2.c3 = :b2 ;

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |  2893 |   177K|   330   (1)| 00:00:04 |
|*  1 |  HASH JOIN                   |                  |  2893 |   177K|   330   (1)| 00:00:04 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ROWNUM_T2        |  2893 | 83897 |   259   (0)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | ROWNUM_T2_IDX_03 |   289 |       |     3   (0)| 00:00:01 |
|   4 |   VIEW                       |                  |   100K|  3320K|    69   (2)| 00:00:01 |
|   5 |    COUNT                     |                  |       |       |            |          |
|   6 |     TABLE ACCESS FULL        | ROWNUM_T3        |   100K|  1171K|    69   (2)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T2"."C1"="T3"."C1")
   3 - access("T2"."C2"=:B1 AND "T2"."C3"=TO_NUMBER(:B2))

-- 인라인 뷰에 rownum 이 있으므로 뷰 머징이 되지 않아서 view 가 그대로 나타남.
-- 뷰 머징이 되지 않을 때 옵티마이저는 View Predication 을 수행해보는데 인라인 뷰 안의 조건 변경에 의해
-- 데이터에 부여되는 rownum이 달라질 수 있으므로 View Predication 을 수행할 수 없음.


var b1 varchar2(2)
var b2 number
exec :b1 := 'A';
exec :b2 := 102530;



-- 인라인 뷰 밖에 rownum 이 있음
SELECT rownum
     , t3.*
     , t2.*
FROM   rownum_t2 t2
     ,(SELECT *
       FROM rownum_t3 ) t3
WHERE  t2.c1 = t3.c1
AND    t2.c2 = :b1
AND    t2.c3 = :b2 ;

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |  2893 |   115K|   330   (1)| 00:00:04 |
|   1 |  COUNT                        |                  |       |       |            |          |
|*  2 |   HASH JOIN                   |                  |  2893 |   115K|   330   (1)| 00:00:04 |
|   3 |    TABLE ACCESS BY INDEX ROWID| ROWNUM_T2        |  2893 | 83897 |   259   (0)| 00:00:04 |
|*  4 |     INDEX RANGE SCAN          | ROWNUM_T2_IDX_03 |   289 |       |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | ROWNUM_T3        |   100K|  1171K|    69   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T2"."C1"="ROWNUM_T3"."C1")
   4 - access("T2"."C2"=:B1 AND "T2"."C3"=TO_NUMBER(:B2))

-- 인라인 뷰 안에 rownum 이 없으므로 뷰 머징이 일어난 것을 확인할 수 있음.