[코드로 배우는 스프링 웹 프로젝트] 12강. 오라클 데이터베이스 페이징 처리 (Oracle/SQL/페이징처리)

해당 프로젝트는 코드로 배우는 스프링 웹 프로젝트(개정판)을 기반으로 진행됩니다.

---

12.1 실행 계획 (execution plan)

오라클의 페이징 처리를 위해서는 실행 계획(execution plan)을 알아야 한다. 실행 계획이란, SQL을 데이터베이스에서 어떻게 처리 할 것인가를 의미한다.

SQL이 데이터베이스에 전달되면 데이터 베이스는 아래와 같은 과정을 거쳐 처리된다.

1) SQL 파싱 단계 : SQL 구문에 오류가 있는지 SQL을 실핼해야 하는 대상 객체(테이블, 제약 조건, 권한 등)가 존대하는지를 검사함.

2) SQL 최적화 단계 : SQL이 싱행되는데 필요한 비용(cost)을 계산하여 이 값으로 어떤 방식으로 실행하는 것이 가장 좋을지 판단하는 '실행 계획'을 세움

3) SQL 실행 단계 : 세워진 실행 계획을 통해서 메모리상에서 데이터를 읽거나 물리적인 공간에서 데이터를 로딩하는 등의 작업을 함.

SQL Developer에서 실행 계획 확인이 가능하다.

실행 계획은 "안쪽에서 바깥쪽으로, 위에서 아래로" 읽으면 된다.

여기서 FULL의 의미는 모든 데이터를 스캔(scan) 했다는 의미이다. (PK_BOARD를 이용하여 접근)

---

12.2 INDEX

데이터가 수백만개와 같이 많은 상태이면 정렬 작업 시간에 영향을 끼치게 된다. 이를 해결하는 일반적인 해결책은 '인덱스'를 이용해서 정렬을 생략하는 방법이다. '인덱스'라는 존재가 이미 정렬된 구조를 의미한다. 따라서, 별도의 정렬을 하지 않는다.

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

 

<실행결과>

기존 0.154초 보다 빨라졌음을 확인할 수 있다.

<주의깊게 봐야할 부분>

1) SORT를 하지 않음.

2) TBL_BOARD를 바로 접근하는 것이 아니라 PK_BOARD를 이용해서 접근한 점

3) RANGE SCAN DESCENDING, BY INDEX ROWID로 접근했다는 점

---

12.2.1 PK_BOARD라는 인덱스

tbl_board 테이블을 생성했을 때의 SQL

create table tbl_board ( 
    bno number(10,0), 
    title varchar2(200) not null, 
    content varchar2(2000) not null, 
    writer varchar2(50) not null, 
    cdate date default sysdate, 
    udate date default sysdate 
); 

alter table tbl_board add constraint pk_board primary key(bno);

테이블을 생성할 때 제약 조건으로 PK를 지정하여 PK 이름이 'pk_board'라고 지정하였다. 데이터베이스의 PK는 흔히 말하는 '식별자'의 의미와 '인덱스'의 의미를 가진다. PK를 부여하면 '인덱스'라는 존재(객체)가 만들어진다.

'인덱스'는 말 그대로 '색인'을 뜻한다.

색인이란?

  도서 맨 뒤쪽에 정리되어 있는 색인. 즉, 이를 보고 원하는 내용을 쉽게 찾을 수 있도록 안내해주는 것

 

위를 예로 들면, tbl_board 테이블은 bno라는 컬럼을 기준으로 인덱스를 생성하게된다.

왼쪽을 보면 bno값이 순서대로 정렬되어 있는 것을 볼 수 있다. 오른쪽의 테이블은 순서가 뒤엉켜있다. 인덱스와 실제 데이터를 연결하는 것은 ROWID 이다. ROWID는 데이터베이스 내의 주소에 해당하는데 모든 데이터는 자신만의 고유한 주소를 가진다.

즉, 위 이미지와 같이 안쪽을 먼저 보면 PK_BOARD 인덱스를 이용하여 100번 데이터의 ROWID를 찾고, 바깥쪽을 보면 'BY INDEX ROWID'라고 되어있는 말 그대로 ROWID를 통해서 테이블에 접근하게 된다. 

 

---

12.3 인덱스를 이용하는 정렬

인덱스에서 가장 중요한 개념 중 하나는 '정렬이 되어 있다는 점'이다.

