[Oracle] SQL Performance - View 사용 주의

View를 사용하면 코드 유지보수성은 좋아질 수 있으나 성능관점에서는 결과가 안좋을 수 있다. 아래 예시를 살펴보자.

 

Employees 테이블 

NO	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	DEPARTMENT_ID
1	198	Donald	OConnell	DOCONNEL	50
2	199	Douglas	Grant	DGRANT	50
3	200	Jennifer	Whalen	JWHALEN	10

 

Departments 테이블

NO	DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
1	10	Administration	200	1700
2	20	Marketing	201	1800
3	30	Purchasing	114	1700

 

Locations 테이블

NO	LOCATION_ID	STREET_ADDRESS	POSTAL_CODE	CITY
1	1000	1297 Via Cola di Rie	00989	Roma
2	1100	93091 Calle della Testa	10934	Venice
3	1200	2017 Shinjuku-ku	1689	Tokyo

 

아래와 같이 View를 생성하였다.

CREATE OR REPLACE VIEW VW_EMP_DEPT AS
SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.FIRST_NAME, E.SALARY, E.JOB_ID,
    D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID, L.STATE_PROVINCE
    FROM EMPLOYEES E, DEPARTMENTS D , LOCATIONS L
WHERE E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID;

 

아래와 같이 View와 Join하여 쿼리를 수행하였을때 Execution Plan을 보면 아래와 같이 Departments 테이블 관련 컬럼은 조회하지 않으나 수행 계획에는 표시됨을 볼 수 있다.

SELECT  V.FIRST_NAME, V.LAST_NAME, J.JOB_TITLE
FROM JOBS J, VW_EMP_DEPT V
WHERE J.JOB_ID = 'IT_PROG'
AND V.JOB_ID = J.JOB_ID;

 

 

 

다음 View를 보자

CREATE OR REPLACE VIEW VW_CUST_NUM_SOLD AS
SELECT C.CUST_ID, CUST_FIRST_NAME,CUST_LAST_NAME, PROD_ID, COUNT(*) NUM_SOLD 
FROM SALES S, CUSTOMERS C
WHERE S.CUST_ID = C.CUST_ID
GROUP BY C.CUST_ID,CUST_FIRST_NAME, CUST_LAST_NAME, PROD_ID
ORDER BY NUM_SOLD DESC;

 

다음 쿼리를 실행해보면 View 내부에 Predicate가 잘 들어가 있는 것을 볼 수있다.

SELECT CUST_ID,PROD_NAME,NUM_SOLD
FROM VW_CUST_NUM_SOLD V, PRODUCTS P
WHERE V.PROD_ID = P.PROD_ID
AND V.PROD_ID = 13;

 

Predicate 잘 들어가 있고 총 Cost 447이 나왔다.

 

위와 같은 결과 인데 View 기준으로 Outer Join을 하면 어떻게 될까?

SELECT CUST_ID,PROD_NAME,NUM_SOLD
FROM VW_CUST_NUM_SOLD V, PRODUCTS P
WHERE V.PROD_ID = P.PROD_ID(+)
AND V.PROD_ID = 13;

 

Customer Table 의 경우 index, predicate없이 full 스캔이 되어버렸다. 총 Cost는 769로 엄청 늘었다.

 

동일한 결과를 위해 View를 사용하지 않고 직접 쿼리를 구성하면 어떻게 될까?

SELECT C.CUST_ID, P.PROD_NAME, COUNT(*) NUM_SOLD FROM SALES S, CUSTOMERS C, PRODUCTS P
WHERE S.CUST_ID = C.CUST_ID
AND S.PROD_ID = P.PROD_ID(+)
AND P.PROD_ID = 13
GROUP BY C.CUST_ID, P.PROD_NAME
ORDER BY NUM_SOLD DESC;

 

Customer table에 대해서 index조회가 잘 되어서 Outer Join을 수행해도 결과가 Cost 364로 양호하다.

 

-- The End --