[0racle] paging처리 시 with절을 이용한 데이터 정렬(order by, rownum 함께 사용 주의)

체크박스가 적용되는 페이징을 위해 여러 테이블을 idx가 같은것끼리 묶어주고 조건을 적용해 원하는 개수의 열만큼 가져오고싶었다.

	SELECT * FROM review r
		left outer join member_info m
		on(r.member_idx = m.idx)
		 where VEHICLE_IDX=1
         order by create_date asc;
         		select v.*, f.type as fuelname, c.type as typename, l.name
		from FUELTYPE f, CARTYPE c, VEHICLE v ,location l
		where v.cartype_idx = c.idx and v.fueltype_idx = f.idx and v.location_idx=l.idx
    
        and rownum between 1 and 10
         order by v.idx asc
       ;

처음 작성한 sql문이다.  내가 원하는건 vehicle의 idx를 기준으로 1부터 10까지 가져오고싶었지만...

idx가 이상하게 정렬되어있다...

내가 원하는 조건은 데이터를 먼저 정렬하고(order by) 순서대로 원하는 수만큼(rownum) 갖고오고 싶었던건데 

oracle에서는 rownum이 먼저 매겨지고 order by가 적용되기 때문이다.

보통 이럴때 서브쿼리를 사용하지만 나는 조회할 테이블이 너무 많았고 mapper에서 조건까지 많이 들어가서 서브쿼리로 처리하기엔 너무 코드가 복잡해질것같아 with 를 사용했다. 

 

with기능은 오라클9 이후 버전부터 지원하니 참고하자!

 

with 의 기본 구조이다. 

WITH 이름 AS (
	SELECT 
    FROM 
    WHERE (선택)
)
SELECT
	....
FROM 
WHERE(선택)

 

 

 

with를 이용하여 쿼리를 다시 작성했다

    	with t as
		(select row_number() over(order by v.idx) num, v.*, f.type as fuelname, c.type as typename, l.name
		from FUELTYPE f, CARTYPE c, VEHICLE v ,location l 
        where v.cartype_idx = c.idx and v.fueltype_idx = f.idx and v.location_idx=l.idx 
        ) select * from t 
	where num between 1 and 10;

원하던대로 기준idx가 정렬되고 rownum이 생성되었다.

매퍼에서 적용할 때는 마지막 num에 변수를 넣어 페이징처리를 했다. 

 

	with t as
		(select row_number() over(order by v.idx) num, v.*, f.type as fuelname, c.type as typename, l.name
		from FUELTYPE f, CARTYPE c, VEHICLE v ,location l 
        where v.cartype_idx = c.idx and v.fueltype_idx = f.idx and v.location_idx=l.idx 
	<if test="typeList !=null">
		and c.idx in
	 <foreach collection="typeList" item="type" separator="," open="(" close=")">
		#{type}
	 </foreach>
	</if>
	<if test="fuelList !=null">
		and f.idx in
	 <foreach collection="fuelList" item="fuel" separator=","  open="(" close=")">
		#{fuel}
	 </foreach>
	</if>
	<if test = "keyword != null">
	 and v.model like #{keyword}
	</if>
	) select * from t 
	where num between 1 and #{page}

조건과 페이지를 넣어 만든 최종 매퍼 쿼리문.

이걸 다 서브쿼리로 만들려고 했으면.....훨씬 더 복잡했을거다!