[3-ch12 Spring web 게시판] 페이징 처리, 힌트(hint), ROWNUM & 인라인뷰 적용

 

order by의 문제

 

데이터의 양이 많을수록 정렬이라는 작업은 많은 리소스를 소모한다. 데이터베이스를 이용할 때 웹이나 애플리케이션에서 가장 신경쓰는 부분은 1) 빠르게 처리되는 것, 2) 필요한 양만큼만 데이터를 가져오는 것이다. 

 

예를들어, 거의 모든 웹페이지에서 페이징을 하는 이유는 최소한의 필요한 데이터만을 가져와 빠르게 화면에 보여주기 위함이다. 만일 수백 만개의 데이터를 매번 정렬해야 하는 상황이라면 사용자는 정렬된 결과를 볼 때까지 오랜 시간을 기다려야만 하고, 특히 웹에서 동시에 여러 명의 사용자가 정렬이 필요한 데이터를 요청하게 된다면 시스템에는 많은 부하가 걸리게 되고 연결 가능한 커넥션의 개수가 점점 줄어서 서비스가 멈추는 상황을 초래하게 된다. 

 

 

 

실행 계획과 order by 

 

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

 

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

 

 

SQL 파싱 단계에서는 SQL 구문에 오류가 있는지 SQL을 실행해야 하는 대상 객체(테이블, 제약 조건, 권한 등)가 존재하는지 검사하게 된다. 

 

SQL 최적화 단계에서는 SQL이 실행되는데 필요한 비용(cost)등을 계산하게 된다.이 계산된 값을 기초로 해서 어떤 방식으로 실행하는 것이 가장 좋다는 것을 판단하는 '실행 계획(execuion plan)'을 세우게 된다.

 

SQL 실행 단계에서는 세워진 실행 계획을 통해서 메모리상에서 데이터를 읽거나 물리적인 공간에서 데이터를 로딩하는 등의 작업을 하게 된다. 개발자들은 도구를 이용하거나 SQL Plus 등을 통해 특정한 SQL에 대한 실행 계획을 알아볼 수 있다. 

 

 

 

 

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

 

 

 

 

 

더미 데이터 추가

 

테스트를 위해 데이터가 좀 더 많아지도록 아래의 SQL을 여러 번 실행해 데이터수를 늘릴 수 있다. insert문을 여러 번 실행하게 되면 현재 tbl_board 테이블의 데이터 수만큼 다시 insert가 진행된다. 즉) 2배씩 데이터가 늘어나게 되는 것.

 

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

 

 

 

 

 

order by 보다는 인덱스

 

데이터가 많아지면 생기는 문제를 해결하기 위한 가장 일반적인 방법은 '인덱스(index)'를 이용해 정렬을 생략하는 방법이다. '인덱스'라는 존재가 이미 정렬된 구조이므로 이를 이용해 별도의 정렬을 하지 않는 방법이다. 처음 tbl_board 테이블을 생성했을 때 SQL을 살펴보자.

 

 

 

 

테이블을 생성할 때 제약 조건으로 PK를 지정하고 PK의 이름이 'pk_board'라고 지정하였다. 데이터베이스에서 PK는 상당히 중요한 의미를 가지는데, 흔히 말하는 '식별자'의 의미와 '인덱스'의 의미를 가진다.

 

'인덱스'는 말 그대로 '색인'이다. 색인을 이용하면 사용자들은 책 전체를 살펴볼 필요 없이 색인을 통해서 자신이 원하는 내용이 책의 어디에 있는지 알 수 있다. 데이터베이스에서 인덱스를 이해하는 가장 쉬운 방법은 데이터베이스의 테이블을 하나의 책이라고 생각하고 어떻게 데이터를 찾거나 정렬하는지를 생각하는 것이다. 색인은 사람들이 쉽게 찾아볼 수 있게 알파벳 순서나 한글 순서로 정렬한다. 이를 통해 원하는 내용을 위에서부터 혹은 반대로 찾아나가는데 이를 '스캔(scan)'한다고 표현한다. 

 

 

