코드로 배우는 스프링 웹 프로젝트-12.오라클 데이터베이스 페이징 처리

코드로 배우는 스프링 웹 프로젝트-12.오라클 데이터베이스 페이징 처리

코드로 배우는 스프링 웹 프로젝트 - 개정판

2019년 7월 10일 인쇄판

 

 

Part3. 기본적인 웹 게시물 관리

Chapter12. 오라클 데이터베이스 페이징 처리

 

구현된 기능들 중 가장 미숙한 부분이 목록 페이지이다.

기본적으로 페이징 처리가 필요한데, 상식적으로 수많은 데이터를 한 페이지에서 보여주면 처리 성능에 영향을 미친다.

 

일반적으로 페이징 처리는 크게 번호를 이용하거나 계속 보기 형태로 구현됟나.

번호 페이징 처리는 전통적인 방식이고 계속 보기는 Ajax와 앱이 등장한 후 무한 스크롤이나 더 보기 형태로 구현된다.

 

12.1 order by의 문제

 

데이터 양이 많을수록 정렬은 많은 리소스를 소모한다.

데이터베이스를 이용할 때 앱에서 가장 신경쓰는 부분은 1) 빠르게 처리되는 것 2) 필요양만큼만 데이터를 가져오는 것이다.

 

빠르게 동작하는 SQL을 위해선 먼저 order by는 가능한 하지 않는게 좋다. 데이터가 많은 경우 엄청난 성능의 저하를 가져오기 때문이다.

 

 

12.1.1 실행 계획과 order by

 

오라클의 페이징 처리를 제대로 이해하기 위해 반드시 알아야 할 것은 실행 계획이다. 말 그대로 SQL을 DB에서 어떻게 처리할 것인가?에 대한 것으로 SQL이 데이터베이스에 전달되면 데이터베이스는 여러 단계를 거쳐 해당 SQL을 어떤 순서, 방식으로 처리할 건지 계획을 세우게 된다.

 

SQL 파싱 -> 최적화 -> 실행

 

파싱 단계에선 SQL 구문에 오류가 있는지, 대상 객체가 존재하는지 검사한다. 최적화 단계에선 SQL 실행에 필요한 비용을 계산한다. 이 계산된 값을 기초로 해서 "실행 계획"을 세우게 되는 것이다.

 

실행 단계에선 계획을 통해 메모리상에서 데이터를 읽거나 물리적인 공간에 데이터를 로딩하는 등의 작업을 하게 된다.

개발자들은 SQL Plus 등을 이용해 특정 SQL에 대한 실행 계획을 알아볼 수 있다.

 

SQL developer에선 실행 계획을 볼 수 있는 버튼이 있다. 방금 전 실행한 SQL문이 어떻게 처리된 것인지 트리 구조로 보여준다. 흔히 SQL 튜닝이라고 하는 작업은 이를 보고 어떤 방식이 더 효과적인지 판단해서 수정하게 된다.

가장 간단하게 보는 방법은 안쪽에서 바깥쪽으로, 위에서 아래로 보는 것이다.

 

테스트를 위해 데이터가 좀 더 많아지도록 한다.

 

insert into tbl_board(bno, title, content, writer)
(select seq_board.nextval, title, content, writer from tbl_board);

위 구문은 insert 시마다 2배씩 데이터가 늘어난다.

 

데이터가 많아지면 정렬에 그만큼 시간을 소모하게 된다.

 

 

12.2 order by 보다는 index

 

정렬 시간에 가장 일반적인 해결책은 인덱스를 이용해 정렬을 생략하는 방법이다. 결론부터 말하자면 인덱스라는 존재가 이미 정렬된 구조이므로 이를 이용해 별도의 정렬을 하지 않는 방법이다.

 

PK_BOARD를 이용할 경우 SORT를 하지 않고 TBL_BOARD를 바로 접근하는 것이 아니라 PK_BOARD를 이용해 접근하며 RANGE SCAN DESCENDING, BY INDEX ROWID로 접근할 수 있다.

 

 

12.2.1 PK_BOARD 인덱스

 

테이블 생성 시 제약 조건으로 PK를 지정하고 PK 이름을 PK_BOARD라고 지정했다.

PK는 상당히 중요한데, 흔히 말하는 식별자의 의미와 인덱스의 의미를 지닌다.

 

인덱스는 말 그대로 색인으로 구조상으로 인덱스라는 객체가 만들어진다. TBL_BOARD는 BNO 칼럼을 기준으로 인덱스를 생성한다. 테이블은 마치 책장에 책을 막 넣은 것처럼 중간에 순서가 섞여 있는 것이 대부분이고, index가 정렬된 구조를 담당한다.

인덱스와 실제 테이블을 연결하는 고리는 ROWID라는 존재로, 데이터베이스 내 주소에 해당한다. 모든 데이터는 자신만의 주소를 가지고 있다.

 

SQL문을 통해 bno 값이 100번인 데이터를 찾고자 할 때 SQL은 where bno = 100과 같은 조건을 갖게 된다. 이를 처리하는 DB 입장에선 tbl_board라는 책에서 bno가 100인 데이터를 찾아야 하는데, 색인으로 내용을 찾으면 FULL SCAN 방식보다 더 빠르게 데이터를 찾을 수 있다.

먼저 인덱스를 이용해 100번 데이터가 어디있는지 ROWID를 찾아내고, ROWID를 통해 테이블에 접근하게 된다.

 

 

 

12.3 인덱스를 이용하는 정렬

 

인덱스의 중요 개념 중 하나는 정렬이 되어 있다는 것이다. 정렬이 이미 되어 있으므로 데이터를 찾아내서 이들을 SORT하는 과정을 생략할 수 있다.

