SQL 튜닝의 시작 (2013년)
인라인 뷰에 ROWNUM을 추가할 때는 주의하자 0 0 53,699

by 구루비스터디 ROWNUM 인라인뷰 [2018.07.14]


인라인 뷰에 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 이 없으므로 뷰 머징이 일어난 것을 확인할 수 있음.


"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3829

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입