구현된 기능들 중 가장 미숙한 부분은 목록 페이지 입니다.
목록 페이지는 기본적으로 페이징(pagination) 처리가 필요한데 상식적으로 생각해봐도 수 많은데이터를 한 페이지에 보여주면, 처리 성능에 영향을 미칩니다.
또한 브라우저에서도 역시 데이터의 양이나 처리 속도에 문제를 일으키게 됩니다.
일반적으로 페이징처리는 크게 번호를 이용하거나 "계속 보기"의 형태로 구현됩니다.
번호를 이용한 페이징 처리는 과거 웹 초기부터 이어오던 방식이고, "계속 보기"는 Ajax와 앱이 등장한 이후에 "무한스크롤"이나 "더 보기"와 같은 형태로 구현 됩니다.
예제에서 목록 페이지는 전통적인 번호를 이용하는 방식으로 처리 하게 됩니다.
오라클에서 페이징 처리하는 것은 Mysql에 비해 추가적인 지식이 필요하므로 이에 대한 학습을 선행해야 합니다.
12.1 order by 문제
프로그램을 이용해서 정렬을 해 본 적이 있다면 데이터의 양이 많을수록 정렬이라는 작업이 얼마나 많은 리소스를 소모하는지 알 수 있습니다.
데이터베이스는 경우에 따라서 수백만 혹은 천 만개 이상의 데이터를 처리하기 때문에 이 경우 정렬을 하게 되면 엄청나게 많은 시간과 리소스를 소모하게 됩니다.
데이터베이스를 이용할 때 웹이나 어플리케이션에 가장 신경쓰는 부분은
- 빠르게 처리되는것
- 필요한 양만큼만 데이터를 가져오는것
입니다.
예를들어 거의 모든 웹페이지에서 페이징을 하는 이유는 최소한의 필요한 데이터만을 가져와서 빠르게 화면에 보여주기 위함입니다.
만일 수백 만개의 데이터를 매번 정렬을 해야 하는 상황이라면 사용자는 정렬된 결과를 볼 때 까지 오랜 시간을 기다려야만 하고, 특히 웹에서 동시에 여러 명의 사용자가 정렬이 필요한 데이터를 요청하게 된다면 시스템에는 많은 부하가 걸리게 되고 연결 가능한 커넥션의 개수가 점점 줄어 서비스가 멈추는 상황을 초래하게 됩니다.
빠르게 동작하는SQL을 위해서는 먼저 order by를 이용하는 작업을 가능하면 하지 말아야 합니다.
order by 는 데이터가 많은 경우 엄청난 성능 저하를 가져오기 때문에
- 데이터가 적은 경우
- 정렬을 빠르게 할 수 있는 방법이 있는 경우
가 아니라면 order by 는 주의해야만 합니다.
12.1.1 실행 계획과 order by
오라클의 페이징 처리를 제대로 이해하기 위해서 반드시 알아두어야 하는 것이실행계획(excution plan)입니다.
실행계획은 말 그대로 SQL을 데이터베이스에서 어떻게 처리할 것인가에 대한 것입니다.
SQL이 데이터베이스에 전달되면 데이터베이스는 여러 단계를 거쳐 해당 SQL을 어떤 순서와 방식으로 처리할 것인지 계획을 세우게 됩니다.
데이터 베이스에 전달된 SQL문은 아래와 같은 과정을 거쳐 처리됩니다.
SQL 파싱 단계에서는
SQL 구문에 오류가 있는지,SQL을 실행해야 하는 대상객체(테이블, 제약 조건, 권한 등)가 존재하는지를 검사 합니다.
SQL 최적화 단계에서는
SQL이 실행되는데 필요한 비용(cost)을 계산하게 됩니다.
이 계획된 값을 기초로 해서 어떤 방식으로 실행하는 것이 가장 좋다는 것을 판단하는 실행 계획(excution plan)을 세우게 됩니다.
SQL 실행 단계에서는 세워진 실행 계획을 통해서 메모리상에서 데이터를 읽거나 물리적인 공간에서 데이터를 로딩하는 등의 작업을 하게 됩니다.
개발자들은 도구를 이용하거나 SQL Plus를 이용해서 특정한 SQL에 대한 실행 계획을 알아볼 수 있습니다.
SQL Developer에서는 간단히 버튼을 클릭해서 실행 계획을 확인 할 수 있습니다.
예를 들어 게시물 번호의 역순으로 출력하라는 처리를 한다면 SQL Developer에서 다음과 같이 처리할 수 있습니다.
select * from tbl_board
order by bno desc;
상단의 버튼 중에는 SQL에 대해서 실행 계획을 쉽게 볼 수 있도록 버튼이 제공 됩니다.
실행 계획을 보면 트리 구조로 방금 전 실행한 SQL이 어떻게 처리된 것인지를 알려줍니다.
흔히 SQL 튜닝이라고 하는 작업은 이를 보고 어떤 방식이 더 효과적인지를 판단해서 수정하게 됩니다.
이 책에서는 실행 계획에 대해 많은 부분을 설명 할 순 없지만, 가장 간단하게 실행계획을 보는 방법은 안쪽에서 바깥쪽으로, 위에서 아래로 봐주면 됩니다.
위 그림의 내용을 해석하자면 TBL_BOARD 테이블을 FULL로 접근하고 정렬했다는 것을 의미 합니다.
Full이라는 의미는 테이블 내의 모든 데이터를 스캔(scan) 했다는 의미 입니다.
실행 계획을 세우는 것은 데이터베이스에서 하는 역할이기 때문에 데이터의 양이나 제약조건 등의 여러 상황에 따라서 데이터베이스는 실행 게획을 다르게 작성 합니다.
테스트를 위해서 데이터가 좀 많아지도록 아래 SQL을 여러번 실행해서 데이터를 수백만개로 만든 후에 커밋 하겠습니다.
--재귀 복사를 통해 데이터의 개수를 늘린다. 반복해서 여러 번 실행 하세요.
insert into tbl_board(bno,title,content,writer)
(select seq_board.nextval, title,content,writer from tbl_board);
위의 insert문을 여러 번 실행하게 되면 현재 TBL_BOARD 테이블의 데이터 수만큼 다시 insert가 됩니다.
결과를 보면 insert문을 실행할 때마다 2배씩 데이터가 늘어나게 됩니다.
commit 후에 select count(*) from tbl_board를 실행 해보면 데이터의 수가 엄청나게 늘어난 것을 확인 할 수 있습니다.
(데이터를 약 200만건정도 넣어 주었습니다.)
데이터가 많아지면 정렬에 그만큼 시간을 소모하게 됩니다.
고의적으로 bno라는 칼럼의 값에다 1을 추가한 값을 역순으로 정렬하는 SQL을 만들다면 아래와 같습니다.
select * from tbl_board order by bno +1 desc;
연산작업이 추가되기는 했지만 SQL문의 결과가 나오는데 5초정도가 나왔습니다.
실행 시간은 현재 시스템의 상황이나 데이터베이스의 상황에 따라 차이가 날 수 있습니다.
반복적으로 몇 번 실행하면 데이터베이스가 메모리상에 보관하는 데이터를 가져오는 사황이 되고 대략 1초대의 결과가 나올 수도 있습니다.
위의 SQL을 실행한 결과는 테이블 전체를 스캔(조사) 하는것을 볼 수 있습니다.
실행 계획을 잠깐 살펴보면 TBL_BOARD를 FULL로 스캔(조사) 했고, 바깥쪽으로 가면서 sort 가 일어난 것을 볼 수 있습니다.
이때 가장 많은 시간을 소모하는 작업은 정렬(sort) 작업 입니다.
위의 SQL에서 order by bno +1 desc에서 +1은 아무런 도움을 주지 않으므로 제거 하겠습니다.
select * from tbl_board order by bno desc;연산이라는 차이가 있긴 하지만 실해에 걸리는 시간은 많은 차이가 나게 됩니다.
이전에 5초 이상 걸리던 작업이 거의 0초만에 실행 됩니다.
이 결과의 차이는 실행 계획에도 다르게 동작 합니다.
FULL 스캔을 하긴 했지만, PK_BOARD를 이용해 접근하고 기존과 달리 맨 위의 SORT 과정이 없는 것을 볼 수 있습니다.
이것을 이해 하려면 데이터베이스의 인덱스(index)에 대해서 조금은 알아 둘 필요가 있습니다.
(저는 실행 했을때 기존과 동일한 결과였습니다.ㅠㅠ)
12.2 order by 보다는 인덱스
데이터가 많은 상태에서 정렬 작업이 문제가 된다는 사실을 알았다면, 이 문제를 어떻게 해결해야 하는지를 살펴보겠습니다.
가장 일반적인 해결책은 인덱스(index)를 이용해서 정렬을 생략하는 방법입니다.
인덱스라는 존재가 이미 정렬된 구조이므로 이를 이용해서 별도의 정렬을 하지 않는 방법 입니다.
인덱스가 무엇인지에 대해서는 조금 뒤에 더 살펴볼 예정이고, 우선 위와 같은 상황에서 다음과 같은 SQL을 실행해 보겠습니다.
select
/*+ INDEX_DESC(tbl_board pk_board) */
*
from tbl_board
where bno > 0;위의 SQL을 실행한 결과는 테이블 전체를 조사하고 정렬한 것과는 동일하지만 실행 시간은 많은 차이가 나게 됩니다.
가장 중요한 것은 SQL의 실행 시간이 거의 0초에 가깝게 나온다는 점입니다.
SQL 문의 실행 계획은 아래와 같은 모습을 가지게 됩니다.
SQL의 실행 계획에서 주의해서 봐야 하는 부분은
- SORT를 하지 않는다는 점.
- TBL_BOARD를 바로 접근하는 것이 아니라 PK_BOARD를 이용해서 접근 했다는 점
- RANGE SCAN DESENDING, BY INDEX ROWID BATCHED로 접근했다는 점입니다.
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,
regdate date default sysdate,
updatedate date default sysdate
);
alter table tbl_board add constraint pk_board
primary key (bno);테이블을 생성할 때 제약조건으로 PK를 지정하고, PK의 이름이 pk_board라고 지정하였습니다.
데이터베이스에서는 PK는 상당히 중요한 의미를 가지는데, 흔히 말하는 식별자와 인덱스의 의미를 가집니다.
인덱스는 말 그대로 색인 입니다.
우리가 가장 흔히 접하는 인덱스는 도서 뒤쪽에 정리되어 있는 색인 입니다.
색인을 이용하면 사용자들은 책 전체를 살펴볼 필요 없이 색인을 통해서 자신이 원하는 내용이 책 어디에 있는지 알 수 있습니다.
데이터베이스에서 인덱스를 이해하는 가장 쉬운 방법은 데이터베이스의 테이블을 하나의 책이라고 생각하고 어떻게 데이터를 찾거나 정렬하는지를 생각하는 것 입니다.
이를 통해서 원하는 내용을 위에서 부터 혹은 반대로 찾아나가는데 이를 스캔(scan) 이라고 표현 합니다.
데이터베이스에 테이블을 만들때 PK를 부여하면 지금까지 얘기한 인덱스 라는 것이 만들어 집니다.
데이터베이스를 만들 때 PK를 지정하는 이유는 식별 이라는 의미가 있지만, 구조상으로는 인덱스라는 존재(객체)가 만들어지는 것을 의미합니다.
tbl_board 테이블은 bno 라는 칼럼을 기준으로 인덱스를 생성하게 됩니다.
인덱스에는 순서가 있기 때문에 그림으로 표현하자면 아래와 같이 만들어 집니다.
ㅇ
그림의 왼쪽은 인덱스이고 오른쪽은 실제 테이블입니다.
왼쪽 그림을 보면 bno 값이 순서대로 정렬된 것을 볼 수 있습니다.
오른쪽은 테이블의 모습입니다.
테이블은 마치 책장에 책을 막 넣은것 처럼 순서가 섞여있는 경우가 대부분 입니다.
인덱스와 실제 테이블을 연결하는 고리는 ROWID 라는 존재입니다.
ROWID는 데이터베이스 내의 주소에 해당하는데 모든 데이터는 자신만의 주소를 가지고 있습니다.
SQL을 통해서 bno 값이 100번인 데이터를 찾고자 할 때는 SQL은 "where bno = 100" 과 같은 조건을 주게 됩니다.
이를 처리하는 데이터베이스 입장에서는 tbl_board라는 책ㄷ에서 bno 값이 100인 데이터를 찾아내야만 합니다.
만일 책이 얇아서 내용이 많치 않다면 전체 데이터를 다 보는것이 빠를 것 입니다.
이를 FULL SCAN이라고 합니다.
하지만 내용이 많고, 색인이 존재한다면 당연히 색인을 찾고 색인에서 주소를 찾아서 접근하는 방식을 이용 하는것이 빠릅니다.
실행 계획을 보면 이러한 생각이 데이터베이스 내에서 진행되는 것을 확인 할 수 있습니다.
안쪽은 먼저 보면 PK_BOARD는 인덱스이므로 먼저 인덱스를 이용해서 100번 데이터가 어디에 있는지 ROWID를 찾아내고, 바깥쪽을 보면 BY INDEX ROWID 라고 되어 있는 말 그대로 ROWID를 통해서 테이블에 접근하게 됩니다.
12.3 인덱스를 이용하는 정렬
인덱스에서 가장 중요한 개념 중 하나는 정렬이 되어있다는 점입니다.
정렬이 이미 되어 있는 상태이므로 데이터를 찾아내서 이들을 SORT하는 과정을 생략 할 수 있습니다.
bno의 역순으로 정렬한 데이터를 원한다면 이미 정렬된 데이터를 이용해서 뒤에서 부터 찾아 올라가는 방식을 이용할 수 있습니다.
이때 뒤에서 부터 찾아 올라간다는 개념이 DESENDING 입니다.
이전에 실행한 bno의 역순으로 데이터를 가져올 때의 실행계획을 다시 한번 살펴 보겠습니다.
실행 계획을 보면 PK_BOARD라는 인덱스를 이용하는데 DESENDING을 하고 있는것을 볼 수 있습니다.
이를 그림으로 표현하면 아래와 같이 표현할 수 있습니다.
인덱스를 역순으로 찾기 때문에 가장 먼저 찾은 bno 값은 가장 큰 값을 가진 데이터가 됩니다.
이후에는 테이블에 접근해서 데이터를 가져오게 되는데, 이런 과정이 반복되면 정렬을 하지 않아도 동일하게 정렬된 결과를 볼 수 있게 됩니다.
하나의 예를 더 생각해보면 만일 사용자가 bno의 순서로 정렬해달라고 요구하는 상황이라면 PK_BOARD 인덱스가 앞에서 부터 찾아서 내려가는 구조를 이용하는 것이 효율적 입니다.
SQL Developer를 이용해 실행해보면 아래와 같은 실행 계획이 수립 되는 것을 확인 할 수 있습니다.
실행 계획 상으로 PK_BOARD 인덱스를 먼저 접근하고 TBL_BOARD를 이용하는 것을 볼 수 있습니다.
SORT가 없기 때문에 0초에 가까운 성능을 보여줍니다.
실무에서도 데이터의 양이 많고 정렬이 필요한 상황이라면 우선적으로 생각하는 것이 인덱스를 작성하는 것입니다.
데이터의 양이 수천, 수만개 정도의 정렬은 그다지 부하가 걸리지 않지만 그이상의 데이터를 처리해야 하는 상황이라면 정렬을 안할 수 있는 방법에 대해 고민해야 합니다.
12.3.1 인덱스와 오라클 힌트(hint)
웹페이지의 목록은 주로 시간의 역순으로 정렬된 결과를 보여줍니다.
최신 데이터가 가장 중요하기 때문에 시간의 역순으로 정렬해서 최신 게시물들을 보여주게 됩니다.
이 경우 개발자의 입장에서는 정렬을 안하는 방식으로 select 문을 실행하고 싶어 합니다.
오라클은 select 문을 전달할때 힌트(hint)라는 것을 사용할 수 있습니다.
힌트는 말 그대로 "데이터베이스에 내가 지금 전달한 SELECT 문을 이렇게 실행하면 좋겠습니다"라는 힌트입니다.
힌트는 특이하게도 SELECT문을 어떻게 처리하는지에 대한 얘기일 뿐이므로 힌트 구문에서 에러가 나도 전혀 SQL 실행에는 지장을 주지 않습니다.
따라서 힌트를 이용한 SELECT문을 작성한 후에는 실행계획을 통해 원하는 대로 실행이 되는지를 확인 해야 합니다.
게시물 목록은 반드시 시간의 역순으로 나와야만 하기 때문에 SQL에서는 "order by bno desc"와 같은 구문을 추가할 수 있습니다.
문제는 "order by bno desc"와 같은 조건은 데이터베이스 상황에 따라서 테이블의 모든 데이터를 정렬하는 방식으로 동작할 수 있다는 점입니다.
반면에 힌트는 개발자가 데이터베이스에 어떤 방식으로 실행해 줘야 하는지를 명시하기 때문에 조금 강제성이 부여되는 방식입니다.
select * from tbl_board order by bno desc;
select /*+INDEX_DESC (tbl_board pk_board) */ *
from tbl_board;위 두 SQL문은 동일한 결과를 생성하는 SQL 입니다.
두번째 SELECT문은 order by 조건이 없어도 동일한 결과가 나온것에 대해 주목해야 합니다.
SELECT문에서 힌트를 부여했는데 힌트의 내용이 tbl_board 테이블에 pk_board 인덱스를 역순으로 이용해 줄 것 이므로 실행 계획에서 이를 활용하고 있는 것을 확인 할 수 있습니다.
오라클 데이터베이스에서 사용할 수 있는 힌트이 종류는 여러가지 있습니다.
이에 대한 모든 내용은 설명하는것은 책의 범위를 넘어서는 것이므로 자주 사용하는 몇 가지만 언급 하겠습니다.
12.3.2 힌트 사용 문법
SELECT문을 작성할 때 힌트는 잘못 작성되어도 실행할 때는 무시되기만 하고 별도의 에러는 발생하지 않습니다.
우선 힌트를 사용 할 때 아래와 같은 문법을 사용 합니다.
SELECT
/*+ Hint name (param....) */ column name, .....
FROM TABLE NAME
......힌트 구문은 " /*+ */ " 입니다.
힌트 자체는 SQL로 처리되지 않기 때문에 위의 그림처럼 뒤에 칼럼 명이 나오더라도 별도의 "," 로 처리되지 않습니다.
12.3.3 FULL 힌트
힌트 중에는 해당 SELECT문을 실행할 때 테이블 전체를 스캔할 것으로 명시하는 FULL 힌트가 있습니다.
FULL 힌트는 테이블의 모든 데이터를 스캔하기 때문에 데이터가 많을수록 상당히 느리게 실행 됩니다.
예를 들어 tbl_board 테이블을 FULL 스캔 하도록 하고, 이 상태에서 정렬을 하려면 다음과 같이 할 수 있습니다.
select /*+ FULL(tbl_board) */ * from tbl_board
order by bno desc;실행 계획을 보면 TBL_BOARD를 FULL로 접근했고 다시 SORT가 적용되는 것을 볼 수 있습니다.
실행 시간 역시 오래 걸리는 것을 볼 수 있습니다.
12.3.4 INDEX_ASC, INDEX_DESC 힌트
흔히 목록 페이지에서 갖아 많이 사용하는 힌트는 인덱스와 관련된 INDEX_ASC, INDEX_DESC 힌트 입니다.
ASC/DESC에서 알 수 있듯 인덱스를 순서대로 이용할 것인지 역순으로 이용할 것인지를 지정하는 것입니다.
INDEX_ASC, INDEX_DESC 힌트는 주로 "order by"를 사용하기 위해 사용한다고 생각하면 됩니다.
인덱스 자체가 정렬을 해 둔 상태이므로 이를 통해 SORT 과정을 생략하기 위한용도 입니다.
INDEX_ASC, INDEX_DESC 힌트는 테이블 이름과 인덱스 이름을 같이 파라미터로 사용 합니다.
select /*+INDEX_ASC (tbl_board pk_board) */ *
from tbl_board
where bno > 0;
INDEX_ASC, INDEX_DESC를 이용하는 경우에는 동일한 조건의 order by 구문을 작성하지 않아도 됩니다.
예를 들어 SQL에서 아무런 order by 조건이 없어도 bno의 순번을 통해서 접근하기 떄문에 order by bno asc 구문은 필요 없습니다.
12.4 ROWNUM과 인라인뷰
페이징 처리를 위해서 역순으로 게시물의 목록을 조회하는 작업이 성공 했다면, 이제는 전체가 아닌 필요한 만큼의 데이터를 가져온느 방식에 대해 학습하겠습니다.
오랔르 데이터베이스는 페이징 처리를 위해서 ROWNUM이라는 특별한 키워드를 사용해서 데이터베이스에 순번을 붙여 사용 합니다.
RUWNUM은 쉽게 생각해서 SQL이 실행된 결과에 넘버링을 해준다고 생각하면 됩니다.
모든 SELECT 문은 ROWNUM이라는 변수를 이용해서 해당 데이터가 몇 번째로 나오는지 알아낼 수 있습니다.
ROWNUM은 실제 데이터가 아니라 테이블에서 데이터를 추출한 후 붙여주는 값이므로 상황에 따라 그 값이 매번 달라질 수 있습니다.
우선 아무 조건을 적용하지 않고 TBL_BOARD 테이블에 접근하고 각 데이터에 ROWNUM을 적용한다면 다음과 같이 작성할 수 있습니다.
SQL에 아무런 조건이 없기 때문에 데이터는 테이블에 섞여있는 상태 그대로 나오게 됩니다.
(테이블을 FULL 스캔 한 것과 동일합니다.)
select rownum rn, bno, title from tbl_board;
ROWNUM은 테이블에 존재하지 않고, 테이블에서 가져온 데이터를 이용해서 번호를 매기는 방식으로 위의 결과는 테이블에서 가장 먼저 가져올 수 이는 데이터들을 꺼내서 번호를 붙여주고 있습니다.
이때 번호는 현재 데이터베이스의 상황에 따라서 저장된 데이터를 로딩하는 것이므로 실습 환경에 따라 다른 값이 나오게 됩니다.
위의 결과에서 568번 데이터는 3번째로 꺼내진 데이터라고 해석 할 수 있습니다.
만일 테이블에서 데이터를 가져온 후에 정렬을 하게 된다면 568번의 ROWNUM 값은 동일하게 3이 됩니다.
select /*+FULL(tbl_board)*/rownum rn, bno, title from tbl_board
where bno > 0 order by bno;
위의 SQL은 FULL 힌트를 이용해서 전체 데이터를 조회하고 다시 정렬한 방식 입니다.
결과를 보면 568번 데이터는 3번째로 접근이 되었지만 정렬 과정에서 뒤로 밀리는 것을 확인 할 수 있습니다.
이를 통해서 알 수 있는 사실은 ROWNUM이라는 것은 데이터를 가져올 때 적용되는 것이고, 이후에 정렬되는 과정에서는 ROWNUM은 변하지 않는다는 것 입니다.
다른 말로는 정렬은 나중에 처리된다는 의미이기도 합니다.
12.4.1 인덱스를 이용한 접근 시 ROWNUM
ROWNUM의 의미가 테이블에서 데이터를 가져오면서 붙는 번호라는 사실을 기억해 보면 결국 문제는 테이블에 어떤 순서로 접근하는가에 따라서 ROWNUM의 값은 바뀔수 있다는 뜻입니다.
다시 말해, 위의 경우는 우선FULL로 접근해서 568번 데이터를 찾았고 이후에 정렬을 하는데 이미 데이터는 다 가져온 상태이므로 ROWNUM에는 아무런 영향을 주지 않습니다.
만일 PK_BOARD 인덱스를 통해 접근한다면 다음과 같은 과정으로 접근합니다.
- PK_BOARD 인덱스를 통해서 테이블에 접근
- 접근한 데이터에 RWONUM 부여
1)의 과정에서 이미 정렬이 되었기 때문에 568번의 접근 순서는 3번째가 아니라 한참 뒤일 것입니다.
이 경우 ROWNUM은 전혀 다른 값을 가지게 됩니다.
select /*+INDEX_ASC(tbl_board)*/rownum rn, bno, title from tbl_board;
힌트를 이용해서 tbl_board 테이블을 pk_board의 순번으로 접근하게 되면 rownum의 값이 달라진 것을 볼 수 있습니다.
만일 게시물의 역순으로 테이블을 접근하게 된다면 568번의 ROWNUM 값은 접근 하는 순서가 뒤쪽이기 때문에 큰 값이 나오게 됩니다.
ROWNUM은 데이터에 접근 하는 순서이기 때문에 가장 먼저 접근하는 데이터가 1번이 되는데, 이를 이용하면 테이블을 bno의 역순으로 접근해서 bno 값이 가능 큰 데이터가 ROWNUM 값이 1이 되도록 작성할 수 있습니다.
select /*+INDEX_DESC(tbl_board)*/rownum rn, bno, title from tbl_board
where bno > 0;
위의 SQL은 PK_BOARD 인덱스 역으로 타면서 테이블에 접근 했기 때문에 bno 값이 가장 큰 데이터를 가장 먼저 가져오게 됩니다.
이 방식을 이용하면 각 게시물을 정렬하면서 순번을 매겨줄 수 있는데, 1페이지에 10개씩 게시물을 보여준다는 전제하에 1페이지의 경우는 위의 그림에서 RN이라는 컬럼의 값이 1부터 10에 해당한다고 볼 수 있습니다.
12.4.2 페이지 번호 1, 2의 데이터
한 페이지당 10개의 데이터를 출력한다고 가정하면 ROWNUM 조건을 WHERER 구문에 추가해서 다음과 같이 작성할 수 있습니다.
select /*+INDEX_DESC(tbl_board)*/rownum rn, bno, title from tbl_board
where rownum <= 10;WHERE 구문에는 RWONUM 관련 조건을 볼 수 있는데, 위의 SQL 처리 결과는 다음과 같습니다.
SQL의 실행 결과를 보면 가장 높은 번호의 게시물 10개만의 출력되는 것을 볼 수 있는데, 이때 실행 계획을 통해 PK_BOARD 인덱스를 역순으로 접근하는 것을 볼 수 있습니다.
WHERE 조건에서 특이하게 ROWNUM 조건은 테이블을 접근할 때 필터링 조건으로 적용된 것을 볼 수 있습니다.
1페이지 데이터를 구했다면 흔히 동일한 방식으로 2페이지 데이터를 구할 수 있을 것이라고 생각 합니다.
하지만 절대로 원하는 결과를 얻을 수 없는데 그 원인을 알아야 합니다.
select /*+INDEX_DESC(tbl_board)*/rownum rn, bno, title from tbl_board
where rownum > 10 and rownum <= 20;
위의 SQL을 보면 ROWNUM이 10보다 크고 20보다 작거나 같은 데이터를 가져올 것이라고 기대하지만 실제로는 아무 결과가 나오지 않습니다.
이렇게 되는 이유를 알아내려면 실행 계획을 유심히 봐야 합니다.
실행 계획은 안쪽에서 바깥쪽으로, 위에서 아래로 보게 되므로 실행 계획은 우선 ROWNUM > 10인 데이터를 찾게 됩니다.
문제는 TBL_BOARD에 처음으로 나오는ROWNUM 값이 1이라는 것 입니다.
TBL_BOARD에서 데이터를 찾고 ROWNUM이 1이 된 데이터는 WHERE 조건에 의해 무효가 됩니다.
이후 다시 다른 데이터를 가져오면 새로운 데이터가 첫 번쨰 데이터가 되므로 다시 ROWNUM은 1이 됩니다.
이 과정이 반복되면 ROWNUM은 항상 1로 만들어지고 없어지는 과정이 반복 되므로 테이블의 모든 데이터를 찾아내지만 결과는 아무것도 나오지 않게 됩니다.
이러한 이유로 SQL을 작성할때 RWONUM조건은 반드시 1이 포함 되어야 합니다.
select /*+INDEX_DESC(tbl_board)*/rownum rn, bno, title from tbl_board
where rownum <= 20;
달라진 점은 ROWNUM 조건이 1을 포함하도록 변경 한것 입니다.
위의 SQL 결과는 역순으로 데이터를 20개 가져오는 것 입니다.
12.4.3 인라인뷰(In-line View) 처리
10개씩 목록을 출력하는 경우 2페이지의 데이터를 20개를 가져오는 데는 성공 했지만, 1페이지의 내용이 같이 출력되는 문제가 있으므로 마지막으로 이 문제를 수정해야 합니다.
이 문제를 해결하기 위해서는 인라인뷰 라는것을 이용하는데
"SELECT문 안쪽 FROM에 다시 SELECT문" 입니다.
인라인뷰는 논리적으로는 어떤 결과를 구하는 SELECT문이 있고, 그 결과를 다시 대상으로 삼아 SELECT 하는 것 입니다.
데이터베이스에서는 테이블이나 인덱스와 같이 뷰(View)라는 개념이 존재합니다.
뷰(View)는 일종의 창문 같은 개념으로 복잡한 SELECT 처리를 하나의 뷰로 생성하고, 사용자들은 뷰를 통해 복잡하게 만들어진 결과를 마치 하나의 테이블 처럼 쉽게 조회한다는 개념 입니다.
인라인뷰는 이러한 뷰의 작성을 별도로 하지 않고 말 그대로 FROM 구문 안에 바로 작성하는 형태입니다.
SELECT ....
FROM (SELECT ... FROM TABLE //인라인뷰
) ;외부에서 SELECT 문은 인라인뷰로 작성된 결과를 하나의 테이블처럼 사용합니다.
에를 들어 위의 경우 20개의 데이터를 가져오는SQL을 하나의 테이블처럼 간주하고 바깥쪽에서 추가적인 처리를 하는 것 입니다.
select bno,title,content from (
select /*+INDEX_DESC(tbl_board)*/
rownum rn,
bno,
title,
content
from tbl_board
where rownum <= 20
)
where rn > 10;
기존의 SQL과 비교해보면 20개의 데이터를 가져온 후 2페이에 해당하는 10개만을 추출하는 방식으로 구현 됩니다.
이 과정을 정리하면 아래와 같습니다.
- 필요한 순서로 정렬된 데이터에 ROWNUM을 붙인다.
- 처음부터 해당 페이지의 데이터를 ROWNUM <=30 과 같은 조건을 이용해 구한다.
- 구해놓은 데이터를 하나의 테이블처럼 간주하고 인라인뷰로 처리한다.
- 인라인뷰에서 필요한 데이터만을 남긴다.
'개발관련 > 코드로 배우는 스프링 웹 프로젝트(개정판)' 카테고리의 다른 글
| Part3 - 페이징 화면 처리 Chapter14 (0) | 2023.06.17 |
|---|---|
| Part3 - MyBatis와 스프링에서 페이징 처리 Chapter13 (0) | 2023.06.04 |
| Part3 - 화면 처리 Chapter11 (0) | 2023.03.26 |
| Part3 - 프레젠테이션(웹) 계층의 CRUD 구현 Chapter10 (0) | 2023.03.19 |
| Part3 - 비즈니스 계층 Chapter09 (0) | 2023.03.19 |
