[03] Oracle DB 실행계획, 인덱스, ROWNUM, Pagination

스프링을 공부하기 전 토이 프로젝트로 Node.js 등을 이용해 간단한 CRUD 게시판 기능을 구현할 때는 이러한 페이징(Pagination) 기능을 구현한 적은 없다. 그냥 단순하게 SELECT * FROM table; 처럼 몽땅 가져와서 순서 고려하지 않고 무지성으로 화면에 띄우게 했었다. 아마 데이터베이스에 대해 제대로 공부한 적도 없고, 페이징 처리가 필요할 만큼 많은 양의 데이터를 다뤄본 적도 없어서일 것이다.

 

대부분의 웹사이트를 이용할 때 우리는 해당 게시판에 존재하는 내용의 전체를 한번에 보지 않고,

대신에 일정 개수만큼 끊어서 차례대로 보게 된다. 대부분은 작성 시간을 기준으로 내림차순하여 보게 된다.

 

레코드의 수가 수만~수백만 개 정도라면, 이를 DBMS가 한번에 처리하는 시간과 성능에 영향을 주고, 이를 보여주는 브라우저에서도 마찬가지 문제가 일어날 것이다. 때문에 페이징 처리가 필요하다.

 

---

 

SQL 실행 계획

SQL을 실행하기 전 DBMS는 실행의 cost를 계산하여 어떤 방식과 순서로 SQL을 처리해야 가장 최적의 비용(짧은 수행 시간 등)으로 수행할 수 있을지 결정하는 SQL 최적화 과정을 거친다. 이 과정에서 '실행 계획'을 세우게 된다.

 

다음과 같은 SQL문의 실행 계획을 확인해보자.

SELECT * FROM tbl_board ORDER BY bno DESC;

SQL Developer의 '계획 설명' 버튼을 누르면

 

트리 구조로 해당 SQL이 어떻게 처리될 것인지를 알려준다. 실행 계획에서 어떤 순서로 SQL이 처리되는지 확인할 수 있는데, 

1) 안쪽부터 바깥쪽으로

2) 위에서 아래로

확인하면 된다.

 

---

 

인덱스를 이용한 정렬

현재 테이블 'tbl_board'에는 약 630만개의 레코드가 저장되어 있다. 이 테이블을 이용해 정렬 과정에서 인덱스의 장점을 확인해 보겠다.

 

'tbl_board'의 PRIMARY KEY는 bno이고, 제약 조건으로 인덱스 PK_BOARD가 존재한다.

 

SELECT * FROM tbl_board ORDER BY bno + 1 DESC;

위의 코드처럼 고의적으로 bno에 1을 더하여 인덱스를 활용할 수 없게 SQL을 작성하였다. 실행 계획과 실행 결과는 각각 다음과 같다.

실행 계획을 살펴보면, tbl_board 테이블 액세스를 FULL로 수행한 것을 확인할 수 있다. 테이블 전체를 대상으로 정렬을 수행한 것이다.

수행 시간은 평범한 노트북 기준으로 약 13초 걸렸다.

 

다음으로 인덱스를 이용한 select 결과에 대해 살펴보자.

SELECT * FROM tbl_board ORDER BY bno DESC;

실행 계획을 살펴보면, SORT 과정이 없고, INDEX ROWID를 이용해 tbl_board에 테이블 액세스를 한다.

같은 결과를 보여주지만, 인덱스를 이용해 SELECT를 수행한다면 1초도 안 걸리는 수행 시간을 보여준다.

---

오라클 힌트

힌트는 DBMS에게 사용자가 원하는 방식으로 SELECT문을 처리하게끔 의사를 전달하는 방법이다. 힌트 구문에서 에러가 나도 주석으로 취급하여 무시하므로 SQL 실행에 지장을 주지 않는다. 따라서 반드시 실행 계획을 통해 작성한 SELECT문이 힌트를 반영하고 있는지 확인해야 한다.

 

힌트는 다음과 같이 작성한다.

/*+ hintName (params...) */

여러 많은 힌트 중 INDEX_DESC 힌트를 살펴보겠다. 이 힌트는, 존재하는 인덱스의 맨 마지막부터 DESCENDING하며 처리하라는 의미이다. 

SELECT /*+ INDEX_DESC(tbl_board pk_board) */ * FROM tbl_board WHERE bno > 0;

---

ROWNUM

rownum은 SELECT 결과의 각 행마다 넘버링을 하는 것이다. rownum은 실제 데이터가 아니므로 어떤 데이터를 가져오느냐에 따라 Rownum이 바뀌게 된다. 

SELECT ROWNUM rn, bno, title FROM tbl_board;

 

rownum은 정렬 전에 각 데이터마다 부여되는 것으로, 정렬은 rownum이 부여된 뒤에 수행된다.

하지만 Index를 이용해 접근한다면 정렬 과정이 따로 없으므로 인덱스를 통해 테이블에 접근한 뒤 바로 Rownum이 부여된다.

 

SELECT /*+ INDEX_DESC(tbl_board pk_board) */
	ROWNUM rn, bno, title, content
FROM tbl_board;

---

Pagination

한 페이지마다 10개의 데이터를 출력하려 한다면, WHERE절에 ROWNUM 조건을 추가하면 된다.

SELECT /*+ INDEX_DESC(tbl_board pk_board) */
	ROWNUM rn, bno, title, content
FROM tbl_board
WHERE ROWNUM <= 10;

인라인뷰에서 WHERE-ROWNUM 조건을 이용하여 Pagination을 구현할 수 있다.

 

아래 SQL문은 Rownum이 11번부터 20번까지의 데이터를 가져오는 것이다.

SELECT rn, 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;

 

---

삽질

원래는 맥에서 오라클 데이터베이스 지원이 안 돼서, 오라클 클라우드 데이터베이스를 이용하고 있었다.

그런데 자꾸만 힌트를 무시하고 SQL을 처리해서 결국 예전에 쓰던 윈도우 노트북에 오라클 데이터베이스를 설치해서

데이터베이스 서버로 이용하기로 결정했다.

 

(데이터베이스 수업 실습때 이미 여러번 오라클 데이터베이스 설치를 시도했지만 모두 실패했던 경험이 있다.)

 

그래서 그냥 포맷하고 윈도우 재설치해서 사용하는 중이다. 진작 이렇게 할걸.