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

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

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

12.1 order by의 문제
데이터 베이스를 이용할때 웹이나 애플리케이션에서 가장 신경 쓰는 부분은 1)빠르게 처리 되는것, 2)필요한 양만큼만 데이터를 가져오는것.
빠르게 동작하는 SQL을 위해서는 order by를 이용하는 작업을 최대한 자제, order by는 데이터가 많은경우 엄청난 성능의 저하를 가져오기때문. 1)데이터가 적은경우, 2)정렬을 빠르게 할수 있는 방법이 있는경우가 아니라면 주의

12.1.1 실행계획과 order by
오라클의 페이징처리를 이해하기 위해선 실행계획(execution plan)을 알아야한다. 말그대로 SQL을 데이터베이스에서 어떻게 처리할것인가?에 대한것.
SQL이 데이터베이스에 전달되면 데이터베이스는 여러 단계를 거쳐서 해당 SQL을 어떤순서와 방식으로 처리할것인지 계획을세움. 
-과정 

1.SQL 파싱(SQL구문에 오류가 있는가?) 

2. SQL 최적화 (SQL 실행되는 필요한 비용계산 (시간소요) ) 

3. SQL 실행 ( 1,2번에서 진행된 실행 계획을 통해 메모리 상에서 데이터를 읽거나 물리적인 공간에서 데이터를 로딩하는 작업을함)

SQL Developer 에서 SQL에 대해서 '실행계획'을 쉽게 볼수 있도록 버튼제공됨.

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

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

데이터가 많아질수록 SQL문 처리 결과가 길어짐(반복적으로 실행시 DB의 캐싱으로인해 소요시간이 줄어든다)

select * from tbl_board order by bno +1 desc;

위쿼리 실행시 실행 계획을 잠깐 살펴보면

'FULL'로 조사했고, 'SORT'가 진행됨 ( 이때 가장 많은 시간을 소모하는 작업은 정렬 작업이다)

 

12.2 order by 보다는 인덱스

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

결론부터 말하자면 '인덱스'라는 존재가 이미 정렬된 구조 이므로 이를 이용해 별도의 정렬을 하지 않는 방법

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

 

위사진의 SQL 실행 계획

SQL 실행 계획에서 주의 깊게 봐야할 부분은 

1) SORT를 하지 않았다는 점

2) TBL_을 바로 접근하는것이 아니라 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,
regdate date default sysdate,
updatedate date default sysdate
);
 
alter table tbl_board add constratin pk_board primary key(bno);

테이블을 생성할때 제약조건으로 PK를 지정하고 PK의 이름이 'pk_board'라고 지정했다.

데이터베이스에서  Primary Key(PK) 는 NOT NULL + UNIQUE 의 기능을 가지고 있습니다.

- 주키 / 기본키 / 식별자 등 으로 자주 불리고 있습니다.

- 자동 INDEX가 생성되는데 이는 검색 키로서 검색 속도를 향상시킵니다.

'인덱스'라는 말그대로 '색인'이다. 색인을 이용하면 사용자들은 전체 살펴 볼 필요없이

색인을 통해서 자신이 원하는 내용이 어디에 있는지 알수 있다.

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

DB를 만들때 PK를 지정하는 이유는 '식별'이라는 의미가 있지만,

구조상으로는 '인덱스'라는 존재(객체)가 만들어지는것을 의미

 

*tbl_board 테이블은 bno 라는 칼럼을 기준으로 인덱스를 생성하게됨.

왼쪽은 인덱스이고 오른쪽은 실제 테이블이다,

왼쪽그림을 보면 bno값이 순서대로 정렬 되있고 오른쪽 테이블은 순서가 섞여 있다. 인덱스와 실제 테이블을 연결하는 고리는 ROWID라는 존재이다. ROWID는 DB내의 주소에 해당하는데 모든 데이터는 자신만의 주소를 가지고있다.

 

SQL을 통해서 bno 값이 100번인 데이터를 찾고자 할때는 SQL은 'where bno= 100'과 같은 조건을 주게됨.

DB입장에서는 테이블에서 bno 값이 100인 데이터를 찾아야 하는데,

데이터가 적다면 속히 전체르 살펴보는게 빠를것이다.('FULL SCAN')

하지만 데이터가 많고 색인(인덱스)가 존재한다면 색인을 찾고 색인에서 주소를 찾아서 접근하는 방식을 이용.

 

 

SELECT * FROM TBL_BOARD where bno = 100;

 

12.3 인덱스를 이용하는 정렬

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

데이터를 찾아내서 이들을 SORT(정렬)하는 과정 생략 가능.

 

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

'뒤에서부터 찾아 올라간다'는 개념은 실행 계획에서 'DESCENDING'이다

인덱스를 역순으로 찾기 때문에, 가장 먼저 찾은 bno값은 가장 큰 값을 가진 데이터가 된다.

이후에는 ROWID와 동일한 테이블에 접근해서 데이터를 가져오게 되는데, 이런 과정을 반복하면 정렬하지 않아도 

