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

목록 페이지는 기본적으로 페이징 처리가 필요하다.

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

 

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

번호를 이용한 페이징 처리는 과거 웹 초기부터 이어오던 방식이고,

계속 보기는 Ajax와 앱이 등장한 이후에 무한 스크롤이나 더 보기와 같은 형태로 구현된다.

 

 

12.1 order by의 문제

데이터베이스를 이용할 때 웹이나 애플리케이션에 가장 신경 쓰는 부분은 1) 빠르게 처리 되는 것, 2) 필요한 양만큼만 데이터를 가져오는 것이다.

예를 들어, 거의 모든 웹페이지에서 페이징을 하는 이유는 최소한의 필요한 데이터만을 가져와서 빠르게 화면에 보여 주기 위함이다.

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

 

빠르게 동작하는 SQL을 위해서는 먼저 order by 를 이용하는 작업을 가능하면 하지 말아야 한다.

order by는 데이터가 많은 경우에 엄청난 성능의 저하를 가져오기 때문에 1) 데이터가 적은 경우와 2) 정렬을 빠르게 할 수 있는 방법이 있는 경우가 아니라면 order by는 주의해야만 한다.

 

 

12.1.1 실행 계획과 order by

오라클의 페이징 처리를 제대로 이해하기 위해서 반드시 알아두어야 하는 것이 실행 계획이다.

실행 계획은 말 그대로 'SQL을 데이터베이스에서 어떻게 처리 할 것인가?'에 대한 것이다.

SQL이 데이터베이스에 전달되면 데이터베이스는 여러 단계를 거쳐서 해당 SQL을 어떤 순서와 방식으로 처리할 것인지 계획을 세우게 된다.

 

데이터베이스에 전달된 SQL문

 

• SQL 파싱 단계에서는 SQL 구문에 오류가 있는지 SQL을 실행해야 하는 대상 객체(테이블, 제약조건, 권한 등)가 존재하는지를 검사한다.
• SQL 최적화 단계에서는 SQL이 실행되는데 필요한 비용을 계산하게 된다. 이 계산된 값을 기초로 해서 어떤 방식으로 실행하는 것이 가장 좋다는 것을 판단하는 실행 계획을 세우게 된다.
• SQL 실행 단계에서는 세워진 실행 계획을 통해서 메모리상에서 데이터를 읽거나 물리적인 공간에서 데이터를 로딩하는 등의 작업을 하게 된다.

 

예를 들어, 게시물 번호의 역순으로 출력하라는 처리를 해보겠다.

실행 계획을 보면 트리 구조로 방금 전 실행한 SQL이 어떻게 처리된 것인지를 알려준다.

흔히 SQL 튜닝이라고 하는 작업은 이를 보고 어떤 방식이 더 효과적인지를 판단해서 수정하게 된다.

 

가장 간단하게 실행 계획을 보는 방법은 '안쪽에서 바깥쪽으로, 위에서 아래로' 봐주면 된다.

위 그림의 내용을 해석하자면 TBL_BOARD 테이블을 FULL 로 접근하고 정렬했다는 것을 의미한다.

FULL 이라는 의미는 테이블의 내의 모든 데이터를 스캔했다는 의미이다.

실행 계획을 세우는 것은 데이터베이스에서 하는 역할이기 때문에 데이터의 양이나 제약 조건 등의 여러 상황에 따라서 데이터베이스는 실행 계획을 다르게 작성한다.

 

테스트를 위해서 데이터가 좀 많아지도록 아래의 SQL 을 여러 번 실행해서 데이터를 수 백 만개로 만든 후에 커밋 한다.

결과를 보면 insert 문을 실행할 때마다 2배씩 데이터가 늘어나게 된다.

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

 

---

12.2 order by 보다는 인덱스

데이터가 많은 상태에서 정렬 작업이 문제가 된다는 사실을 알았다면, 이 문제를 어떻게 해결해야 하는지 살펴보자.

가장 일반적인 해결책은 인덱스를 이용해서 정렬을 생략하는 방법이다.

인덱스라는 존재가 이미 정렬된 구조 이므로 이를 이용해서 별도의 정렬을 하지 않는 방법이다.

 

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 입장에선 tb1_board라는 책에서 bno가 100인 데이터를 찾아야 하는데, 색인으로 내용을 찾으면 FULL SCAN 방식보다 더 빠르게 데이터를 찾을 수 있다.

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

 

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

 

---

12.3 인덱스를 이용하는 정렬

인덱스의 중요 개념 중 하나는 정렬이 되어 있다는 것이다.

정렬이 이미 되어 있으므로 데이터를 찾아내서 이들을 SORT하는 과정을 생략할 수 있다.

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

 

12.3.1 인덱스와 오라클 힌트(hint)

웹 페이지의 목록은 주로 시간의 역순으로 정렬된 결과를 보여준다.

최신 데이터가 가장 중요하기 때문에 시간의 역순으로 정렬해서 최신 게시물들을 보여주게 된다.

 

오라클은 select 문을 전달할 때 '힌트' 라는 것을 사용할 수 있다.

힌트는 말 그대로 데이터베이스에 '지금 내가 전달한 select 문을 이렇게 실행해 주면 좋겠다' 라는 힌트다.

 

위의 두 SQL은 동일한 결과를 생성하는 SQL이다.

두번째 select 문은 order by 조건이 없어도 동일한 결과가 나온 것에 주목해야 한다.

 

