페이징 처리 (오라클)

책의 일부를 작성하였습니다.

 

1. order by의 문제

 

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

 

??

 

order by 는 데이터가 많은 경우 성능의 저하를 가져오기 때문!

 

1) 데이터가 적은 경우

2) 정렬을 빠르게 할 수 있는 방법이 있는 경우가 아니라면 order by는 주의해야 한다...

 

2. 실행 계획과 order by

 

오라클 페이징 처리를 이해하기 위해서 알아두어야 하는 것이 바로 실행 계획(execution plan)

 

실행 계획이란?

 

▶'SQL을 데이터베이스에서 어떻게 처리할 것인가?' 에 대한 것

 

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

 

• SQL 파싱 단계 : SQL 구문에 오류가 있는지, SQL 실행해야 하는 대상 객체가 존재하는지를 검사
• SQL 최적화 : SQL이 실행되는데 필요한 비용(cost)을 계산
• SQL 실행(수행) : 이 계산된 값을 기초로 해서 어떤 방식으로 실행하는 것이 가장 좋다는 것을 판단하는 '실행 계획(execuion plan)'을 세우게 된다.

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

예시)

board 라는 테이블이 있다고 가정

 

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

이 쿼리문을 계속 실행해서 22만건의 데이터를 생성한다.

 

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

 

고의적으로 bno라는 칼럼의 값에다 1을 추가한 값을 역순으로 정렬하는 SQL을 만든다면 다음과 같다.

select * from board order by bno + 1 desc;

-> FULL SCAN(풀 스캔) -> 전체를 조사하는 것을 볼 수 있다. (8.27초)

 

실행 계획을 살펴보면 BOARD를 FULL 스캔했고, 바깥쪽으로 가면서 'SORT'가 일어난 것을 볼 수 있다.(궁금하면 책..)

 

위의 SQL에서 'order by bno + 1 desc' 라는 조건에서 '+1'을 하는 것은 정렬에 아무런 도움을 주지 않으므로 아래와 같이 SQL을 수정해서 실행한다.

select * from board order by bno desc;

 -> 이전에 8초 이상 걸리던 작업이 거의 0초만에 실행되는 차이가 나게 된다.

 

실행 계획도 기존과 다르게 동작한다.

 

기존의 SQL이 BOARD 테이블 전체를 스캔했지만, 이번에는 PK_BOARD라는 것을 이용해서 접근하고 기존과 달리 맨 위의 SORT 과정이 없는 것을 볼 수 있다.

 

이해하려면 인덱스(index)에 대해 알 필요가 있다.

 

3. order by 보다는 인덱스

 

데이터가 많은 상태에서 정렬 작업이 문제가 된다.

 

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

 

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

 

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

위의 SQL을 실행한 결과는 테이블 전체를 조사하고 정렬한 것과 결과는 동일하지만 실행 시간은 엄청나게 차이가 나게 된다.

 

가장 중요한 점은 SQL의 실행 시간이 거의 0초(혹은 0.1 이하)로 나온다는 점이다.

 

SQL의 실행 계획에서 주의해서 봐야 하는 부분

1. SORT를 하지 않았다는 점
2. BOARD를 바로 접근하는 것이 아니라 PK_BOARD를 이용해서 접근한 점
3. RANGE SCAN DESCENDING, BY INDEX ROWID로 접근했다는 점

4. PK_BOARD라는 인덱스

board 테이블 생성했을 때 SQL

create table 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 board add constraint pk_board 
primary key(bno);

테이블을 생성할 때 제약조건으로 PK를 지정

 

PK 이름이 'pk_board'라고 지정

 

데이터베이스에서 PK는 상당히 중요한 의미를 가지는데, 흔히 말하는 '식별자'의 의미와 '인덱스'의 의미를 가진다.

 

'인덱스' 는 말 그대로 '색인' 이다. 

 

색인을 이용하면 사용자들은 책 전체를 살펴볼 필요 없이 색인을 통해서 자신이 원하는 내용이 책의 어디에 있는지 알 수 있다. 

 

데이터베이스에서 인덱스를 이해하는 가장 쉬운 방법은 데이터베이스의 테이블을 하나의 책이라고 생각하고 어떻게 데이터를 찾거나 정렬하는지를 생각하는 것이다. 

 

색인은 사람들이 쉽게 찾아볼 수 있게 알파벳 순서나 한글 순서로 정렬한다.

 

이를 통해서 원하는 내용을 위에서부터 혹은 반대로 찾아나가는데 이를 '스캔(scan)' 한다고 표현한다.

 

데이터베이스에 테이블을 만들 때 PK를 부여하면 '인덱스'라는 것이 만들어진다.

 

데이터베이스를 만들 때 PK를 지정하는 이유는 '식별'이라는 의미가 있지만 

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

 

왼쪽은 인덱스

 

오른쪽은 실제 테이블

 

왼쪽 그림을 보면 bno 값이 순서대로 정렬된 것을 볼 수 있다.

 

테이블은 마치 책장에 책을 막 넣은 것처럼 중간에 순서가 섞여 있는 경우가 대부분이다. 

 

인덱스와 실제 테이블을 연결하는 고리 : ROWID

 

ROWID는 데이터베이스 내의 주소에 해당하는데 모든 데이터는 자신만의 주소를 가지고 있다.

 

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

 

이를 처리하는 데이터베이스 입장에서는 board라는 책에서 bno 값이 100인 데이터를 찾아야만 한다. 

 

데이터가 적다면 전체를 살펴보는 것이 빠르겠지만, 데이터가 많다면 색인을 찾고 색인에서 주소를 찾아서 접근하는 방법이 더 효율적이다. 

 