동일하게 정렬된 결과 출력가능.

(처음 DB 설계시 bno는 페이지처리를 위해 시퀀스를 등록 해놨기때문에 테이블에 삽입시 저절로 1씩 증가하게 설계 해서 제일 큰값이 제일 최근에 삽입한 데이터다)

 

하나의 예를 더 생각해보면 만일 사용자가 'bno의 순서로 정렬해달라'고

요구하는상황이면 PK_BOARD 인덱스가 앞에서부터 찾아가서 내려가는 구조를 이용하는것이 효율적이다

 

**실무에서도 데이터의 양이 많고 정렬이 필요한 상황이 있다면 우선적으로 생각하는것이 '인덱스'를 작성하는것.**

 

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

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

최신데이터가 가장 중요하기 때문에 시간의 역순으로 게시물을 보여줌.

 

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

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

힌트는 특이하게 select문을 어떻게 처리하는지에 대한 얘기일뿐이므로, 힌트 구문 에러나도 SQL 실행에 지장없다.

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문을 작성할때 힌트는 잘못 작성되어도 실행 할때는 무시되기만 하고, 별도의 에러는 발생 X

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;

 

12.3.4 INDEX_ASC,INDEX,DESC 힌트

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

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

INDEX_ASC/DESC 힌트는 주로 'order by'를 위해서 사용한다고 생각하면됨.

(인덱스 자체가 정렬해둔상태이므로 이를통해 SORT 과정을 생략하기 위함)

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

(예를 들어 위으 SQL 에서 아무런 order by 조건이 없어도 bno의 순번을 통해 접근하기 떄문에 필요하지않음)

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

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

 

12.4 ROWNUM과 인라인뷰

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

ROWNUM은 쉽게 생각해서 SQL이 실행된 결과에 넘버링을 해준다고 생각하면됨.

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

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

 

우선 아무조건을 적용하지 않고 tbl_board테이블에 접근하고 각 데이터에 ROWNUM을 적용하면 ,아래와 같이 작성한다

SQL에 아무런 조건이 없기때문에 데이터는 테이블에 섞여 있는상태로 나온다(테이블을 FULL스캔한것과 동일)

select rownum rn, bno, title from tbl_board;

ROWNUM은 테이블에는 존재하지 않고, 테이블에서 가져온 데이터를 이용해서 번호를 매기는 방식으로 위의 결과는

테이블에서 가장 먼저 가져올수 있는 데이터를 꺼내서 번호를 붙여주고 있다.

(ROWNUM이라는것은 데이터를 가져올때 적용되는것이고,

이 후에 정렬되는 과정에서는 ROWNUM이 변경되지않는다.=나중에 처리된다)

 

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;

힌트를 이용해서 tbl_board 테이블을 pk_board 순번으로 접근하게되면 ROWNUM 값이 달라진다

만일 게시물의 역순으로 테이블을 접근하게된다면 50번의 ROWNUM 값은 접근하는 순서가 뒤쪽에있기에 큰값이나옴

ROWNUM은 데이터에 접근하는 순서 이기때문에 가장먼저 접근하는 데이터가 1번이되는데, 잘생각해보면 

테이블을 bno의 역순으로 접근해서 bno값이 가장 큰데이터가 ROWNUM 값이 1이 되도록 작성할수있다.

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

위의 SQL은 PK_BOARD 인덱스 역으로 타면서 테이블에 접근했기때문에, bno값이 가장 높은데이터를 가져오게된다

 

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;

WHERE 구문에는 ROWNUM관련 조건을 줄수 있는데, 위의 결과는 1페이지 데이터는 구해져도

동일한 방식으로 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 조건에 의해 무효화가 된다.

이후 다시 다른데이터를 가져오면 새로운 데이터가 첫번째 데이터가 되므로 다시 ROWNUM은 1이 되고 또 무효 반복.

 

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

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

위의 쿼리가 달라진점은 ROWNUM 조건이 1을 포함하도록 변경한거뿐.

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

 

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

10개씩 목록을 출력하는 경우 2페이지의 데이터를 20개 가져오는데는 성공했지만,

1페이지의 내용이 같이 출력되는 문제가 있으므로, 마지막으로 이문제를 수정해야함.(인라인뷰 이용)

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

DB에는 테이블이나 인덱스와 같이 뷰(View)라는 개념이 존재한다. '뷰'는 일종의 '창문'같은 개념으로

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

외부에서 SELECT 문은 인라인뷰로 작성된 결과를 마치 하나의 테이블처럼 사용한다.

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

위의 결과를 하나의 테이블로 보면 해당 테이블은 RN,BNO,TITLE,CONTENT라는 칼럼을 가지는 테이블이 된다.

이경우 이 테이블에서 원하는것은 RN 칼럼 값이 10보다 큰 데이터만 가져오면된다.

처음의 SQL과 비교해보면 20개의 데이터를 가져온후 2페이지에 해당하는 10개만을 추출하는 방식으로 구현됨.

 

*과정을 정리 하자면 다음과 같다.

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

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

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

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