select 문에서 힌트를 부여해 힌트의 내용이 실행 계획에서 이를 활용하고 있는 것을 확인할 수 있다.

 

 

12.3.2 힌트 사용 문법

select문을 작성할 때 힌트는 잘못 작성되어도 실행할 때는 무시되기만 하고 별도의 에러는 발생하지 않는다.

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

힌트 구문은 /*+ 로 시작하고 */로 마무리된다.

 

 

12.3.3 FULL 힌트

힌트 중에는 해당 select 문을 실행할 때 테이블 전체를 스캔할 것으로 명시하는 FULL 힌트가 있다.

FULL 힌트는 테이블의 모든 데이터를 스캔하기 때문에 데이터가 많을 때는 상당히 느리게 실행된다.

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

실행 계획을 보면 tb1_board를 full로 접근하고, 다시 sort 가 적용된 것을 볼 수 있다.

 

 

12.3.4 INDEX_ASC, INDEX_DESC 힌트

흔히 목록 페이지에서 가장 많이 사용하는 힌트는 인덱스와 관련된 'INDEX_ASC, INDEX_DESC' 힌트다.

ASC/DESC에서 알 수 있듯이 인덱스를 순서대로 이용할 것인지 역순으로 이용할 것인지를 지정한다.

인덱스 자체가 정렬을 해 둔 상태임로 이를 통해서 SORT 과정을 생략하기 위한 용도다.

 

INDEX_ASC/DESC 힌트는 테이블 이름과 인덱스 이름을 같이 파라미터로 사용한다.

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

INDEX_ASC/DESC를 이용하는 경우에는 동일한 조건의 order by 구문을 작성하지 않아도 된다.

 

---

12.4 ROWNUM과 인라인뷰

필요한 만큼의 데이터를 가져오는 방식에 대해 학습하겠다.

오라클 데이터베이스는 페이지 처리를 위해서 ROWNUN이라는 키워드를 사용해서 데이터에 순번을 붙여 사용한다.

쉽게 생각해 SQL 실행 결과에 넘버링을 해주는 것이다.

모든 SELECT 문에는 ROWNUM이라는 변수를 이용해서 해당 데이터가 몇 번째로 나오는지 알아낼 수 있다.

ROWNUM은 실제 데이터가 아니라 테이블에서 데이터를 추출한 후에 처리되는 변수이므로 상황에 따라서 그 값이 매번 달라질 수 있다.

ROWNUM은 데이터를 가져올 때 적용되는 것이고, 이 후에 정렬되는 과정에서 ROWNUM이 변경되지 않는다는 것이다. 즉 정렬은 나중에 처리된다는 의미이기도 하다.

 

 

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

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

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

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

 

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

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

 

 

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

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

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

SQL의 실행 결과를 보면 가장 높은 번호의 게시물 10개만이 출력되는 것을 볼 수 있다.

1페이지 데이터를 구했다면 동일한 방식으로 2페이지 데이터를 구할 수 있을 것이라고 생각하지만 결과를 구할 수 없다.

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

위의 SQL을 보면 rownum이 10보다 크고 20보다 작거나 같은 데이터들을 가져올 것이라고 기대하지만 실제로는 아무 결과가 나오지 않는다.

실행 계획은 안쪽에서부터 바깥쪽으로, 위에서부터 아래로 보게 되므로 위의 실행 계획은 우선 ROWNUM >10, 데이터들을 찾게 된다.

문제는 TB1_BOARD에 처음으로 나오는 ROWNUM의 값이 1이라는 것이다.

TB1_BOARD에서 데이터를 찾고 ROWNUM 값이 1이 된 데이터는 where 조건에 의해서 무효화된다.

이후에 다시 다른 데이터를 가져오면 새로운 데이터가 첫 번째 데이터가 되므로 다시 ROWNUM은 1이 된다.

이 과정이 반복되면 ROWNUM 값은 항상 1로 만들어지고 없어지는 과정이 반복되므로 테이블의 모든 데이터를 찾아내지만 결과는 아무것도 나오지 않게 된다.

이러한 이유로 SQL 작성시 ROWNUM 조건은 반드시 1을 포함해야 한다.

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

달라진 조건은 rownum 조건이 1을 포함하도록 변경한 것 뿐이다.

위의 SQL 결과는 위와 같이 역순으로 데이터를 20개 가져온다.

 

 

12.4.3 인라인뷰 처리

데이터 20개를 가져오는데 성공했지만, 1페이지의 내용이 같이 출력되는 문제가 있으므로 이 문제를 수정해야 한다.

이 문제를 해결하기 위해서는 인라인뷰라는 것을 이용한다.

인라인뷰는 SELECT문 안쪽 FROM에 다시 SELECT문 으로 이해할 수 있다.

인라인뷰는 논리적으로 어떤 결과를 구하는 SELECT문이 있고 그 결과를 다시 대상으로 삼아서 SELECT 하는 것이다.

데이터베이스에는 테이블이나 인덱스와 같이 뷰라는 개념이 존재한다.

뷰는 복잡한 SELECT 처리를 하나의 뷰로 생성하고, 사용자들은 뷰를 통해서 복잡하게 만들어진 결과를 마치 하나의 테이블처럼 쉽게 조회한다는 개념이다.

이러한 뷰의 작성을 별도로 작성하지 않고 말그대로 FROM 구문 안에 바로 작성하는 것이 인라인 뷰이다.

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

 

 

---

출처 : 코드로 배우는 스프링 웹 프로젝트 [구멍가게 코딩단]