[spring] 오라클 데이터베이스 페이징 처리
업데이트:
일반적으로 페이징 처리는 크게 번호를 이용하거나, 계속 보기의 형태로 구현된다. 번호를 이용한 페이징은 흔히 볼 수 있는 방식이고, 계속 보기는 Ajax와 앱이 등장한 이후에 ‘무한 스크롤’이나 ‘더 보기’와 같은 형태로 구현된다. 예제에서는 번호를 이용하는 방식으로 처리하게 된다.
order by의 문제
데이터베이스를 이용할 때 웹이나 애플리케이션에서 가장 신경쓰는 부분은 빠르게 처리되는 것, 필요한 양만큼만 데이터를 가져오는 것이다. 웹 페이지에서 페이징을 하는 이유는 최소한의 필요한 데이터만을 가져와서 빠르게 화면에 보여주기 위함이다.
빠르게 동작하는 SQL을 위해서는 order by를 이용하는 작업을 가능하면 하지 말아야 한다. order by는 데이터가 많은 경우에 엄청난 성능의 저하를 가져오기 때문에 데이터가 적은 경우와 정렬을 빠르게 할 수 있는 방법이 있는 경우가 아니라면 사용에 주의해야 한다.
실행 계획과 order by
실행 계획(Execution Plan)은 SQL을 데이터베이스에서 어떻게 처리할 것인가? 대한 것이다. SQL이 데이터베이스에 전달되면 데이터베이스는 여러 단계를 거쳐서 해당 SQL을 어떤 순서와 방식으로 처리할 것인지 계획을 세운다.
데이터베이스에 전달된 SQL문은 아래와 같은 과정을 거쳐서 처리된다.
- SQL 파싱 : SQL 구문에 오류가 있는지 SQL을 실행해야 하는 대상 객체(테이블, 제약 조건, 권한 등)가 존재하는지 검사
- SQL 최적화 : SQL이 실행되는데 필요한 비용(cost)을 계산하게 된다. 이 계산된 값을 기초로 어떤 방식으로 실행하는 것이 가장 좋다는 것을 판단하는 ‘실행 계획’을 세우게 된다.
- SQL 실행 : 세워진 실행 계획을 통해서 메모리상에서 데이터를 읽거나 물리적인 공간에서 데이터를 로딩하는 등의 작업을 하게 된다.
SQL Developer에서 실행 계획을 보려면 아래 버튼을 누르면 된다.
실행 계획을 보면 트리 구조로 방금 전 실행한 SQL이 어떻게 처리된 것인지 알려준다.
실행 계획을 해석하는 방법은 안쪽에서 바깥쪽으로, 위에서 아래로 보면된다. order by 절을 이용한 SQL문은 테이블을 Full로 접근하고 정렬했다는 것을 의미한다. Full은 테이블 내의 모든 데이터를 스캔했다는 의미이다. 실행 계획을 세우는 것은 데이터베이스에서 하는 역할이기 때문에 데이터의 양이나 제약 조건등의 여러 상황에 따라서 데이터베이스는 실행 계획을 다르게 작성한다.
테스트를 위해 아래 SQL문을 여러 번 실행 후 커밋한다.
INSERT INTO tbl_board (BNO, TITLE, CONTENT, WRITER)
(SELECT seq_board.NEXTVAL, TITLE, CONTENT, WRITER FROM tbl_board);
위의 insert문을 여러 번 실행하면 현재 tbl_board 테이블의 데이터 수만큼 다시 insert가 진행된다.
order by 보다는 인덱스
데이터가 많은 상태에서 정렬을 해야 한다면 가장 일방적으로 쓰이는게 인덱스(index)이다. 인덱스는 이미 정렬된 구조이므로 이를 이용해서 별도의 정렬을 하지 않아도 된다.
SELECT /*+ INDEX_DESC(tbl_board pk_board)*/
* FROM tbl_board WHERE BNO > 0;
위의 SQL문의 실행 계획에서는 SORT를 하지 않았고, TBL_BOARD를 바로 접근하지 않고 PK_BOARD를 이용해서 접근했다. 그리고 RANGE SCAN DESCENDING, BY INDEX ROWID로 접근했다는 점을 주의해서 봐야한다.
PK_BOARD 인덱스
tbl_board 테이블 생성 시 PK의 이름을 ‘pk_board’라고 지정했다. 데이터베이스에서 PK는 식별자의 의미와 인덱스의 의미를 가진다.
인덱스는 말 그대로 색인이다. 색인은 사람들이 쉽게 찾아볼 수 있게 알파벳 순서나 한글 순서로 정렬한다. 이를 통해서 원하는 내용을 위에서부터 혹은 반대로 찾아나가는데 이를 ‘스캔(scan)’한다고 표현한다.
인덱스와 실제 테이블을 연결하는 고리는 ROWID라는 존재이다. ROWID는 데이터베이스 내의 주소에 해당하는데 모든 데이터는 자신만의 주소를 가지고 있다. 데이터의 양이 많고, 색인이 존재한다면 색인을 찾고 색인에서 주소를 찾아서 접근하는 방식을 이용한다.
SELECT * FROM tbl_board WHERE BNO = 89;
안쪽을 먼저 보면 PK_BOARD는 인덱스이므로 먼저 인덱스를 이용해서 89번 데이터가 어디 있는지 ROWID를 찾아내고, 바깥쪽을 보면 BY INDEX ROWID라고 되어있는 말 그대로 ROWID를 통해 테이블에 접근한다.
인덱스를 이용하는 정렬
인덱스에서 가장 중요한 개념 중 하나는 ‘정렬이 되어 있다는 점’이다. 정렬이 이미 되어 있는 상태이므로 SORT 하는 과정을 생략할 수 있다.
bno의 역순으로 정렬한 결과를 원한다면 이미 정렬되어 있는 인덱스를 이용해서 뒤에서부터 찾아 올라가는 방식, DESCENDING을 사용한다.
인덱스를 역순으로 찾기 때문에 가장 먼저 찾은 bno 값은 가장 큰 값을 가진 데이터가 된다. 이후에는 테이블에 접근해서 데이터를 가져오게 되는데, 이런 과정이 반복되면 정렬을 하지 않아도 동일하게 정렬된 결과를 볼 수 있다.
이러한 특성 때문에 실무에서도 데이터의 양이 많고 정렬이 필요한 상황이라면 우선적으로 생각하는 것이 ‘인덱스’를 작성하는 것이다.
인덱스와 오라클 힌트(hint)
오라클은 SELECT문을 전달할 때 힌트(hint)라는 것을 사용할 수 있다. 힌트는 SQL 튜닝의 핵심부분으로 일종의 지시구문이다. 데이터베이스에 ‘지금 내가 전달한 select문을 이렇게 저렇게 실행해 달라는’ 의미를 담고있다.
게시물 목록은 일반적으로 시간의 역순으로 출력되어야 한다. 때문에 SQL에서는 ‘order by desc’와 같은 구문을 추가할 수 있다. 문제는 ‘order by desc’와 같은 조건은 데이터베이스 상황에 따라서 테이블의 모든 데이터를 정렬하는 방식으로 동작할 수 있다.
반면에 힌트는 개발자가 데이터베이스에 어떤 방식으로 실행해 줘야하는지를 명시하기 때문에 조금 강제성이 부여되는 방식이다.
SELECT * FROM tbl_board ORDER BY BNO DESC;
SELECT /*+INDEX_DESC(tbl_board pk_board)*/ *
FROM tbl_board;
-- 두 SQL은 동일한 결과를 생성한다.
힌트를 사용한 SQL문의 실행 계획을 보면 힌트에 명시한 것과 같이 잘 실행되고 있는 걸 확인할 수 있다.
힌트 사용 문법
힌트는 다음과 같은 문법을 사용한다.
SELECT /*+ Hint name(param...) */ column name, ....
FROM TABLE NAME .....
힌트 자체는 SQL로 처지되지 않기 때문에 위의 그림처럼 뒤에 컬럼명이 나오더라도 별도의 ‘,’로 처리되지 않는다.
FULL 힌트
FULL 힌트는 테이블 전체를 스캔할 것으로 명시한다. FULL 힌트는 테이블의 모든 데이터를 스캔하기 때문에 데이터가 많을 때는 상당히 느리게 실행된다.
SELECT /*+ FULL(tbl_board) */ * FROM tbl_board ORDER BY BNO DESC;
위의 SQL 실행 계획을 보면 TBL_BOARD를 FULL로 접근하고, 다시 SORT가 적용된다. 즉, 인덱스를 사용했을 때보다 느리다.
INDEX_ASC, INDEX_DESC 힌트
INDEX_ASC/DESC는 인덱스를 순서대로 이용할 것인지 역순으로 이용할 것인지를 지정한다. 이 힌트를 사용하면 정렬 시 SORT 과정을 생략할 수 있다.
SELECT /*+ INDEX_ASC(tbl_board pk_board) */ * FROM tbl_board
WHERE BNO > 0;
INDEX_ASC/DESC의 경우 동일한 조건의 ORDER BY 구문을 작성하지 않아도 된다.
ROWNUM과 인라인뷰
오라클 데이터베이스는 페이지 처리를 위해서 ROWNUM이라는 특별한 키워드를 사용해 데이터에 순번을 붙인다.
ROWNUM은 쉽게 생각해서 SQL이 실행된 결과에 넘버링을 해준다고 생각하면 된다. ROWNUM은 실제 데이터가 아니라 테이블에서 데이터를 추출한 후 처리되는 변수이므로 상황에 따라서 그 값이 매번 달라질 수 있다.
상황에 따라서 값이 달라지는 이유는 ROWNUM이 데이터를 가져올 때 적용되는 것이고, 이 후 정렬되는 과정에서 ROWNUM이 변경되지 않는다는 것이다. 즉 ROWNUM이 먼저 실행되고 정렬은 그 이후에 처리된다는 의미이다. 따라서 아래 두 SQL문은 실행 결과가 다르다.
SELECT ROWNUM rn, bno, title FROM tbl_board;
SELECT /*+FULL(tbl_board)*/
ROWNUM RN, BNO, TITLE FROM tbl_board WHERE BNO > 0 ORDER BY BNO;
인덱스를 이용한 접근 시 ROWNUM
PK_BOARD 인덱스를 통해 접근하면 다음과 같은 과정으로 접근한다.
- PK_BOARD 인덱스를 통해 테이블 접근
- 접근한 데이터에 ROWNUM 부여
1의 과정에서 이미 정렬이 되어있기 때문에 위에 나온 SQL 구문의 결과와는 전혀 다른 값을 가지게 된다. 따라서 다음과 같이 SQL문을 작성하면 된다.
SELECT /*+ INDEX_DESC(tbl_board pk_board)*/
ROWNUM RN, BNO, TITLE, CONTENT
FROM tbl_board
WHERE BNO > 0;
페이지 번호 1, 2의 데이터
한 페이지당 10개의 데이터를 출력한다고 가정하면 ROWNUM 조건을 WHERE 구문에 추가해서 다음과 같이 작성할 수 있다.
SELECT /*+ INDEX_DESC(TBL_BOARD PK_BOARD)*/
ROWNUM RN, BNO, TITLE, CONTENT
FROM TBL_BOARD
WHERE ROWNUM <= 10;
위와 같은 방식으로 2페이지 데이터를 구할 수 있을 것 같은데 아래 SQL 구문을 실행해보면 원하는 결과를 얻을 수 없다.
SELECT /*+ INDEX_DESC(TBL_BOARD PK_BOARD)*/
ROWNUM RN, BNO, TITLE, CONTENT
FROM TBL_BOARD
WHERE ROWNUM > 10 and ROWNUM <=20;
실행 계획을 보면 우선 ROWNUM > 10, 데이터들을 찾는다. 문제는 TBL_BOARD에서 처음으로 나오는 값은 ‘1’이다. TBL_BOARD에서 데이터를 찾고 ROWNUM 값이 1이 된 데이터는 WHERE 조건에 의해 무효화된다.
이후에 다시 다른 데이터를 가져오면 새로운 데이터가 첫 번째 데이터가 되므로 다시 ROWNUM이 ‘1’이 된다. 결론적으로는 아무 데이터도 출력하지 못한다. 이러한 이유로 SQL을 작성할 때 ROWNUM 조건은 반드시 1이 포함되어야 한다.
SELECT /*+ INDEX_DESC(TBL_BOARD PK_BOARD)*/
ROWNUM RN, BNO, TITLE, CONTENT
FROM TBL_BOARD
WHERE ROWNUM <=20;
인라인뷰(In-line View) 처리
지금 문제는 2페이지의 경우 11-20번까지의 데이터만 출력되어야 하는데 1페이지의 데이터가 함께 나온다. 이 문제를 해결하기 위해 인라인뷰를 사용해야한다. 인라인뷰는 SELECT문 안쪽 FROM에 다시 SELECT문을 기술하는 방법이다. SELECT문은 인라인뷰로 작성된 결과를 마치 하나의 테이블처럼 사용한다. 20개의 데이터를 인라인뷰로 가져오고 바깥 SELECT문에서 추가적인 처리를 한다.
SELECT BNO, TITLE, CONTENT
FROM (
SELECT /*+ INDEX_DESC(TBL_BOARD PK_BOARD)*/
ROWNUM RN, BNO, TITLE, CONTENT
FROM TBL_BOARD
WHERE ROWNUM <= 20 )
WHERE RN > 10;
이 과정을 정리하면 다음과 같은 순서이다.
- 필요한 순서로 정렬된 데이터에 ROWNUM을 붙인다.
- 처음부터 해당 페이지의 데이터를 ‘ROWNUM <= 30’과 같은 조건을 이용해서 구한다.
- 구해놓은 데이터를 하나의 테이블처럼 간주하고 인라인뷰로 처리한다.
- 인라인뷰에서 필요한 데이터만을 남긴다.
- 참고 : 코드로 배우는 스프링 웹 프로젝트
공유하기
Twitter Google+ LinkedIn
댓글남기기