tbl_board 테이블은 bno라는 칼럼을 기준으로 인덱스를 생성하게 된다. 인덱스에는 순서가 있기 때문에 그림으로 표현하면 아래와 같이 만들어 진다.

 

 

 

왼쪽은 인덱스이고 오른쪽은 실제 테이블이다. 왼쪽 그림을 보면 bno 값이 순서대로 정렬된 것을 볼 수 있다. 오른쪽은 실제 테이블의 모습이다. 인덱스와 실제 테이블을 연결하는 고리는 ROWID라는 존재인데, ROWID는 데이터베이스 내의 주소에 해당하며 모든 데이터는 자신만의 주소를 가지고 있다. SQL을 통해 bno 값이 100인 테이터를 찾으려면 SQL은 'where bno = 100'과 같은 조건을 주게 된다. 이를 처리하는 데이터베이스 입장에서는 tbl_board라는 책에서 bno값이 100인 데이터를 찾아야 한다. 만일 책이 얇아서 내용이 많지 않다면 속히 전체를 살펴보는 것이 더 빠를 것이다. 하지만 내용이 많고 색인이 존재한다면 당연히 색인을 찾고 색인에서 주소를 찾아서 접근하는 방식을 사용한다. 

 

 

 

인덱스를 이용하는 정렬, 오라클 힌트(hint)

 

인덱스에서 가장 중요한 개념 중 하나는 '정렬이 되어 있다는 점'이다. 이미 정렬되어 있는 상태이므로 데이터를 찾아내 이들을 SORT하는 과정을 생략할 수 있다. 

 

웹페이지의 목록은 주로 시간의 역순으로 정렬된 결과를 보여준다. 최신 데이터가 가장 중요하기 때문에 시간의 역순으로 정렬해서 최신 게시물들을 보여주게 된다. 이 경우 개발자의 입장에서는 정렬을 안 하는 방식으로 select문을 실행하고 싶어한다. 

 

오라클 select문을 전달할 때 '힌트(hint)'라는 것을 사용할 수 있다. 힌트는 말 그대로 DB에 '지금 내가 전달한 select문을 이렇게 실행해 주면 좋겠습니다.'라는 힌트이다. 힌트는 특이하게 select문을 어떻게 처리하는지에 대한 얘기일 뿐이므로 구문에서 에러가 나도 전혀 sql 실행에 지장을 주지 않는다. 따라서 힌트를 이용한 select문을 작성한 후에는 실행 계획을 통해 개발자가 원하는 대로 SQL이 실행되는 지를 확인하는 습관을 가져야 한다. 

 

select * from tbl_board order by bno desc;

--힌트 사용 (order by 없이도 역순으로 출력)
select /*+ INDEX_DESC(tbl_board pk_board ) */ * from tbl_board;

 

 

select문을 작성할 때 힌트의 문법은 다음과 같다. 

 

 

힌트 구문은 '/*+'로 시작하고 '*/'로 마무리된다. 힌트 자체는 SQL로 처리되지 않기 때문에 위의 그림처럼 칼럼명이 나오더라도 별도의 ','로 처리되지 않는다. 

 

 

 

 

ROWNUM과 인라인뷰

 

페이징 처리를 위해 역순으로 게시물의 목록을 조회하는 작업에 대해 알아봤다면, 이제는 전체가 아닌 필요한 만큼의 데이터를 가져오는 방법을 알아보겠다. 오라클 데이터베이스는 페이지 처리를 위해 ROWNUM이라는 특별한 키워드를 사용해서 데이터에 순번을 붙여 사용한다. ROWNUM은 쉽게 생각해서 SQL이 실행된 결과에 넘버링을 해준다고 생각하면 된다. 모든 SELECT 문에는 ROWNUM이라는 변수를 이용해 해당 데이터가 몇 번째로 나오는지 알아낼 수 있다. ROWNUM은 실제 데이터가 아니라 테이블에서 데이터를  추출한 후에 처리되는 변수이므로 그 값이 매번 달라질 수 있다. 

 

 

