[18] 기본적인 웹 게시물 관리 - 오라클 데이터베이스 페이징 처리 2

 

3-2. 힌트 사용 문법

 

select 문을 작성할 때 힌트는 잘못 작성되어도 실행할 때는 무시되기만 하고 별도의 에러는 발생하지 않는다. 우선 힌트를 사용할 때에는 다음과 같은 문법을 사용한다. 

 

select
 /*+ Hint name (param) */ colum name, .......
from 
 table_name

 

힌트 구문은 /*+ 로 시작하고 */ 로 마무리된다. 힌트 자체는 SQL로 처리되지 않기 때문에 위의 그림처럼 뒤에 칼럼명이 나오더라도 별도의 ',' 로 처리되지 않는다.

 

3-3. FULL 힌트

 

힌트 중에는 해당 select 문을 실행할 때 테이블 전체를 스캔할 것으로 명시는 FULL 힌트가 있다. FULL 힌트는 테이블의 모든 데이터를 스캔하기 때문에 데이터가 많을 때는 상당히 느리게 실행된다. 예를 들어, tbl_board 테이블을 FULL 스캔하도록 하고, 이 상태에서 정렬을 하려면 다음과 같이 작성할 수 있다. 

 

 

실행 계획을 보면 TBL_BOARD를 FULL로 접근하고, 다시 SORT가 적용된 것을 볼 수 있다. 실행 시간 역시 1.3초 이상으로 오래 걸리는 것을 볼 수 있다. (인덱스를 사용했을 때에 비해서 느리다)

 

 

3-4. INDEX_ASC, INDEX_DESC 힌트

 

흔히 목록 페이지에서 가장 많이 사용하는 힌트는 인덱스와 관련된 'INDEX_ASC, INDEX_DESC' 힌트다. ASC/DESC에서 알 수 있듯이 인덱스를 순서대로 이용할 것인지 역순으로 이용할 것인지를 지정하는 것이다. INDEX_ASC/DESC 힌트는 주로 'order by'를 위해서 사용한다고 생각하면 된다. 인덱스 자체가 정렬을 해 둔 상태이므로 이를 통해서 SORT 과정을 생략하기 위한 용도다.

 

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

INDEX_ASC/DESC 를 이용하는 경우에는 동일한 조건의 order by 구문을 작성하지 않아도 된다. 예를 들어, 위의 SQL에서 아무런 order by 조건이 없어도 bno의 순번을 통해서 접근하기 때문에 'order by bno asc' 구문은 필요로 하지 않는다.

 

4. ROWNUM과 인라인 뷰

 

페이징 처리를 위해서 역순으로 게시물의 목록을 조회하는 작업이 성공했다면, 이제는 전체가 아닌 필요한 만큼의 데이터를 가져오는 방식에 대해서 학습하겠다. 오라클 데이터베이슨느 페이지 처리를 위해서 ROWNUM 이라는 특별한 키워드를 사용해서 데이터에 순번을 붙여 사용한다.

 

ROWNUM은 쉽게 생각해서 SQL이 실행된 결과에 넘버링을 해준다고 생각하면 된다. 모든 SELECT 문에는 ROWNUM 이라는 변수를 이용해서 해당 데이터가 몇 번째로 나오는지 알아낼 수 있다. ROWNUM은 실제 데이터가 아니라 테이블에서 데이터를 추출한 후에 처리되는 변수이므로 상황에 따라서 그 값이 매번 달라질 수 있다. 

 

우선 아무 조건을 적용하지 않고 tbl_board 테이블에 접근하고 각 데이터에 ROWNUM을 적용하면 다음과 같이 작성할 수 있다. SQL에 아무런 조건이 없기 때문에 데이터는 테이블에 섞여 있는 상태 그대로 나오게 된다. (테이블을 FULL 스캔한 것과 동일하다. 각자 사용하는 데이터베이스에 저장된 구조가 다르므로 아래 화면과 동일하지 않을 수 있으나 섞여 있다는 점은 동일하다.)

 

 

ROWNUM은 테이블에는 존재하지 안혹, 테이블에서 가져온 데이터를 이용해서 번호를 매기는 방식으로 위의 결과는 테이블에서 가장 먼저 가져올 수 있는 데이터들을 꺼내서 번호를 붙여주고 있다. 이때 번호는 현재 데이터베이스의 상황에 따라서 저장된 데이터를 로딩하는 것이므로 실습 환경에 따라 다른 값이 나오게 된다. 

 

위의 결과에서 8번 데이터는 3번째로 꺼내진 데이터라고 해석할 수 있다. 만일 테이블에서 데이터를 가져온 후에 정렬을 하게 된다면 8번의 ROWNUM 값을 동일하게 3이 된다. 

 

위의 SQL은 FULL 힌트를 이용해서 전체 데이터를 조회하고 다시 정렬한 방식이다. 결과를 보면 8번 데이터는 3번째로 접근되었지만 정렬 과정에서 뒤쪽으로 밀리는 것을 볼 수 있다.

 

이를 통해서 알 수 있는 사실은 ROWNUM이라는 것은 데이터를 가져올 때 적용되는 것이고, 이 후에 정렬되는 과정에서는 ROWNUM이 변경되지 않는다는 것이다. 다른 말로는 정렬을 나중에 처리된다는 의미이기도 하다.

 

4-1. 인덱스를 이용한 접근 시 ROWNUM

 