bno의 역순으로 정렬한 결과를 원한다면 이미 정렬된 인덱스를 이용해 뒤에서부터 찾아 올라가는 방식을 이용할 수 있다.

 

 

 

인덱스를 역순으로 찾기 때문에 가장 먼저 찾은 bno 값은 가장 큰 값을 가진 데이터가 된다. 이후에는 테이블에 접근해서 데이터를 가져오게 되는데 이를 반복하면 정렬을 하지 않아도 동일하게 정렬된 결과를 볼 수 있다.

 

 

12.3.1 인덱스와 오라클 힌트

 

웹페이지에선 주로 시간 역순으로 정렬된 결과를 보여준다. 최신 데이터가 가장 중요하기 때문이다.

오라클은 select 문을 전달할 때 hint라는 것을 사용할 수 있다. 힌트는 말 그대로 DB에 내가 지금 전달한 select 문을 이렇게 처리해 주면 좋겠다는 힌트이다. select문을 어떻게 처리하는지에 대한 얘기일 뿐이므로 힌트 구문에서 에러가 나도 전혀 SQL 실행에 지장을 주지 않는다.

 

게시물 목록은 반드시 시간 역순으로 나와야 하므로 order by bno desc 구문을 추가할 수 있다. 문제는 위 조건은 DB 상황에 따라 테이블의 모든 데이터를 정렬하는 방식으로도 동작할 수 있다.

 

select * from tbl_board order by bno desc;

select /*+INDEX_DESC(tbl_board pk_board) */*
from tbl_board;

위 두 SQL은 동일한 결과를 생성한다.

두번째 select문에서 order by 조건이 없어도 동일한 결과가 나온다. 힌트를 부여해 실행 계획이 이를 활용하고 있는 것이다.

 

12.3.2 힌트 사용 문법

 

SELECT
/*+ Hint name (param...) */ column name,.......
FROM
 table_name
 ......

위와 같은 구문을 사용한다. 힌트 구문은 /*+로 시작하고 */로 마무리된다.

 

 

12.3.3 FULL 힌트

 

해당 select문을 실행할 떄 테이블 전체를 스캔할 것으로 명시하는 힌트. 

 

select /*+ FULL(tbl_board) */* from tbl_board order by bno desc;

 

 

12.3.4 INDEX_ASC, INDEX_DESC 힌트

 

select /*+ INDEX_ASC(tbl_board pk_board) */ * from tbl_board where bno > 0;

위와 같이 주로 테이블 이름과 인덱스 이름을 같이 파라미터로 사용한다.

 

 

12.4 ROWNUM과 인라인뷰

 

오라클 DB는 페이지 처리를 위해 ROWNUM이라는 키워드로 데이터에 순번을 붙인다. 쉽게 생각해 SQL 실행 결과에 넘버링을 해주는 것이다. 실제 데이터가 아니라 데이터 추출 후 처리되는 변수로 상황에 따라 값이 매번 달라진다.

 

아무 조건을 적용하지 않고 tbl_board 테이블에 접근해 각 데이터에 ROWNUM을 적용하면 데이터가 섞여져 나온다.

 

select rownum rn, bno, title from tbl_board;

위에 order by 를 해도 뒤죽박죽으로 정렬된다. 즉 정렬은 데이터를 가져온 후에 뒤늦게 처리되는 것이다.

 

 

12.4.1 인덱스를 이용한 접근 시 ROWNUM

 

PK_BOARD 인덱스를 통해 접근한다면 다음과 같은 과정으로 접근한다

 

1) PK_BOARD 인덱스를 이용해 테이블 접근

2) 접근 데이터에 ROWNUM 부여

 

 

select /*+ INDEX_ASC(tbl_board pk_board) */
rownum rn, bno, title, content
from tbl_board;

 

ROWNUM은 데이터에 접근하는 순서이므로 가장 먼저 접근하는 데이터가 1번이 되는데 이를 이용해 테이블을 bno의 역순으로 접근해서 bno값이 가장 큰 데이터가 ROWNUM 1이 되도록할 수 있다.

 

select
/*+ INDEX_DESC(tbl_board pk_board) */
rownum rn, bno, title, content
from tbl_board
where bno > 0;

 

 

12.4.2 페이지 번호 1, 2의 데이터

 

한 페이지당 10개의 데이터를 출력한다고 가정하면 ROWNUM 조건을 WHERE 구문에 추가해 다음과 같이 작성할 수 있다.

 

select /*+ INDEX_DESC(tbl_board pk_board) */
rownum rn, bno, title, content
from tbl_board
where rownum <=10;

그러나 2페이지 구현을 위해 아래를 실행해도 아무런 결과가 나오지 않는다.

 

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 에 처음으로 나오는 ROWNUM이 1이라는 것. TBL_BOARD에서 데이터를 찾고 ROWNUM 값이 1이 된 데이터는 where 조건에 의해 무효화된다.

이후에 다시 다른 데이터를 가져오면 새로운 데이터가 1이 되므로 결과가 아무것도 나오지 않게 된다. 이러한 이유로 SQL 작성시 ROWNUM 조건은 반드시 1을 포함해야 한다.

 

 

12.4.3 인라인 뷰 처리

 

인라인뷰는 SELECT 안쪽 FROM에 다시 SELECT문으로 이해하면 된다.

DB는 테이블이나 인덱스와 같이 뷰라는 개념이 존재한다. 이러한 뷰의 작성을 별도로 작성하지 않고 말그대로 FROM 구문 안에 바로 작성하는 것이 인라인 뷰이다.

 

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;