[JSP] 중간 팀 프로젝트 : View 테이블 생성

View란?

DB에 존재하는 가상의 테이블을 의미한다.

일반 테이블처럼 DROP VIEW 이전까지는 DB에 저장이 되지만, 데이터가 따로 저장되지는 않는다.

 

View를 사용하는 이유는

많은 JOIN이나 서브쿼리가 사용되어서 복잡해지는 쿼리를 간결하게 만들어 줄 수 있고,

반복해서 사용되는 쿼리를 재사용할 수 있다.

 

 

 

▼ 아래는 최초 작성한 상품 전체 출력 기능에 대한 SQL문이다.

SELECT P.PRODUCT_NUM, P.PRODUCT_NAME, P.PRODUCT_PRICE,P.PRODUCT_ADDRESS, P.PRODUCT_LOCATION, P.PRODUCT_CATEGORY,COALESCE(RV.RATING,0) AS RATING,
COALESCE(RS.RESERVATION_COUNT,0) AS RESERVATION_COUNT, COALESCE(W.WISHLIST_PRODUCT_NUM,0) AS WISHLIST_COUNT, I.FILE_NAME, I.FILE_EXTENSION, I.FILE_DIR
FROM PRODUCT P 
LEFT JOIN(
	SELECT R.REVIEW_PRODUCT_NUM, ROUND(AVG(R.REVIEW_STAR),1) AS RATING 
	FROM REVIEW R GROUP BY R.REVIEW_PRODUCT_NUM
)RV ON P.PRODUCT_NUM = RV.REVIEW_PRODUCT_NUM
LEFT JOIN(
	SELECT RE.RESERVATION_PRODUCT_NUM, COUNT(RE.RESERVATION_PRODUCT_NUM) AS RESERVATION_COUNT  
	FROM RESERVATION RE GROUP BY RE.RESERVATION_PRODUCT_NUM
) RS ON P.PRODUCT_NUM = RS.RESERVATION_PRODUCT_NUM
LEFT JOIN (
	SELECT WI.WISHLIST_PRODUCT_NUM, COUNT(WI.WISHLIST_PRODUCT_NUM) AS WISHLIST_COUNT 
	FROM WISHLIST WI GROUP BY WI.WISHLIST_PRODUCT_NUM
)W ON P.PRODUCT_NUM=W.WISHLIST_PRODUCT_NUM
LEFT JOIN (
	SELECT FILE_NAME, FILE_EXTENSION, FILE_DIR, ITEM_NUM 
	FROM (
		SELECT FILE_NAME, FILE_EXTENSION, FILE_DIR,ITEM_NUM, ROW_NUMBER() OVER(PARTITION BY ITEM_NUM ORDER BY FILE_NUM)AS RN 
		FROM IMAGEFILE 
		WHERE FILE_CONDITION=1
	)
	WHERE RN=2
)I ON P.PRODUCT_NUM = I.ITEM_NUM
ORDER BY PRODUCT_NUM DESC;

 

 

상품 페이지에서는 사용자에게 보여줘야 하는 데이터가 여러 테이블에 들어있기 때문에,

많은 JOIN이 발생된다.

다수의 JOIN 사용으로 인해 성능이 떨어지고 쿼리가 복잡해 보인다.

 

전체출력은 전체출력 / 별점순 전체출력 / 검색하여 전체출력 등 기능이 여러 가지로 나뉘어 있다.

이때 반복되는 sql문이 발생되기 때문에 이를 view 테이블로 만들었다.

 

 

 

▼ view 테이블 생성

CREATE VIEW PRODUCT_INFO_VIEW AS
SELECT P.PRODUCT_NUM, P.PRODUCT_NAME, P.PRODUCT_PRICE,P.PRODUCT_ADDRESS, P.PRODUCT_LOCATION, P.PRODUCT_CATEGORY,COALESCE(RV.RATING,0) AS RATING,
COALESCE(PM.PAYMENT_COUNT,0) AS PAYMENT_COUNT, COALESCE(W.WISHLIST_PRODUCT_NUM,0) AS WISHLIST_COUNT, PI.FILE_NAME, PI.FILE_EXTENSION, PI.FILE_DIR
FROM PRODUCT P 
LEFT JOIN(
	SELECT R.REVIEW_PRODUCT_NUM, ROUND(AVG(R.REVIEW_STAR),1) AS RATING 
	FROM REVIEW R GROUP BY R.REVIEW_PRODUCT_NUM
)RV ON P.PRODUCT_NUM = RV.REVIEW_PRODUCT_NUM
LEFT JOIN(
	SELECT PA.PAYMENT_PRODUCT_NUM, COUNT(PA.PAYMENT_PRODUCT_NUM) AS PAYMENT_COUNT  
	FROM PAYMENT PA GROUP BY PA.PAYMENT_PRODUCT_NUM
) PM ON P.PRODUCT_NUM = PM.PAYMENT_PRODUCT_NUM
LEFT JOIN (
	SELECT WI.WISHLIST_PRODUCT_NUM, COUNT(WI.WISHLIST_PRODUCT_NUM) AS WISHLIST_COUNT 
	FROM WISHLIST WI GROUP BY WI.WISHLIST_PRODUCT_NUM
)W ON P.PRODUCT_NUM=W.WISHLIST_PRODUCT_NUM
LEFT JOIN (
	SELECT I.FILE_NAME, I.FILE_EXTENSION, I.FILE_DIR, I.PRODUCT_ITEM_NUM 
	FROM (
		SELECT FILE_NAME, FILE_EXTENSION, FILE_DIR,PRODUCT_ITEM_NUM, ROW_NUMBER() OVER(PARTITION BY PRODUCT_ITEM_NUM ORDER BY FILE_NUM)AS RN 
		FROM IMAGEFILE)I 
		WHERE RN = 2
)PI
ON P.PRODUCT_NUM = PI.PRODUCT_ITEM_NUM;

 

테이블 생성처럼 CREATE문을 사용하여 VIEW 테이블을 생성해 주면 되는데,

VIEW 생성의 경우 권한이 필요하기 때문에 별도의 설정이 필요하다.

 

▼ 권한 설정은 아래의 글에서 확인할 수 있다.

2024.09.18 - [오류] - [Oracle] ORA-01031: insufficient privileges(0 rows affected)

 

 

 

▼ View 테이블 생성 후 전체출력 SQL문

SELECT PRODUCT_NUM, PRODUCT_NAME, PRODUCT_PRICE,PRODUCT_ADDRESS, PRODUCT_LOCATION, PRODUCT_CATEGORY, RATING, PAYMENT_COUNT, WISHLIST_COUNT, FILE_NAME, FILE_EXTENSION, FILE_DIR
FROM (SELECT PRODUCT_NUM, PRODUCT_NAME, PRODUCT_PRICE,PRODUCT_ADDRESS, PRODUCT_LOCATION, PRODUCT_CATEGORY, RATING, PAYMENT_COUNT, WISHLIST_COUNT, FILE_NAME, FILE_EXTENSION, FILE_DIR,
	ROW_NUMBER() OVER (ORDER BY PRODUCT_NUM DESC) AS ROW_NUM
	FROM PRODUCT_INFO_VIEW)
WHERE ROW_NUM BETWEEN (COALESCE(?,1)-1)*9+1 AND COALESCE(?,1)*9;

(현재 페이지네이션 기능 추가로 인해 최초 쿼리와 조금 상이한 부분은 있다.)

처음 쿼리보다 훨씬 간결해진 쿼리를 확인할 수 있다.