아무 조건을 적용하지 않고 tbl_board 테이블에 접근하려면 아래와 같이 작성할 수 있다.

select rownum rn, bno, title from tbl_board;

 

 

 결과값은 SQL에 아무런 조건이 없기 때문에 데이터는 테이블에 섞여 있는 상태 그대로 나오게 된다.

 

 

ROWNUM은 테이블에 존재하지 않고, 테이블에서 가져온 데이터를 이용해서 번호를 매기는 방식으로 위의 결과는 테이블에서 가장 먼저 가져올 수 있는 데이터들을 꺼내서 번호를 붙여주고 있다. 

 

 

 

인덱스를 이용한 접근 시 ROWNUM

 

ROWNUM의 의미가 테이블에서 데이터를 가져오면서 붙는 번호라는 사실을 알았다면 결국 문제는 테이블에 어떤 순서로 접근하는가에 따라서 ROWNUM의 값은 바뀔 수 있다는 뜻이다. 만약 PK_BOARD 인덱스를 통해서 접근한다면 다음과 같은 과정으로 접근한다.

 

1. PK_BOARD 인덱스를 통해서 테이블에 접근
2. 접근한 데이터에 ROWNUM 부여

 

 

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

따라서 위의 힌트를 주어 정렬하면 역순으로 정렬되는 걸 확인할 수 있다.

 

 

 

 

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

 

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

 

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

 

SQL의 실행 결과를 보면 가장 높은 번호의 10개만 출력되는 것을 볼 수 있는데, 이때 실행계획을 통해 PK_BOARD 인덱스를 역순으로 접근하는 것을 확인할 수 있다. WHERE 조건에서 특이하게 ROWNUM 조건은 테이블을 접근할 때 필터링 조건으로 적용된 것을 볼 수 있다. 

 

 

 

 

그렇다면 만약 10~20개를 출력하려고 한다면?? 

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

 

 

이렇게 작성하면 20개의 데이터를 가져오며 1페이지의 내용이 같이 출력되는 문제가 있다. 이 문제를 해결하기 위해 인라인뷰라는 것을 이용하는데, 인라인 뷰란? 'SELECT문 안쪽 FROM에서 다시 SELECT문' 으로 이해할 수 있다. 즉) 어떤 결과를 구하는 SELECT문이 있고, 그 결과를 다시 대상으로 삼아 SELECT하는 것이다. 

 

데이터베이스에서는 테이블이나 인덱스와 같이 뷰(VIEW)라는 개념이 존재한다. '뷰(View)'는 일종의 창문같은 개념으로 select 처리를 하나의 뷰로 생성하고, 사용자들은 뷰를 통해 복잡하게 만들어진 결과를 마치 하나의 테이블처럼 쉽게 조회한다는 개념이다. 인라인뷰는 이러한 작성을 별도로 작성하지 않고 말 그대로 FROM 구문 안에 바로 작성하는 형태이다. 

 

 

 

외부에서 SELECT문은 인라인뷰로 작성된 결과를 마치 하나의 테이블처럼 사용한다. 예를들어 위의 경우 20개의 데이터를 가져오는 SQL을 하나의 테이블처럼 간주하고 바깥쪽에서 추가적인 처리를 하는 것이다.

 

즉) 인라인뷰 안에 (FROM 절 안에) 더 포괄적인 값을 조회한다. 그 후 바깥 원래 SELECT문에서 세부적인 내용(인라인뷰에서 필요한 내용만)을 추출한다. 

 

 

인라인뷰를 적용한 2페이지 데이터의 처리는 아래와 같다. 

 

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;

 

 

 

 

 

위의 과정을 정리하면 다음과 같은 순이다.

 

1. 필요한 순서로 정렬된 데이터에 ROWNUM을 붙인다.

2. 처음부터 해당 페이지의 데이터를 'ROWNUM <= 30'과 같은 조건을 이용해서 구한다.

3. 구해놓은 데이터를 하나의 테이블처럼 간주하고 인라인뷰로 처리한다.

4. 인라인뷰에서 필요한 데이터만 남긴다.