4. 인덱스를 이용하는 정렬

 

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

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

 

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

이용할 수 있다.

 

이전에 실행한 bno의 역순으로 데이터를 가져올 때의 실행 계획을 살펴보면?

 

-> PK_BOARD 인덱스를 이용하는데 DESCENDING을 하고 있는 것을 볼 수 있다.

 

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

 

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

 

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

 

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

 

힌트는 개발자가 데이터베이스에 어떤 방식으로 실행해 줘야 하는지를 명시하기 때문에 강제성이 부여되는 방식이다.

 

select * from board order by bno desc;

select /*+INDEX_DESC (board pk_board) */ *
from board;

위는 동일한 결과를 생성하는 SQL

 

두번째 select 문에서 힌트를 주어서 같은 결과가 나왔다.

 

힌트 내용 : 'board 테이블에 pk_board 인덱스를 역순으로 이용해 줄 것'

 

5. 힌트 사용 문법

 

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

 

6. INDEX_ASC, INDEX_DESC 힌트

 

목록 페이지에서 가장 많이 사용하는 힌트 목록 : 'INDEX_ASC, INDEX_DESC'

 

SORT 과정을 생략하기 위한 용도이다.!

 

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

 

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

 

7. ROWNUM과 인라인뷰

 

필요한 만큼의 데이터를 가져오는 방식 : 오라클 데이터베이스는 페이지 처리를 위해서 ROWNUM이라는 특별한 키워드를 사용해서 데이터에 순번을 붙여 사용한다.

 

ROWNUM : SQL이 실행된 결과에 넘버링을 해준다. 

 

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

 

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

해석 : FULL 힌트를 이용해서 전체 데이터를 조회하고 다시 정렬한 방식이다.

 

-> 218번 데이터는 3번째로 접근되었지만 정렬 과정에서 뒤쪽으로 밀린다.

 

알 수 있는것?

 

ROWNUM이라는 것은 데이터를 가져올 때 적용되는 것이고, 이 후에 정렬되는 과정에서 ROWNUM이라는 것은 데이터를 가져올 때 적용되는 것이고, 이 후에 정렬되는 과정에서는 ROWNUM이 변경되지 않는다는 것.

 

즉 원래는 FULL SCAN으로 정렬이 되지 않은 상태로 데이터가 넘어오고 거기에 ROWNUM이 붙여지게 된다는 뜻!..

 

order by 를 통해 정렬이 이루어지니 ROWNUM의 값이 섞이게 되는 것!.(SQL의 순서의 제일 마지막인 이유도 붙일 수 있을듯)

 

8. 인덱스를 이용한 접근 시 ROWNUM

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

1) pk_board 인덱스를 통해서 테이블 접근

2) 접근한 데이터에 ROWNUM 부여

 

1) 과정에서 이미 정렬이 되어있기 때문에 218번의 접근 순서는 3번째가 아니라 한참 뒤(221)일 것

 

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

 

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

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

-> 가장 높은 번호의 게시물 10개만이 출력된다. 

 

1페이지를 이렇게 구했다면 2페이지는????? 쉬울거라고 생각..

 

 

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

위에처럼 11 ~ 20 번호의 게시글이 나올거라고 처음에 생각했었으나 ~ 

 

아니였다..

 

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

 

실행계획

SELECT STATEMENT

- COUNT                                                                            |      STOPKEY

  - Filter Predicates                                                           |

      -> ROWNUM <= 20                                                      |

  - FILTER                                                                           |

    - Filter Predicates                                                         | 

           -> ROWNUM > 10                                                   |

    - TABLE ACCESS                         BOARD                   |      BY INDEX ROWID

        -> INDEX                                   PK_BOARD             |      FULL SCAN DESCENDING

 

해석하는 부분이 틀릴 수도 있지만. 

 

실행계획 해석 :

제일 안 쪽의 부분이 ROWNUM > 10 이다. 요거부터 시작이 된다는 뜻이다. 

그다음  ROWNUM <= 20 이런 순서로 실행계획이 잡히는 것 같다.

 

오라클이 FULL SCAN DESCENDING 해서 읽어들인 데이터는 거꾸로 되어 있다.

즉 처음으로 접근한 BNO는 마지막 숫자일 것이다.

 

BNO                ROWNUM

22121212               1

22121211               2

 

이런 식으로 접근을 하게 된다.

실행계획에 따라 ROWNUM > 10 을 하게 되는데 

 

저 첫번째 ROWNUM은 1이기 때문에 조건을 만족하지 못하기에 무효화된다. 

 

그리고 다시 가져오게 되는데..

BNO                ROWNUM

22121212               1

22121211               1 (?) 

 

그 다음번째 ROWNUM을 가져와서 조건에 대입할 때, 이 ROWNUM은 초기화되어서 1이 된다. (개인적으로 이해하니깐 쉬웠던 부분)

 

그래서 모든 데이터가 무효화되기 때문에 오라클 ROWNUM 조건은 반드시 1이 포함되어야 한다.

 

10. 인라인뷰(In-line-View) 처리

인라인 뷰란?

https://youtu.be/efkQFP0wj08

 

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

 

인라인 뷰를 이용하면 아래의 페이징 쿼리를 작성할 수 있다.

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

가지고 있는 데이터베이스 기준으로 페이징 처리가 된 결과를 볼 수 있었다.!..

 

 

참고 자료

- 정희락, 불친절한 SQL 프로그래밍, 데비안

- 코드로 배우는 스프링 웹 프로젝트