저자 Harry Fuecks
Paged result set을 구현하는 방법, 특히 큰 용량의 데이터셋을 효과적으로 디스플레이 하는 방법에 대해 알아 봅니다.
아티클 관련 다운로드:
Oracle Database 10g
Zend Core for Oracle
Apache HTTP Server 1.3 and later
게시일: 2005년 11월
Paged result set은 쿼리 결과 셋을 효율적으로 디스플레이 하기 위한 방법으로 널리 사용되고 있습니다. 가장 잘 알려진 예의 하나로 구글 검색 결과 페이지의 “결과 페이지” 내비게이션을 들 수 있습니다.
본 문서에서는 PHP/Oracle 환경에서 paged result set을 구현하는 방법, 특히 큰 용량의 데이터셋을 효율적으로 디스플레이 하는 방법을 설명합니다.
Paged Result Set의 필요
사용자가 스크롤 업/다운 작업을 수행하는 동안에도 결과 커서가 오픈 되어 있는 데스크톱 GUI와 달리, 웹 기반 애플리케이션은 HTTP 프로토콜의 “stateless”한 요청/응답 라이프사이클의 제약을 받습니다. 한 차례 요청에 대한 응답이 전달되고 나면 세션은 종료됩니다. 단 한 번의 요청을 통해 수천 개의 로우가 HTML 테이블의 형태로 전달되는 경우라면 하부 인프라스트럭처는 물론, 사용자 역시 심각한 성능 저하 현상을 경험할 수 밖에 없습니다.
이러한 문제를 해결하기 위한 검증된 방법으로 “페이지(page)”라는 추상적인 개념이 활용되고 있습니다. 페이지는 결과 셋의 상대적인 위치를 지정하는 용도로 사용됩니다. 페이지 넘버는 URL을 통해 전달되며, 따라서 (Rasmus Ledorf의 아티클, "PHP 하십니까?"에서 지적된 PHP의 비공유(shared-nothing) 아키텍처를 이루는 핵심 요소의 하나인) HTTP의 “stateless” 속성을 보장할 수 있습니다. 서버에서, 요청된 페이지 넘버는 로우 시퀀스로 매핑되고, 조회 결과가 사용자에게 전달됩니다.
샘플 데이터
본 문서에서는 Feedster Top 500 Blog 블로그 데이터를 샘플 데이터로 사용합니다. 이 데이터는 http://top500.feedster.com/top500.tab에서 온라인으로 조회가 가능하며, 파싱이 용이하다는 장점이 있습니다. 데이터는 아래 테이블에 저장됩니다:
CREATE TABLE feedster_top_blogs (
rank NUMBER PRIMARY KEY,
name VARCHAR2(200),
url VARCHAR2(200),
links NUMBER
);
TPaged result set을 구현하기 위해서는 애플리케이션의 모든 계층에서 작업이 이루어져야 합니다. 먼저 특정 페이지에 해당되는 로우만을 포함하는 결과 커서를 반환하기 위한 SELECT 쿼리를 작성하고, 웹 서버 및 데이터베이스 서버 간의 트래픽을 최소화해야 합니다. 또 결과 셋을 브라우징 하기 위해 사용자 인터페이스에 “페이저(pager)”를 구현해야 합니다. 마지막으로, 페이지 넘버를 결과 셋의 로우 넘버로 변환하기 위한 로직이 구현되어야 합니다.
사용자 인터페이스
그러면 사용자가 “페이지 넘버”를 이용하여 조회하고자 하는 결과 셋을 지정할 수 있는 기능을 구현해 보기로 합시다. 사용자가 요청한 페이지 넘버는 URL을 통해 전달됩니다 (예: http://example.com/results.php?page=5). 이는 HTTP 프로토콜의 “stateless” 속성을 반영하는 것입니다. 사용자는 URL을 수정함으로써 쉽게 “5 페이지”로 이동할 수 있습니다. 물론 시간이 지나 데이터가 변경된 경우라면, 특정 페이지에서 출력되는 결과 또한 달라질 수 있습니다 (아래의 읽기 일관성 섹션을 참고하십시오).
데이터셋에 대한 사용자 내비게이션을 지원하기 위해, 다른 페이지로의 링크를 제공하는 “페이저” 엘리먼트를 구현해야 합니다. 지원 가능한 페이지의 수에 제한이 없기 때문에 (실제로 페이지의 수는 무한히 늘어날 수 있습니다), 현재 페이지를 기준으로 “슬라이딩 윈도우(sliding window)”를 두어야 합니다. 이와 같이 함으로써 사용자에게 지나치게 많은 정보가 한꺼번에 제공되는 것을 방지하고, 브라우징의 편의성을 높일 수 있습니다.
아래 예제에서는 액티브 페이지의 앞 뒤로 최대 5개 페이지만을 디스플레이하는 것으로 구현하였습니다. 이처럼 “슬라이딩 윈도우”를 설정하는 것 이외에도, 이전 및 다음 페이지를 위한 링크를 제공하고 있습니다.
아래 예제는 PHP 함수를 통해 페이저 “컴포넌트”를 구현하고, 임의의 URL이 함수에 전달되는 경우 페이지 링크를 포함한 HTML 출력을 반환하는 내용을 포함하고 있습니다:
<?php
function draw_pager($url, $total_pages, $current_page = 1) {
if ( $current_page <= 0 || $current_page > $total_pages ) {
$current_page = 1;
}
if ( $current_page > 1 ) {
printf( "<a href='$url?page=%d'>[Start]</a> \n" , 1);
printf( "<a href='$url?page=%d'>[Prev]</a> \n" , ($current_page-1));
}
for( $i = ($current_page-5); $i <= $current_page+5; $i++ ) {
if ($i < 1) continue;
if ( $i > $total_pages ) break;
if ( $i != $current_page ) {
printf( "<a href='$url?page=%1\$d'>%1\$d</a> \n" , $i);
} else {
printf("<a href='$url?page=%1\$d'><strong>%1\$d</strong></a> \n",$i);
}
}
if ( $current_page < $total_pages ) {
printf( "<a href='$url?page=%d'>[Next]</a> \n" , ($current_page+1));
printf( "<a href='$url?page=%d'>[End]</a> \n" , $total_pages);
}
}
?>
총 50 페이지 분량의 데이터셋에 대해 현재 페이지 15를 기준으로 위 함수를 호출하는 코드가 아래와 같습니다:
draw_pager('http://example.com/results.php',50,15);
결과로 출력되는 HTML은 아래와 같습니다:
<a href='http://example.com/results.php?page=1'>[Start]</a>
<a href='http://example.com/results.php?page=14'>[Prev]</a>
<a href='http://example.com/results.php?page=10'>10</a>
<a href='http://example.com/results.php?page=11'>11</a>
<a href='http://example.com/results.php?page=12'>12</a>
<a href='http://example.com/results.php?page=13'>13</a>
<a href='http://example.com/results.php?page=14'>14</a>
<a href='http://example.com/results.php?page=15'><strong>15</strong></a>
<a href='http://example.com/results.php?page=16'>16</a>
<a href='http://example.com/results.php?page=17'>17</a>
<a href='http://example.com/results.php?page=18'>18</a>
<a href='http://example.com/results.php?page=19'>19</a>
<a href='http://example.com/results.php?page=20'>20</a>
<a href='http://example.com/results.php?page=16'>[Next]</a>
<a href='http://example.com/results.php?page=50'>[End]</a>
위 HTML은 Amazon의 A9 검색 페이지와 유사한 방식으로 동작합니다. 이에 반해, 구글 사이트의 경우에는 페이지 넘버가 아닌 시작 로우 넘버(starting row number)를 URL을 통해 전달하는 방식을 사용하고 있으며, 현재 페이지를 기준으로 한 페이지 번호를 표시하는 대신 1-10, 11-20 등의 순으로 링크를 디스플레이하는 방식을 사용합니다.
draw_pager() 함수가 전달된 URL을 변환하는 과정에서 오류가 발생할 수 있음에 주의해야 합니다. 아래와 같은 베이스 URL을 전달한 경우를 가정해 봅시다:
http://example.com/results.php?orderby=name
draw_pager() 함수는 아래와 같은 URL을 생성합니다:
http://example.com/results.php?orderby=name?page=5
하지만 실제로 GET 쿼리 매개변수 뒤에 다시 페이지 번호를 붙이는 경우에는 “&” 기호가 사용되는 것이 맞습니다. 따라서 아래와 같은 URL이 생성되어야 합니다:
http://example.com/results.php?orderby=name&page=5
이러한 문제를 해결하기 위해 PHP의 parse_url() 함수를 활용할 수 있습니다. 하지만 이미 매개변수가 사용된 URL에 “&page”를 붙이는 것을 피하고, HTML로 출력하기 전에 모든 입력 매개변수를 검증하는 작업을 거쳐야 합니다. 이와 같이 하지 않는 경우, XSS 보안 공격에 이용될 수도 있습니다.
페이지 넘버/로우 넘버 변환
위의 “페이저” 예제 코드에서 두 가지 계산이 필요하다는 점을 참고하시기 바랍니다. 우선 전체 페이지의 수를 구해야 하고, 다음으로는 페이지 넘버를 로우 넘버로 변환해 주어야 합니다. 이를 위해서는 먼저, 하나의 페이지에 표시되는 로우의 수를 정의할 필요가 있습니다 (실제 구현 시에는 사용자가 이 값을 변경할 수 있게 하는 것을 고려할 수도 있습니다.)
한 페이지에 표시되는 로우의 수를 정의했다면, 아래 total_pages() PHP 함수를 이용하여 페이지의 전체 숫자를 출력합니다:
function total_pages($total_rows, $rows_per_page) {
if ( $total_rows < 1 ) $total_rows = 1;
return ceil($total_rows/$rows_per_page);
}
$total_rows란 결과 값으로 반환될 수 있는 모든 로우의 수를 의미합니다. 샘플 데이터의 경우, 전체 로우의 수는 정확히 500 개입니다. 하지만 테이블의 로우 숫자가 계속 변경되는 경우라면, 데이터베이스에 이 값을 조회해야 합니다 (읽기 일관성 섹션을 참고하시기 바랍니다). total_pages() 함수에서 디스플레이할 최대 페이지 수에 제한을 둘 수도 있습니다. (최대 페이지 수 섹션을 참고하십시오.)
특정 페이지 넘버를 결과 셋의 로우 넘버로 변환하기 위해 사용하는 함수가 아래와 같습니다:
function page_to_row($current_page, $rows_per_page) {
$start_row = ($current_page-1) * $rows_per_page + 1;
return $start_row;
}
위 함수는 $current 페이지에 해당하는 결과 셋의 첫 번째 로우 넘버를 반환합니다.
읽기 일관성
본 문서에서는 데이터셋의 페이지 출력이 (웹/HTTP 환경에서 불가피하게 적용되는) “stateless” 속성을 갖는다고 가정하였습니다. 따라서 HTTP 요청이 진행되는 과정에서 로우의 INSERT/DELETE/UPDATE 작업이 발생하여 로우의 상대적인 위치 및 순서, 또는 전체 페이지 수가 변경되는 경우, 일관성에 관련한 문제가 발생할 수도 있습니다.
데이터셋이 자주 변경되지 않는 경우에는 이것이 심각한 문제가 되지 않습니다. 사용자가 일관성의 문제를 확인하는 경우는 흔치 않기 때문입니다. 하지만 변경 빈도가 높고 중요한 데이터라면 일관적인 뷰를 제공하는 것이 필수적이며, 따라서 데이터셋에 대한 “스냅샷”을 주기적으로 생성하고 URL을 통해 “스냅샷 타임” 정보를 추가로 전달하는 방법을 고려해 볼 필요가 있습니다. 이러한 방법으로 세션의 “stateless” 속성을 유지한 상태에서 데이터의 양과 보존 기간을 동시에 관리하는 것이 가능합니다.
또 다른 대안으로 사용자 세션 별로 데이터셋의 정적인 복사본을 저장하고, 데이터셋을 “stateful”한 상태로 관리하는 방법을 사용할 수도 있습니다. 이 경우 각각의 사용자는 일관적인 뷰를 확보하게 되며, 데이터셋의 수명(age)와 같은 문제로부터 자유로워질 수 있습니다. 하지만 각각의 사용자가 별도의 데이터셋 복제본을 가져야 하므로, 사용자의 수가 많은 경우 스토리지 여유 공간이 급격하게 줄어들 수 있습니다. 그 대신, 대부분의 사용자가 데이터셋의 처음 몇 페이지만을 필요로 한다는 점에 착안하여, 해당 데이터만을 복사하여 관리하는 최적화 테크닉을 적용해 볼 수도 있습니다.
실제로 데이터를 어디에 저장할 것인가의 문제는 요구사항 또는 환경에 따라 달라지게 됩니다. 경우에 따라서는 PHP 데이터 구조에 대해 serialize 함수를 적용하거나 Service Data Objects 익스텐션을 활용하는 것이 편리할 수 있습니다. 또는 여러 개의 테이블을 이용하여 스냅샷을 저장하는 방식을 고려해 볼 필요가 있을 수 있습니다.
일반적인 환경에서는, 꼭 필요한 경우를 제외하고는 데이터셋을 "stateful"한 상태로 관리하는 방법을 피하는 것이 좋습니다. 애플리케이션 로직의 복잡성이 증가하고, 데이터 관리가 어려워지며, 최대 부하 상태에서 서버 성능이 저하될 위험이 높아지기 때문입니다.
로우 수의 계산
이제 SELECT 구문을 통해 반환되는 로우의 수를 계산하기 위해, SELECT 로우 수를 계산하기 위한 PHP 함수를 아래와 같이 별도로 구현할 수 있습니다.
SELECT 구문을 통해 반환되는 로우의 수를 계산하기 위해, 로우 수를 계산하기 위한 PHP 함수를 아래와 같이 별도로 구현할 수 있습니다:
function count_rows(& $conn, $select) {
$sql = "SELECT COUNT(*) AS num_rows FROM($select)";
$stmt = oci_parse($conn,$sql);
oci_define_by_name($stmt,"NUM_ROWS",$num_rows);
oci_execute($stmt);
oci_fetch($stmt);
return $num_rows;
}
COUNT를 이용하는 것은 그리 효율적인 방법은 아닙니다. 하지만 결과 커서에 의해 반환되는 전체 로우 수는 마지막 로우가 조회되는 시점까지 확인이 불가능합니다. 결국 유일한 대안은 전체 결과 셋에 대해 SELECT 쿼리를 수행하고 루프를 돌리는 것이지만, 이 방법은 COUNT보다도 훨씬 비효율적입니다.
John Lim의 아티클 "Optimizing PHP and Oracle" 에는 별도의 테이블과 INSERT와 DELETE 트리거를 이용하여 전체 로우 수를 추적하는 방법이 소개되고 있습니다. 또, 데이터가 자주 변경되지 않는 경우라면, 웹 서버의 결과를 일정 기간 캐시에 저장하는 방법을 고려해 볼 수도 있습니다.
count_rows()함수는 전달된 SELECT 구문에 대한 매개변수 바인딩을 지원하지 않습니다. 매개변수의 바인딩이 필요한 경우 (특히 전체 로우 수를 변경해야 하는 경우), count_rows(), which you loop over while calling oci_bind_by_name를 호출하는 루프를 구현할 수도 있습니다.
또 이 함수를 이용하여 반환되는 값의 수를 제한하고, 대용량 페이지셋에서 너무 많은 페이지가 반환되는 문제를 방지할 수 있습니다.
(최대 페이지 수 섹션을 참고하십시오.)
많은 양의 데이터 처리가 필요한 환경이라면, 로우 수를 계산하는 로직을 생략하는 것을 고려해 볼 수도 있습니다. 반환 가능한 페이지의 수를 제한할 수 있다는 점을 감안한다면 (최대 페이지 수 섹션 참고), 위의 count_rows() 함수가 50 보다 큰 숫자를 반환하게 하고, “검색 결과 50 페이지 이상의 데이터가 반환되었습니다”와 같은 메시지를 사용자에게 전달하는 것도 가능합니다."
데이터 셋의 페이지 가져오기
특정 페이지에서 사용할 로우를 SELECT 하기 위해서 아래와 같은 “SQL 래핑(wrapping)” 함수를 활용하는 것이 가능합니다:
function & paged_result(& $conn, $select, $start_row, $rows_per_page) {
$sql = "SELECT
*
FROM
(
SELECT
r.*, ROWNUM as row_number
FROM
( $select ) r
WHERE
ROWNUM <= :end_row
)
WHERE :start_row <= row_number";
$stmt = oci_parse($conn,$sql);
oci_bind_by_name($stmt, ':start_row', $start_row);
// Calculate the number of the last row in the page
$end_row = $start_row + $rows_per_page - 1;
oci_bind_by_name($stmt, ':end_row', $end_row);
oci_execute($stmt);
// Prefetch the number of rows per page
oci_set_prefetch($stmt, $rows_per_page);
return $stmt;
}
여기서 두 개의 SELECT 서브쿼리가 사용되었음을 참고하시기 바랍니다. 가장 안쪽의 SELECT 서브쿼리는 $end_row를 마지막으로 하는 모든 로우를 반환하고, 중간의 서브쿼리는 이 결과에 대해 $start_row를 시작점으로 필터링을 수행합니다. 이처럼 $end_row와 $end_row를 기준으로 액세스하고자 하는 로우를 필터링하는 것이 가장 효율적인 방법입니다.
그렇다면 BETWEEN을 사용하는 것이 더 간단하지 않을까요?:
$sql = "SELECT
*
FROM
(
SELECT
r.*, ROWNUM as row_number
FROM
( $select ) r
)
WHERE row_number BETWEEN :start_row AND :end_row";
하지만 BETWEEN을 사용하면, 먼저 결과 셋의 모든 로우를 가져온 후 필터를 이용하여 영역을 좁히는 방법을 사용하기 때문에 효율성이 떨어진다는 문제가 있습니다.
또 oci_set_prefetch() 함수를 호출하고 이 함수에 페이지 당 로우의 수를 전달한 부분을 참고하시기 바랍니다. 이 방법을 사용하면 웹 서버와 데이터베이스 간의 네트워크 트래픽을 줄일 수 있습니다. (John Lim의 아티클," PHP와 오라클의 최적화"에서 prefetching에 대한 상세한 정보를 확인하실 수 있습니다.)
위에서 설명한 count_rows() 와 마찬가지로paged_result() 역시 매개변수의 바인딩을 지원하지 않습니다. ADOdb와 PEAR::DB 라이브러리는 이 문제를 해결하기 위한 방안을 각각 제공하고 있습니다.
최대 페이지 수
위의 paged_result() 함수 사용 예제에는 한 가지 심각한 문제점이 내포되어 있습니다. 일반적으로 결과 셋의 안쪽으로 들어가면 들어갈 수록, 응답시간은 더욱 느려집니다. $end_row 를 먼저 확인하고 $start_row 이후의 로우를 필터링하는 방식이 사용되기 때문입니다. 결과 셋의 용량이 크고 트래픽이 많은 사이트의 경우, 많은 수의 페이지를 요청하는 사용자의 응답시간이 용납할 수 없는 수준까지 증가할 수도 있습니다.
이러한 문제를 예방하기 위해서, 한 번에 요청 가능한 최대 페이지수를 제한하는 것이 도움이 될 수 있습니다. 단순한 검색 창이라면 이러한 제한을 두는 것이 당연할 수 있습니다. 사용자가 몇 페이지에 걸쳐 검색 결과를 조회한 후에도 필요한 자료를 찾지 못했다면, 다른 조건으로 검색을 재실행하는 것이 일반적입니다. 또는 컬럼 기준 정렬 기능을 이용하여 제한된 수의 페이지에서 필요한 자료를 찾을 수 있도록 지원할 수도 있습니다.
최종 스크립트의 작성
위에서 설명한 함수들을 pager_functions.php라는 이름의 PHP 스크립트 내에 구현했다면, Feedster 데이터의 페이지 내비게이션을 위한 스크립트를 아래와 같이 작성할 수 있습니다:
<?php
$conn = OCILogon('scott', 'tiger') or die ("Unable to connect to db");
require_once 'pager_functions.php';
$rows_per_page = 20;
$url = 'feedster_top500.php'; // URL to this script
$sql = 'SELECT * FROM feedster_top_blogs ORDER BY rank ASC'; // The unfiltered SELECT
// Get the total page count from the number of rows
$total_rows = count_rows($conn,$sql);
$total_pages = total_pages($total_rows, $rows_per_page);
// Make sure the page number is a sane value
if ( !isset($_GET['page']) ||
!preg_match('/^[0-9]+$/',$_GET['page']) ||
$_GET['page'] < 1 ) {
$_GET['page'] = 1;
} else if ( $_GET['page'] > $total_pages ) {
$_GET['page'] = $total_pages;
}
// Translate the page number into a starting row number
$start_row = page_to_row($_GET['page'], $rows_per_page);
// Filter to a single page of rows
$stmt = & paged_result($conn, $sql, $start_row, $rows_per_page);
?>
<table width="600">
<caption>Feedster Top 500 Blogs [#<?php echo $_GET['page']; ?>]</caption>
<thead>
<tr>
<th>Rank</th>
<th>Blog</th>
<th>Inbound Links</th>
</tr>
</thead>
<tbody>
<?php while (OCIFetchinto($stmt,$row,OCI_ASSOC)) { ?>
<tr valign="top">
<td align="right"><?php echo htmlspecialchars($row['RANK']); ?></td>
<td>
<a href="<?php echo htmlspecialchars($row['URL']); ?>">
<?php echo htmlspecialchars($row['NAME']); ?>
</a>
</td>
<td align="right"><?php echo htmlspecialchars($row['LINKS']); ?></td>
</tr>
<?php } ?>
</tbody>
<tfoot>
<tr>
<td colspan="3" align="center">
<?php echo draw_pager($url, $total_pages, $_GET['page']); ?>
</td>
</tr>
</tfoot>
</table>
아래 그림에서 볼 수 있듯, 페이저는 테이블의 하단 부분, <tfoot/> 태그 내에 디스플레이 됩니다.
결론
지금까지 paged result set을 구현하는데 필요한 구성요소와, 이에 관련한 성능 이슈들을 살펴보았습니다. 본 문서에서 예시된 코드들을 여러분의 애플리케이션을 확장하는데 활용하시기 바랍니다.
