VIEW를 만들어도 원본테이블의 INDEX를 제대로 탈까?

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

방법 : 

아래와 같은 테이블 구조를 가지고 있을 때 

A 테이블 (COL1(PK), COL2, COL3) 

B 테이블 (COL3(PK), COL4, COL5)

아래와 같은 뷰를 만들어서

CREATE OR REPLACE VIEW TEST_VIEW AS 
SELECT A.COL1, A.COL2, MAX(A.COL3) AS COL3
FROM A, B
WHERE A.COL3 = B.COL3
GROUP BY A.COL1, A.COL2;

아래처럼 조회 했을 시 A테이블의 INDEX를 타는지 테스트를 해보았다.

SELECT *
FROM TEST_VIEW 
WHERE COL1 = '변수';

테스트

테이블 생성

SQL> 
create table a (col1 number, col2 number, col3 number, CONSTRAINT a_pk PRIMARY KEY (col1));
create table b (col3 number, col4 number, col5 number, CONSTRAINT b_pk PRIMARY KEY (col3));

데이터 삽입

SQL>
insert into a values(11,22,33);
insert into b values(33,22,11);
commit;

뷰 생성

SQL>
CREATE OR REPLACE VIEW TEST_VIEW AS 
SELECT A.COL1, A.COL2, MAX(A.COL3) AS COL3
FROM A, B
WHERE A.COL3 = B.COL3
GROUP BY A.COL1, A.COL2;

뷰 없이 조회 실행계획

SQL> 
SELECT A.COL1, A.COL2, MAX(A.COL3) AS COL3
FROM A, B
WHERE A.COL3 = B.COL3
AND COL1 = '11'
GROUP BY A.COL1, A.COL2;
 
      COL1     COL2        COL3
---------- ---------- ----------
    11       22          33
 
1 row selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1464742999
 
--------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       1 |      52 |       1   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT          |      |       1 |      52 |       1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |      |       1 |      52 |       1   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| A    |       1 |      39 |       1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN          | A_PK |       1 |         |       1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | B_PK |       1 |      13 |       0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("COL1"=11)
   5 - access("A"."COL3"="B"."COL3")
 
 
Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      3  consistent gets
      0  physical reads
      0  redo size
    660  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

A테이블의 인덱스(A_PK)를 타는것을 확인가능함

뷰를 이용해 조회 실행계획

SQL>
SELECT *
  FROM TEST_VIEW 
WHERE COL1 = '11';
 
     COL1     COL2        COL3
---------- ---------- ----------
    11       22          33
 
1 row selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1464742999
 
--------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       1 |      52 |       1   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT          |      |       1 |      52 |       1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |      |       1 |      52 |       1   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| A    |       1 |      39 |       1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN          | A_PK |       1 |         |       1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | B_PK |       1 |      13 |       0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("A"."COL1"=11)
   5 - access("A"."COL3"="B"."COL3")
 
 
Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      3  consistent gets
      0  physical reads
      0  redo size
    660  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

뷰를 이용해도 똑같이 A테이블의 인덱스(A_PK)를 타는것을 확인가능함

잘보면 Plan hash value도 동일한 것을 확인할 수 있음

참조 :