ROWNUM의 의미가 테이블에서 데이터를 가져오면서 붙는 번호라는 사실을 기억해 보면 결국 문제는 테이블에 어떤 순서로 접근하는가에 따라서 ROWNUM 값은 바뀔 수 있다는 뜻이 된다. 다시 말해, 위의 경우는 우선 FULL로 접근해서 8번 데이터를 찾았고 이후에 정렬을 하는데 이미 데이터는 다 가져온 상태이므로 ROWNUM에는 아무런 영향을 주지 않는다.

 

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

 

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

1)의 과정에서 이미 정렬이 되어있기 때문에 8번의 접근 순서는 3번째가 아니라 한참 뒤일 것이다. 이 경우 ROWNUM은 전혀 다른 값을 가지게 된다. 

 

위의 SQL은 인덱스를 찾는 순서가 다르므로 아래와 같은 방식으로 실행되게 된다.  8번의 ROWNUM이 5 인것을 볼 수 있다!

 

힌트를 이용해서 tbl_board 테이블을 pk_board의 순번으로 접근하게 되면 ROWNUM의 값이 5번으로 달라진 것이 보인다. 이 때의 실행 계획은 다음과 같다. 

 

 

만일 게시물의 역순으로 테이블을 접근하게 된다면 8번의 ROWNUM 값은 접근하는 순서가 뒤쪽이기 때문에 큰 값이 나오게 된다. ROWNUM은 데이터에 접근하는 순서이기 때문에 가장 먼저 접근하는 데이터가 1번이 되는데, 이를 이용하면 테이블을 bno의 역순으로 접근해서 bno값이 가장 큰 데이터가 ROWNUM 값이 1이 되도록 작성할 수 있다. 

 

위의 SQL은 PK_BOARD 인덱스 역으로 타면서 테이블에 접근했기 때문에 bno 값이 가장 높은 데이터를 가장 먼저 가져오게 된다. 이 방식을 이용하면 각 게시물을 정렬하면서 순번을 매겨줄 수 있는데, 1 페이지의 경우는 위의 그림에서 RN이라는 칼럼의 값이 1부터 10에 해당한다고 볼 수 있다. (10개씩 페이징을 한다는 전제)

 

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

 

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

 

 

SQL의 실행 결과를 보면 가장 높은 번호의 게시물 10개만 출력되는 것을 볼 수 있는데, 이때 실행 계획을 통해서 PK_BOARD 인덱스를 역순으로 접근하는 것을 확인할 수 있다. 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;

 

위의 SQL을 보면 ROWNUM이 10보다 크고 20보다 작거나 같은 데이터들을 가져올 것이라고 기대하지만 실제로는 아무 결과가 나오지 않는다. 이렇게 되는 이유를 알아내려면 실행 계획을 유심히 살펴봐야만 한다. 

 

실행 계획은 안쪾에서부터 바깥쪽으로, 위에서부터 아래로 보게 되므로 위의 실행 계획은 우선 ROWNUM > 10, 데이터들을 찾게 된다. 문제는 TBL_BOARD 에 처음으로 나오는 ROWNUM의 값이 1이라는 것이다. TBL_BOARD에서 데이터를 찾고 ROWNUM 값이 1이 된 데이터는 where 조건에 의해서 무효화된다. 이후에 다시 다른 데이터를 가져오면 새로운 데이터가 첫 번째 데이터가 되므로 다시 ROWNUM은 1이 된다. 이 과정이 반복되면 ROWNUM 값은 항상 1로 만들어지고 없어지는 과정이 반복되므로 테이블의 모든 데이터를 찾아내지만 결과는 아무것도 나오지 않게 된다. 이러한 이유로 SQL을 작성할 때 ROWNUM 조건은 반드시 1이 포함되어야 한다. 

 

SQL에 ROWNUM 조건이 1이 포함되도록 다음과 같이 수정해보면 결과가 나오는 것을 볼 수 있다. 

 

-- ROWNUM은 반드시 1이 포함되도록 해야 한다.
select /*+INDEX_DESC(tbl_board pk_board) */
rownum rn, bno, title, content 
from 
tbl_board
where rownum <= 20;

 

달라진 점은 ROWNUM 조건이 1을 포함하도록 변경한 것뿐이다. 위의 SQL 결과는 아래와 같이 역순으로 데이터를 20개 가져오게 된다. 

 

4-3. 인라인뷰 처리

 

10개씩 목록을 출력하는 경우 2페이지의 데이터 20개를 가져오는 데는 성공했지만, 1페이지의 내용이 같이 출력되는 문제가 있으므로 마지막으로 이 문제를 수정해야 한다. 이 문제를 해결하기 위해서는 인라인뷰라는 것을 이용하는데 인라인뷰를 쉽게 설명하자면 'SELECT 문 안쪽 FROM에 다시 SELECT문' 로 이해할 수 있다. 인라인뷰는 논리적으로는 어떤 결과를 구하는 SELECT 문이 있고, 그 결과를 다시 대상으로 삼아서 SELECT를 하는 것이다.

 

데이터베이스에서는 테이블이나 인덱스와 같이 뷰라는 개념이 존재한다. 뷰는 일종의 창문같은 개념으로 복잡한 SELECT 처리를 하나의 뷰로 생성하고, 사용자들은 뷰를 통해서 만들어진 결과를 마치 하나의 테이블처럼 쉽게 조회한다는 개념이다. 

 

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

 

외부에서 SELECT 문은 인라인뷰로 작성된 결과를 마치 하나의 테이블처럼 사용한다. 예를 들어, 위의 경우 20개의 데이터를 가져오는 SAL을 하나의 테이블처럼 간주하고 바깥쪽에서 추가적인 처리를 하는 것이다. 

 

인라인뷰를 적용한 2페이지 데이터의 처리는 아래와 같이 작성될 수 있다.

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

 

이 과정을 정리하면 다음과 같은 순서이다.

 

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