INDEX_ASC(오름차순), INDEX_DESC(내림차순)를 주로 가장 많이 사용하는데 인덱스의 'order by'를 위해 사용한다고 생각하면 된다.

사용 예는 아래와 같다.

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

select /* + INDEX_DESC(tbl_board pk+board) */ * from tbl_board where bno > 0;

.

반응형

---

12.4 ROWNUM과 인라인뷰

페이징 처리를 위해서는 필요한 만큼의 데이터를 가져오는 방식이 필요하다.

오라클 데이터 베이스는 페이지 처리를 위래서 ROWNUM이라는 특별한 키워드를 사용해서 데이터에 순번을 붙여 사용한다. 쉽게 말해 ROWNUM은 SQL이 실행된 결과에 넘버링을 해준다고 생각하면 된다! (실제 데이터가 아니라 테이블에서 데이터를 SELECT한 후에 처리되는 값이므로, 상황에 따라 값이 매번 달라질 수 있다.)

<실행 예시>

select rownum rn, bno, title from tbl_board;

BNO 26번글이 1번째로 조회된 값임을 확인할 수 있다.

위 이미지와 같이 아무 조건 없이(FULL SCAN) tbl_board 테이블에 접근하고 각 데이터에 ROWNUM을 적용하면 정렬되지 않은 상태 그대로 결과값이 출력된다. (가장 먼저 가져올 수 있는 데이터 순서대로 가져오는 것임!)

<FULL 힌트를 통해 전체 데이터를 조회하고, 다시 정렬 했을 때>

select /*+ FULL(tbl_board) */
rownum rn, bno, title
from tbl_board where bno>0
order by bno;

BNO 26번 데이터는 1번째로 접근되었지만, 정렬과정에서 8번째로 밀렸다.

 

---

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

PK_BOARD 인덱스를 통해서 접근한다면 아래와 같은 과정으로 접근하게 된다.

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

1번 과정에서 이미 정렬되어있기 때문에 26번의 접근 순서는 1번이 아니라 1번보다 뒤에 조회될 것(ex. 8번째) 이다.

* INDEX_ASC를 통해 이미 오른차순으로 정렬되어 있기 때문에 가장 먼저 찾는 데이터 부터 ROWNUM이 할당된다. * 

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

BNO의 오름차순으로 조회되었다.

 

<반대로, INDEX를 내림차순으로 정렬 후 조회하여 ROWNUM 부여하기>

BNO 마지막 값이 1번째로 조회되었다.

---

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

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

BNO값이 가장 높은 20개의 데이터만 출력됐다.

1페이지를 위와 같이 구했다면, 2페이지 데이터는 어떻게 조회할까?

ROWNUM 21~40번째 데이터를 가져온다고하고, WHERE절을 수정해보았다.

결과는 아무 결과도 나오지 않았다. 왜?

tbl_board에서 처음에 가져오는 ROWNUM 값이 1이다.

즉, 테이블에서 데이터를 조회하면 무조건 ROWNUM은 1부터 시작하고, 이는 WHERE절(ROWNUM이 21~40 데이터를 조회한다.)에 맞지 않으므로 무효화된다.

즉, 이후에 다시 다른 데이터를 가져오면 또 새로운 데이터이므로 ROWNUM은 1이되고 이는 또 WHERE절에 의해 무효화된다. 이 과정이 반복되고, 결과는 아무것도 나오지 않는다. ROWNUM 조건은 1이 반드시 포함되어야 한다.

---

12.4.3 인라인뷰(In-line View) 처리

위와 같이 다음페이지의 데이터를 출력하여면 인라인뷰 라는 것을 이용해야 한다.

인라인뷰란? 'SELECT문 안쪽 FROM에 다시 SELECT문'

 

<인라인뷰를 적용한 2페이지 데이터 가져오기>

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

먼저, 40개의 데이터를 조회 후 rn값이 20 초과인 데이터만 다시 조회하기

위 동작방식을 정리하면 아래와 같다.

1. 필요한 순서로 정렬된 데이터에 ROWNUM을 붙인다.
2. 처음부터 해당 페이지 데이터를 'ROWNUM <=40'과 같은 조건을 이용해서 구한다.
3. 구해놓은 데이터를 하나의 테이블처럼 간주라고 인라인뷰로 처리한다.
4. 인라인뷰에서 필요한 데이터만을 재추출한다.

---

 

다음 시간에는 오늘 공부한 내용으로 MyBatis와 스프링에서 페이징 처리를 해보겠다!