페이지에 덧글부분이 아이프레임으로 되어있는 페이지가 있습니다.
이에 덧글은 단다거나 페이지이동을 한다면 아이프레임부분에 그만큼 히스토리가 쌓이게되며, 브라우저의 뒤로가기 버튼을 클릭시에는 그 히스토리만큼 뒤로간 후에 페이지자체가 뒤로 이동을 하더라고요....
아이프레임에서 히스토리를 남기지 않게 링크를 시킨다 등등의 방법이 있습니다만...
폼전송 등의 문제로 힘들것같아서...
혹시 이 포스팅을 보시는분께서 해결법을 아신다면 조언부탁드리겠습니다 ^^;;
Posted by web20korea
,
뒤로가기,
아이프레임 뒤로가기
Posted by web20korea
,
윈도우 바로가기 키
SQL Server에서 사용할 수 있는 서버측 페이징기법
Andrew Rosca
웹 애플리케이션은 일반적으로 사용자에게 많은 양의 정보를 제공하기 위해 페이징 기법을 사용한다.
예를 들어 인터넷 검색엔진은 사용자의 쿼리 결과로 대용량의 결과값을 반환한다.
이 때 검색엔진이 한번에 결과값 전체를 반환하게 되면 결과값을 받는 클라이언트측 시스템에 과부하가 발생할 수 있다.
하지만 페이징 기법을 사용하게 되면, 반환되는 결과값을 클라이언트측과 서버측 양쪽에서 관리할 수 있을 만큼의 고정된 크기의 블록으로 구분하여 한 번에 이동시키는 정보의 양을 줄일 수 있다. 애플리케이션에서는 한 번 에 소수의 레코드만 사용자에게 보내게 되며, 결과값 중에서 사용자가 필요로 하는 정보만 반환하게 된다.
페이징 기법을 사용하면 데이터를 사용자가 좀 더 이해하고 표현하기 쉽게 해 줄 뿐만 아니라, 대량의 정보를 조회하고 표현하기 위해서 시스템에 불필요한 과부하가 발생하여 결국 시스템의 성능에 악영향을 미치지 않도록 통제하기 때문에 전체적인 시스템 성능을 향상시키게 된다.
정상적으로 시스템에 반환된 결과값 레코드가 페이징되었다면, 검색엔진을 사용하는 사용자는 대부분 맨 처음 한 페이지 또는 일부 페이지만 조회하게 될 것이다.
불행하게도 많은 프로그래머들이 페이징 관련해서 성능측면에서 매우 중요한 고려사항에 대해서 잘 모르고 있다.
IIS와 SQL Server를 사용하는 환경에서는,
AbsolutePage, PageSize, PageCount와 같은,
표준 ADO RecordSet 페이징 기능을 사용하는 것이 가장 일반적인 페이징 기법이다.
소량(수십 또는 수백 레코드 정도)의 데이터에 대해서는 이러한 기능을 사용하면 정상적으로 동작하고, 시스템의 성능측면에서도 부하를 발생시키지 않는다. 하지만 레코드 수가 증가하게 되면, 이러한 기능을 사용하게 되면 효율성이 감소하게 되고 전체적인 애플리케이션의 성능에 악영향을 미치게 된다.
대량의 발주정보를 조회해야 하는 구매조달관련 애플리케이션, 수 천명의 회원이 동시에 접속하는 미팅 웹사이트, 고객의 검색조건에 따라 수백 개의 상품의 정보를 표시해야 하는 대규모 전자상거래 웹사이트와 같이, 대용량 데이터를 처리해야 하는 애플리케이션의 경우에는 좀 더 개선된 서버측 페이징 기술이 필요하게 된다. 이번 호의 기사에서는 수백만 개의 행정보를 포함하는 테이블에서도 사용할 수 있는 페이징 기법에 대해서 소개하고자 한다.
ADO RecordSet 페이징 기법의 한계
대용량의 레코드를 페이징하기 위해서 ADO RecordSet의 페이징기법을 사용할 때 발생하는 문제의 원인은 ADO에서 데이터를 처리하는 방법 때문이다.
ADO 기술구조에서는 데이터베이스에서 정보를 조회하기 위해 서 조회의 대상이 되는 데이터에 대한 포인터를 관리해야 할 필요가 있다.
데이터에 대한 포인터를 커서 라고 하며, 클라이언트측(예를 들어 ASP 페이지)에서는 각 레코드를 건별로 조회하게 된다.
ADO RecordSet 개체는 서버측 커서(기본값)와 클라이언트측 커서 유형을 지원한다.
서버측 커서를 사용하게 되면 모든 데이터는 그대로 SQL Server에 두고, 해당 데이터가 필요한 시점에 순서에 따라 각 레코드를 조회하게 된다.
클라이언트측 커서를 사용하게 되면 필요한 모든 데이터를 클라이인트로 전송한 다음, 클라이언트측 커서를 사용하여 클라이언트측 버퍼 메모리에 있는 데이터를 레코드별로 조회하게 된다.
검색엔진 예제에서처럼 쿼리의 결과값 중에서 일부분만을 표시하거나 사용해야 하는 경우라면 SQL 서버가 클라이언트에서 요청하는 페이지만 전송하고 전체 결과값 중 나머지 레코드는 데이터베이스 서버에 그대로 남겨두게 되는, 서버측 커서를 사용하는 것이 효율적이다.
서버측 커서를 사용하게 되면 클라이언트로 전송되는 레코드 수가 특정 페이지를 구성하는 20~30 레코드 정도로 제한된다는 것이다.
PageCount 와 같은 일부 레코드셋 페이징 기능을 사용하기 위해서는 클라이언트측 커서를 사용해야 한다.
클라이언트 커서를 사용할 수 있도록 ADO를 설정하기 위해서는 RecordSet의 ClientLocation 속성을 adUseServer에서 adUseClient로 변경해 주면 된다.
[리스트 1]의 VB 코드는 RecordSet 개체에서 클라이언트측 커서와 서버측 커서를 사용하는 방법에 대한 예제가 나타나 있다. ClientLocation 속성을 asUseClient 로 변경하게 되면, 사용자 쿼리의 결과로 반환되는 데이터에서 필요로 하는 페이지 수를 판단하기 위해 결과값 전체가 클라이언트로 전송된다.
예를 들어 데이터베이스로부터 5000 레코드를 반환하는 쿼리를 실행했다고 가정하자.
애플리케이션에서 서버측 커서를 사용하게 되면 반환되는 레코드를 한 페이지당 20 레코드씩으로 페이징하고, 사용자가 1 페이지만 보고 있는 경우라면 애플리케이션에서는 클라이언트로 맨 처음 페이지를 구성하는 20 레코드만을 전송하면 된다.
그 다음 사용자가 두번째 페이지로 이동하면 애플리케이션에서는 21~40번 레코드만 클라이언트로 전송하게 된다. 반면에 클라이언트측 커서를 사용하게 되면 ADO에서는 비록 사용자가 단지 첫 페이지에 해당하는 20 레코드만 필요한 경우라도 5000 레코드 전체를 클라이언트로 전송하게 된다.
이렇게 전체 레코드를 전송하게 되면 결과값이 사용자에게 나타나는 시간이 지연되게 되고, 반환되는 레코드 수가 매우 많은 경우에는 성능에 심각한 악영향을 미칠 수 있다.
다른 페이징 기법
ADO RecordSet 페이징 기법과 관련한 문제가 애플리케이션의 성능에 영향을 미치게 되었기 때문에 필자는 수천 레코드를 페이징해야 하는 웹 애플리케이션에서 사용할 수 있는 다른 페이징 기법을 찾아 보았다.
SQL 서버의 인덱스를 활용하여 전체 결과집합 중에서 상위의 레코드를 선택하는 방법을 소개하고자 한다.
다음은 Northwind 데이터베이스의 Orders 테이블에서 상위 10개의 레코드를 선택하는 쿼리이다.
SELECT TOP 10 * FROM Orders
위의 구문을 활용하면 전체 결과집합에서 10 개의 레코드 단위로 결과값이 반환되게 할 수 있다.
주어진 페이지에 해당하는 레코드만 선택하기 위해서는, 한 페이지에 몇 개의 레코드를 포함시킬 것인지 결정하고, 실제 사용자가 몇 번째 페이지의 정보를 조회하기를 원하는지에 대한 페이지 카운트를 알고 있어야 한다.
예를 들어 한 페이지에 10 개의 레코드가 포함되고, 사용자가 전체 결과값 집합 중에서 3 페이지를 조회하고자 하는 경우라면 다음과 같은 쿼리를 사용하면 된다.
SELECT TOP 10 * FROM Orders WHERE OrderID NOT IN (SELECT TOP 20 OrderID FROM Orders)
위의 쿼리는 맨 처음 20 개의 레코드 이후에 존재하는 10개의 레코드, 즉 21~30번까지의 레코드를 반환한다. 위의 쿼리를 절차코드로 일반화하게 되면 다음과 같이 표현할 수 있다.
SELECT TOP page_size * FROM Orders WHERE OrderID NOT IN (SELECT TOP (page_size * (current_page - 1)) OrderID FROM Orders)
위의 쿼리는 대량의 레코드를 반환하는 경우라도 잘 동작하지만, 반환할 페이지의 숫자가 많아질수록 전체적인 성능은 감소하게 된다.
문제의 원인은 IN 연산자에 포함되는 쿼리의 결과값이 많아지면서 비효율성이 증가하기 때문이다. 예를 들어 한 페이지당 10개의 레코드를 반환하는 결과집합 중에서 500번째 페이지를 조회하기 위해 쿼리를 실행하게 되면, IN 연산자의 대상이 되는 서브쿼리에는 다음과 같은 문장이 포함되게 된다.
(SELECT TOP 4990 OrderID FROM Orders)
마지막 10개의 레코드를 조회하기 위해 서버에서는 4990 개의 OrderID와 각 OrderID를 비교해야만 한다.
불필요한 비교작업이 많이 발생하게 된다.
물론 SQL Server의 경우에는 이러한 경우 데이터를 좀 더 빠르고 효율적인 방법으로 검색하기 위해서 인덱스를 사용하여 빠르게 쿼리를 처리하게 된다.
(OrderID가 기본키로 설정되어 있기 때문에, SQL Server는 기본적으로 인덱스를 사용하게 된다.)
조회하고자 하는 페이지 수가 증가함에 따라 성능면에서 느려지게 된다고 하더라도 이러한 조회성능의 감소현상은 조회하고자 하는 페이지 수가 매우 큰 경우에만 인식할 수 있게 된다.
이러한 점증적인 성능의 감소현상은 대부분의 경우 사용자가 맨 처음부분의 일부 페이지만 조회하게 되고, 맨 처음부분의 일부 페이지를 조회 할 경우에는 매우 빠른 성능을 보장할 수 있기 때문에 크게 중요한 관심의 대상이 되지 않는다.
애플리케이션이션에서 적절하게 데이터를 정렬하고 필터링한다면 사용자는 찾고자 하는 정보를 거의 한 두 페이지 이내에서 찾게 된다. 만약 맨 처음 부분의 페이지에서 필요로 하는 데이터를 찾지 못한 경우에는 대부분의 사용자는 예제에서처럼 500 페이지까지 원하는 데이터를 찾고자 계속 다음 페이지를 찾아보는 것이 아니라 다른 정렬 및 필터링 조건으로 새로운 쿼리를 하게 된다.
앞에서 언급한 것과 같이 검색작업을 수행할 때, 테이블의 기본키가 어떤 컬럼에 설정되었는지가 매우 중요한 역할을 하게 된다.
기본키에는 각 레코드를 유일하게 식별할 수 있는 컬럼이 포함되어야 하며, 쿼리를 실행할 때 기본키를 기준으로 해당 레코드를 쿼리의 결과값으로 선택할 것인지 무시할 것인지를 판단하게 된다.
앞의 일반화된 쿼리에는 정렬 및 기본키에 관련한 WHEHE 절이나 ORDER BY 절 내용이 누락되어 있다. 목록 2에는 이러한 요소를 포함시킨 일반화된 쿼리가 나타나 있다.
정렬은 일반적으로는 비효율적인 작업이며, 쿼리의 성능을 저하시키는 원인이 되기도 한다.
SQL 서버의 경우, 인덱스가 설정된 컬럼에 대해서는 매우 효율적으로 정렬 및 필터링 작업을 할 수 있고, SQL 서버가 항상 기본키 컬럼에는 인덱스를 생성하기 때문에 쿼리를 좀 더 최적화하여 빠르게 실행한다.
기본키에 설정된 인덱스를 최대한 활용하기 위해서, [리스트 3]과 같이 테이블의 전체 컬럼을 선택하지 않고, 먼저 기본키의 조건으로 대상이 되는 레코드를 검색한 다음에 결과값에 포함되어 있는 기본키값으로 다시 해당 레코드에 대한 전체 컬럼을 찾아오게 할 수도 있다.
테이블에 기본키 인덱스가 설정되어 있고, 인덱스가 설정된 필드에 대해서만 정렬 및 필터링 작업을 하게 되면, 조회하고자 하는 레코드를 찾기 위해 인덱스 페이지만을 사용하게 된다. 이렇게 쿼리의 결과값에 필요한 전체 필드가 인덱스에 포함되어 있는 경우를 커버된 인덱스라고 한다.
동일한 쿼리를 실행시킨 경우라도 해당 쿼리가 커버된 인덱스를 사용하는 경우가 일반 테이블에 대해서 쿼리하는 경우보다 더 빠르게 된다. 맨 마지막으로 선택된 결과값에 해당하는 나머지 정보를 조회하기 위한 작업을 수행할 때에는(즉, SELECT * 부분), 기본키 인덱스를 사용하여 SQL Server가 해당 인덱스를 바로 찾을 수 있기 때문에 매우 효율적으로 쿼리를 수행하게 된다.
[리스트3]에 나타나 있는 쿼리는 결과집합 중에서 특정 페이지를 매우 효율적이고, 단순한 방법으로 조회하게 된다. 물론, GROUP BY나 HAVING 절을 추가하여 사용할 수도 있다.
쿼리를 좀 더 단순화하기 위해서 [리스트 4]와 같이 SELECT_WITH_PAGING 라는 저장 프로시저를 생성하여, 기능을 캡슐화하였다.
SELECT_WITH_PAGING 저장프로시저에는 fields_to_return (string), primary_key (string), table_name (string), page_number (integer, default 1), page_size (integer), get_record_count (true/false), filter_conditions (string), sort_columns (string), group_by (string)와 같은 매개변수(데이터형)가 입력되게 된다.
예를 들어 한 페이지당 레코드 수를 10개로 지정하고 Northwind 데이터베이스의 Order 테이블으로부터 주문일자로 정렬하여 CustomerID, ShipName을 조회한 다음, 결과값 중 세번째 페이지를 조회하기 위해서는 다음 문장을 실행하면 된다.
EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID', 'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate'
위의 쿼리를 실행하게 되면 입력된 조건에 따라 필터링되어 반환되는 전체 레코드 수가 두번째 레코드셋으로 반환된다. 전체 레코드 수는 사용자에게 전체 페이지 수를 표시하려고 할 때 유용하게 사용되며, 대부분의 경우 사용자는 단순하게 이전페이지 또는 다음페이지로 표시되는 것보다는 전체 페이지 수 중에서 현재 조회하고 있는 페이지의 번호를 표시하는 방법을 더 선호한다.
조건에 해당하는 전체 레코드 수를 조회하기 위해서 필자는 여섯번째 매개변수를 1로 설정하였다.
만약 여섯번째 매개변수를 설정하지 않으면 데이터베이스로부터 페이지당 레코드로 제한된 10개의 레코드만을 반환하게 되기 때문에 전체 레코드 수가 몇 개인지는 알 수 없게 된다.
table_name 매개변수에는 두 개 또는 그 이상의 테이블에 대한 조인을 설정하는 문장이 올 수도 있고, 필요에 따라 서브쿼리도 올 수 있다.
예를 들어, 다음 두 문장은 table_name 매개변수에 모두 사용될 수 있다.
'Northwind.dbo.Orders A JOIN Northwind.dbo.Customers B ON A.CustomerID = B.CustomerID' '(SELECT * FROM Northwind.dbo.Orders WHERE OrderDate > ''8/1/1996'') AS tbl'
이번 호의 기사에서 소개한 페이징 기법은 레코드의 수가 매우 많은 경우에 서버측 페이징을 처리하기 위해 매우 단순하고, 효율적으로 사용할 수 있으며, 필자의 경우에는 수백만 행이 포함되어 있는 테이블에 대해서도 사용한 경험이 있다. 예를 들어 2천 5백만 레코드가 있는 테이블에 대해서 맨 처음 일부 페이지를 조회하는 쿼리를 실행할 때, ADO RecordSet 페이징 기법을 사용했을 때에는 거의 40초가 걸렸으나 이번 호에 소개한 저장프로시저를 사용한 경우에는 1초로 수행시간을 단축할 수 있었다.
이처럼 이번 호에 소개한 페이징 기법은 조회의 대상이 되는 데이터양이 많아서 ADO RecordSet 페이징 기법을 사용하게 되면 성능상 문제가 발생할 수 있는 상황에서 유용한 대안으로 사용할 수 있다.
[리스트 1] ClientLocation 속성을 변경하는 코드
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
' 연결 생성
Set objConn = New ADODB.Connection
objConn.Open "Driver=SQL Server; Server=localhost; Database=Northwind"
Set objRS = New ADODB.Recordset
' 클라이언트측 커서를 사용하게 하는 옵션
objRS.CursorLocation = adUseClient
' 서버측 커서를 사용하게 하는 옵션
objRS.CursorLocation = adUseServer
objRS.Open "SELECT * FROM Orders", objConn, adOpenStatic,
adLockOptimistic
' 이 문장은 페이징을 위해서 필요하지만, 서버측 커서를 사용하는 경우에는 에러의 원인이 된다.
Debug.Print "Total records: " & objRS.RecordCount
[리스트 2] 정렬을 위한 조건절을 지정한 일반화된 페이징 쿼리
SELECT TOP page_size * FROM table WHERE primary_key NOT IN
(SELECT TOP page_size * (page_number - 1) primary_key FROM table
WHERE filter_conditions
ORDER BY sort_field)
AND filter_criteria
ORDER BY sort_field
[리스트 3] 기본키에 검색조건을 먼저 설정하는 일반화 쿼리
SELECT * FROM table WHERE primary key IN
(SELECT TOP page_size primary_key FROM table
WHERE primary_key NOT IN
(SELECT TOP page_size * (page_number - 1) primary_key FROM table
WHERE filter_conditions ORDER BY sort_field) AND filter_criteria
ORDER BY sort_field)
ORDER BY sort_field
[리스트 4] SELECT_WITH_PAGING 저장 프로시저
CREATE PROCEDURE SELECT_WITH_PAGING (
@strFields varchar(4000),
@strPK varchar(100),
@strTables varchar(4000),
@intPageNo int = 1,
@intPageSize int = NULL,
@blnGetRecordCount bit = 0,
@strFilter varchar(8000) = NULL,
@strSort varchar(8000) = NULL,
@strGroup varchar(8000) = NULL)
/* 매개변수에 따라 반환되는 결과값을 특정 페이지로 정의하거나 전체 행을 모두 반환할 수 있도록 설정한다. */
AS
DECLARE @blnBringAllRecords bit
DECLARE @strPageNo varchar(50)
DECLARE @strPageSize varchar(50)
DECLARE @strSkippedRows varchar(50)
DECLARE @strFilterCriteria varchar(8000)
DECLARE @strSimpleFilter varchar(8000)
DECLARE @strSortCriteria varchar(8000)
DECLARE @strGroupCriteria varchar(8000)
DECLARE @intRecordcount int
DECLARE @intPagecount int
/* 페이징 조건 정규화 의미있는 페이징 조건이 입력되지 않은 경우, 페이징하지 않고 좀 더 효율적인 방법으로 쿼리를 실행시키기 위해 blnBringAllRecords 플래그를 사용 */
IF @intPageNo < 1
SET @intPageNo = 1
SET @strPageNo = CONVERT(varchar(50), @intPageNo)
IF @intPageSize IS NULL OR @intPageSize < 1 ?- 페이징하지 않고 전체 행을 반환
SET @blnBringAllRecords = 1
ELSE
BEGIN
SET @blnBringAllRecords = 0
SET @strPageSize = CONVERT(varchar(50), @intPageSize)
SET @strPageNo = CONVERT(varchar(50), @intPageNo)
SET @strSkippedRows = CONVERT(varchar(50), @intPageSize * (@intPageNo - 1))
END
/* 정렬 및 필터링 조건 정규화 정렬 및 필터링 조건이 지정되지 않으면, 필터링이나 정렬작업이 수행되지 않도록 하여 쿼리의 성능을 향상시킴.*/
IF @strFilter IS NOT NULL AND @strFilter != ''
BEGIN
SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
SET @strSimpleFilter = ' AND ' + @strFilter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilterCriteria = ''
END
IF @strSort IS NOT NULL AND @strSort != ''
SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
ELSE
SET @strSortCriteria = ''
IF @strGroup IS NOT NULL AND @strGroup != ''
SET @strGroupCriteria = 'GROUP BY' + @strGroup + ' '
ELSE
SET @strGroupCriteria = ''
/* 실제 조회작업을 시작 */
IF @blnBringAllRecords = 1 -- 페이징 하지 않고 단순한 SELECT 문장만을 실행
BEGIN
EXEC (
'SELECT ' + @strFields + 'FROM' + @strTables + @strFilterCriteria +
@strGroupCriteria + @strSortCriteria
)
END -- 전체 레코드를 반환.
ELSE -- 지정된 페이지를 반환
BEGIN
IF @intPageNo = 1 -- 맨 처음 페이지를 찾기 때문에 서브쿼리가 없어서 가장 효율적으로 실
행된다.
EXEC (
'SELECT TOP' + @strPageSize + ' ' + @strFields + 'FROM' + @strTables +
@strFilterCriteria + @strGroupCriteria + @strSortCriteria
)
ELSE -- 특정 페이지를 선택하기 위해 서브쿼리 구조를 실행한다.
EXEC (
'SELECT' + @strFields + 'FROM' + @strTables + 'WHERE' + @strPK + 'IN' + '
(SELECT TOP' + @strPageSize + ' ' + @strPK + 'FROM' + @strTables +
' WHERE' + @strPK + 'NOT IN' + '
(SELECT TOP' + @strSkippedRows + ' ' + @strPK + 'FROM' + @strTables +
@strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
@strSimpleFilter +
@strGroupCriteria +
@strSortCriteria + ') ' +
@strGroupCriteria +
@strSortCriteria
)
END -- 특정 페이지를 지정한 경우
/* 전체 레코드 수를 반환하도록 지정된 경우 */
IF @blnGetRecordCount = 1
IF @strGroupCriteria != ''
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
@strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)
)
ELSE
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM' + @strTables + @strFilterCriteria
+ @strGroupCriteria)
GO
Posted by web20korea
,
페이징,
페이징쿼리오라클에서는 rownum을 이용하면 페이징 쿼리를 간결하게 만들수 있습니다.
아래방법들은 제가 찾았던 순서대로 적어봤습니다.
----------------------------------------------------------------------------------------------------------
[첫번째방법]
select * from (
select rownum as rnum, A.* from (
[사용할 SQL 쿼리는 이부분에 작성세요]
) A where rownum <= (:PageNo + 1) * :PageSize order by rownum desc
) where rownum <= :PageSize order by rnum asc;
*주의할점
위 쿼리를 사용할경우 마지막페이지의 실제 레코드수가 PageSize 크기보다 작은경우에도
마지막 레코드부터 PageSize 만큼 가져오게 됩니다.
예를 들면 PageSize는 20 이고 마지막페이지의 레코드수가 5개일때도
위 쿼리는 마지막 레코드부터 20개를 가져옵니다.
그러면 마지막페이지와 이전페이지에 15개의 레코드가 중복되어서 보이게 되겠죠..
때문에 선택한 페이지가 마지막페이지라면 마지막페이지에 해당하는 레코드가 아닌 레코드는
아래와 같은 처리를 이용해서 삭제한 다음 바인딩하시면 됩니다.
다음 예제는 ASP.NET WITH C#를 사용했습니다.
// 위 페이징쿼리를 통해 가져온 레코드가 DataSet인 dsResult의 0번째 테이블에 들어있다고 가정합니다.
int deleteRowNum = PageSize * (PageNum-1);
DataRow[] rows = dsResult.Tables[0].Select("RNUM<="+deleteRowNum.ToString());
foreach(DataRow row in rows)
row.Delete();
----------------------------------------------------------------------------------------------------------
[두번째방법]
사용할 쿼리의 가져오는 컬럼에 ROWNUM을 추가하여 범위를 지정해서 가져온다.
select * from (
select rownum as rnum, name, address from members
) where rnum between (:PageNo * (:PageNo-1)) and ((:PageNo * (:PageNo-1)) + :PageSize )
----------------------------------------------------------------------------------------------------------
[세번째방법]
각 레코드가 포함된 페이지의 번호를 계산해서 그 번호와 페이지번호와 일치하는 레코드만 가져오는 방법입니다.
select * from members where age>20 and CEIL(rownum/:PageSize) = :PageNo
위 쿼리처럼 원하는 조건절 다음에 추가로 계산식 조건을 넣어준다음 사용하면 됩니다.
----------------------------------------------------------------------------------------
*주의할점
위 쿼리를 인덱스의 힌트와 함께 사용할 경우 다음과 같은 에러가 발생할수 있습니다.
Toad에서 실행하면 에러메시지와 함께 연결이 끊어져버리더군요..
ORA-03113: 통신 채널에 EOF 가 있습니다.
정확한 이유는 잘 모르겠네요. 원인을 아시는분께서는 속시원하게 설명좀 부탁드립니다^^;
출처 : 나무통발 (korekiss) 님의 네이버 블로그
Posted by web20korea
,
오라클 페이징 쿼리,
페이징ROWNUM의 동작 원리와 활용 방법
이번 호의 Ask Tom 컬럼은 지금까지와는 조금 다른 내용을 담고 있습니다. 필자는 오라클 데이터베이스에서 Top-N 쿼리와 페이지네이션(pagination) 쿼리를 구현하는 방법에 대해 자주 질문을 받곤 합니다. 하나의 컬럼을 통해 이러한 질문에 한꺼번에 대답하기 위한 방편으로, < Effective Oracle by Design (Oracle Press, 2003)> 의 내용을 인용하기로 했습니다. 컬럼의 포맷에 맞게 책의 내용이 다소 수정되었음을 참고하시기 바랍니다.
결과 셋의 제한
ROWNUM은 오라클 데이터베이스가 제공하는 마술과도 같은 컬럼입니다. 이 때문에 많은 사용자들이 문제를 겪기도 합니다. 하지만 그 원리와 활용 방법을 이해한다면 매우 유용하게 사용할 수 있습니다. 필자는 주로 두 가지 목적으로 ROWNUM을 사용합니다.
두 가지 활용 방안을 설명하기 전에, 먼저 ROWNUM의 동작 원리에 대해 살펴 보기로 하겠습니다
ROWNUM의 동작 원리
ROWNUM은 쿼리 내에서 사용 가능한 (실제 컬럼이 아닌) 가상 컬럼(pseudocolumn)입니다. ROWNUM에는 숫자 1, 2, 3, 4, ... N의 값이 할당됩니다. 여기서 N 은 ROWNUM과 함께 사용하는 로우의 수를 의미합니다. ROWNUM의 값은 로우에 영구적으로 할당되지 않습니다(이는 사람들이 많이 오해하는 부분이기도 합니다). 테이블의 로우는 숫자와 연계되어 참조될 수 없습니다. 따라서 테이블에서 "row 5"를 요청할 수 있는 방법은 없습니다. "row 5"라는 것은 존재하지 않기 때문입니다.
또 ROWNUM 값이 실제로 할당되는 방법에 대해서도 많은 사람들이 오해를 하고 있습니다. ROWNUM 값은 쿼리의 조건절이 처리되고 난 이후, 그리고 sort, aggregation이 수행되기 이전에 할당됩니다. 또 ROWNUM 값은 할당된 이후에만 증가(increment) 됩니다. 따라서 아래 쿼리는 로우를 반환하지 않습니다.
select * from t where ROWNUM > 1;
첫 번째 로우에 대해 ROWNUM > 1의 조건이 True가 아니기 때문에, ROWNUM은 2로 증가하지 않습니다. 아래와 같은 쿼리를 생각해 봅시다.
select ..., ROWNUM from t wheregroup by having order by ;
이 쿼리는 다음과 같은 순서로 처리됩니다.
1. FROM/WHERE 절이 먼저 처리됩니다.
2. ROWNUM이 할당되고 FROM/WHERE 절에서 전달되는 각각의 출력 로우에 대해 증가(increment) 됩니다.
3. SELECT가 적용됩니다.
4. GROUP BY 조건이 적용됩니다.
5. HAVING이 적용됩니다.
6. ORDER BY 조건이 적용됩니다.
따라서 아래와 같은 쿼리는 에러가 발생할 수 밖에 없습니다.
select * from emp where ROWNUM <= 5 order by sal desc;
이 쿼리는 가장 높은 연봉을 받는 다섯 명의 직원을 조회하기 위한 Top-N 쿼리로 작성되었습니다. 하지만 실제로 쿼리는 5 개의 레코드를 랜덤하게(조회되는 순서대로) 반환하고 salary를 기준으로 정렬합니다. 이 쿼리를 위해서 사용되는 가상코드(pseudocode)가 아래와 같습니다.
SELECT_WITH_PAGING.txt
ROWNUM = 1
for x in
( select * from emp )
loop
exit when NOT(ROWNUM <= 5)
OUTPUT record to temp
ROWNUM = ROWNUM+1
end loop
SORT TEMP
위에서 볼 수 있듯 처음의 5 개 레코드를 가져 온후 바로 sorting이 수행됩니다. 쿼리에서 "WHERE ROWNUM = 5" 또는 "WHERE ROWNUM > 5"와 같은 조건은 의미가 없습니다. 이는 ROWNUM 값이 조건자(predicate) 실행 과정에서 로우에 할당되며, 로우가 WHERE 조건에 의해 처리된 이후에만 increment 되기 때문입니다.
올바르게 작성된 쿼리가 아래와 같습니다.
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
위 쿼리는 salary를 기준으로 EMP를 내림차순으로 정렬한 후, 상위의 5 개 레코드(Top-5 레코드)를 반환합니다. 아래에서 다시 설명되겠지만, 오라클 데이터베이스가 실제로 전체 결과 셋을 정렬하지 않습니다. (오라클 데이터베이스는 좀 더 지능적인 방식으로 동작합니다.) 하지만 사용자가 얻는 결과는 동일합니다.
ROWNUM을 이용한 Top-N 쿼리 프로세싱
일반적으로 Top-N 쿼리를 실행하는 사용자는 다소 복잡한 쿼리를 실행하고, 그 결과를 정렬한 뒤 상위의 N 개 로우만을 반환하는 방식을 사용합니다. ROWNUM은 Top- N쿼리를 위해 최적화된 기능을 제공합니다. ROWNUM을 사용하면 대량의 결과 셋을 정렬하는 번거로운 과정을 피할 수 있습니다. 먼저 그 개념을 살펴보고 예제를 통해 설명하기로 하겠습니다.
아래와 같은 쿼리가 있다고 가정해 봅시다.
select ... from ... where ... order by columns;
또 이 쿼리가 반환하는 데이터가 수천 개, 수십만 개, 또는 그 이상에 달한다고 가정해 봅시다. 하지만 사용자가 실제로 관심 있는 것은 상위 N개(Top 10, Top 100)의 값입니다. 이 결과를 얻기 위한 방법에는 두 가지가 있습니다.
두 번째 접근법은 첫 번째에 비해 월등한 장점을 제공합니다. 그 이유는 두 가지입니다. 첫 번째로, ROWNUM을 사용하면 클라이언트의 부담이 줄어듭니다. 데이터베이스에서 제한된 결과 값만을 전송하기 때문입니다. 두 번째로, 데이터베이스에서 최적화된 프로세싱 방법을 이용하여 Top N 로우를 산출할 수 있습니다. Top-N 쿼리를 실행함으로써, 사용자는 데이터베이스에 추가적인 정보를 전달하게 됩니다. 그 정보란 바로 "나는N 개의 로우에만 관심이 있고, 나머지에 대해서는 관심이 없다"는 메시지입니다. 이제, 정렬(sorting) 작업이 데이터베이스 서버에서 어떤 원리로 실행되는지 설명을 듣고 나면 그 의미를 이해하실 수 있을 것입니다. 샘플 쿼리에 위에서 설명한 두 가지 접근법을 적용해 보기로 합시다.
select * from t order by unindexed_column;
여기서 T가 1백만 개 이상의 레코드를 저장한 큰 테이블이라고, 그리고 각각의 레코드가 100 바이트 이상으로 구성되어 있다고 가정해 봅시다. 그리고 UNINDEXED_COLUMN은 인덱스가 적용되지 않은 컬럼이라고, 또 사용자는 상위 10 개의 로우에만 관심이 있다고 가정하겠습니다. 오라클 데이터베이스는 아래와 같은 순서로 쿼리를 처리합니다.
1. T에 대해 풀 테이블 스캔을 실행합니다.
2. UNINDEXED_COLUMN을 기준으로 T를 정렬합니다. 이 작업은 "full sort"로 진행됩니다.
3. Sort 영역의 메모리가 부족한 경우 임시 익스텐트를 디스크에 스왑하는 작업이 수행됩니다.
4. 임시 익스텐트를 병합하여 상위 10 개의 레코드를 확인합니다.
5.쿼리가 종료되면 임시 익스텐트에 대한 클린업 작업을 수행합니다. .
결과적으로 매우 많은 I/O 작업이 발생합니다. 오라클 데이터베이스가 상위 10 개의 로우를 얻기 위해 전체 테이블을 TEMP 영역으로 복사했을 가능성이 높습니다.
그럼 다음으로, Top-N 쿼리를 오라클 데이터베이스가 개념적으로 어떻게 처리할 수 있는지 살펴 보기로 합시다.
select * from (select * from t order by unindexed_column) where ROWNUM < :N;
오라클 데이터베이스가 위 쿼리를 처리하는 방법이 아래와 같습니다.
1. 앞에서와 마찬가지로 T에 대해 풀-테이블 스캔을 수행합니다(이 과정은 피할 수 없습니다).
2. :N 엘리먼트의 어레이(이 어레이는 메모리에 저장되어 있을 가능성이 높습니다)에서 :N 로우만을 정렬합니다.
상위N 개의 로우는 이 어레이에 정렬된 순서로 입력됩니다. N +1 로우를 가져온 경우, 이 로우를 어레이의 마지막 로우와 비교합니다. 이 로우가 어레이의 N +1 슬롯에 들어가야 하는 것으로 판명되는 경우, 로우는 버려집니다. 그렇지 않은 경우, 로우를 어레이에 추가하여 정렬한 후 기존 로우 중 하나를 삭제합니다. Sort 영역에는 최대 N 개의 로우만이 저장되며, 따라서 1 백만 개의 로우를 정렬하는 대신N 개의 로우만을 정렬하면 됩니다.
이처럼 간단한 개념(어레이의 활용, N개 로우의 정렬)을 이용하여 성능 및 리소스 활용도 면에서 큰 이익을 볼 수 있습니다. (TEMP 공간을 사용하지 않아도 된다는 것을 차치하더라도) 1 백만 개의 로우를 정렬하는 것보다 10 개의 로우를 정렬하는 것이 메모리를 덜 먹는다는 것은 당연합니다.
아래의 테이블 T를 이용하면, 두 가지 접근법이 모두 동일한 결과를 제공하지만 사용되는 리소스는 극적인 차이를 보임을 확인할 수 있습니다.
create table t
as
select dbms_random.value(1,1000000)
id,
rpad('*',40,'*' ) data
from dual
connect by level <= 100000;
begin
dbms_stats.gather_table_stats
( user, 'T');
end;
/
Now enable tracing, via
exec
dbms_monitor.session_trace_enable
(waits=>true);
And then run your top-N query with ROWNUM:
select * from (select * from t order by id) where rownum <= 10;
마지막으로 상위 10 개의 레코드만을 반환하는 쿼리를 실행합니다.
declare
cursor c is
select *
from t
order by id;
l_rec c%rowtype;
begin
open c;
for i in 1 .. 10
loop
fetch c into l_rec;
exit when c%notfound;
end loop;
close c;
end;
/
이 쿼리를 실행한 후, TKPROF를 사용해서 트레이스 결과를 확인할 수 있습니다. 먼저 Top-N 쿼리 수행 후 확인한 트레이스 결과가 Listing 1과 같습니다.
Code Listing 1: ROWNUM을 이용한 Top-N 쿼리
select * from (select * from t order by id) where rownum <= 10 call count cpu elapsed disk query current rows -------- -------- ------- ------- ------- -------- -------- ------ Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.04 0.04 0 949 0 10 -------- -------- ------- ------- ------- -------- -------- ------ total 4 0.04 0.04 0 949 0 10 Rows Row Source Operation ----------------- --------------------------------------------------- 10 COUNT STOPKEY (cr=949 pr=0 pw=0 time=46997 us) 10 VIEW (cr=949 pr=0 pw=0 time=46979 us) 10 SORT ORDER BY STOPKEY (cr=949 pr=0 pw=0 time=46961 us) 100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400066 us)
이 쿼리는 전체 테이블을 읽어 들인 후, SORT ORDER BY STOPKEY 단계를 이용해서 임시 공간에서 사용되는 로우를 10 개로 제한하고 있습니다. 마지막 Row Source Operation 라인을 주목하시기 바랍니다. 쿼리가 949 번의 논리적 I/O를 수행했으며(cr=949), 물리적 읽기/쓰기는 전혀 발생하지 않았고(pr=0, pw=0), 불과 400066 백만 분의 일초 (0.04 초) 밖에 걸리지 않았습니다. 이 결과를 Listing 2의 실행 결과와 비교해 보시기 바랍니다.
Code Listing 2: ROWNUM을 사용하지 않은 쿼리
SELECT * FROM T ORDER BY ID call count cpu elapsed disk query current rows -------- -------- ------- ------- ------- -------- -------- ------ Parse 1 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 10 0.35 0.40 155 949 6 10 -------- -------- ------- ------- ------- -------- -------- ------ total 13 0.36 0.40 155 949 6 10 Rows Row Source Operation ----------------- --------------------------------------------------- 10 SORT ORDER BY (cr=949 pr=155 pw=891 time=401610 us) 100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400060 us) Elapsed times include waiting for the following events: Event waited on Times ------------------------------ ------------ direct path write temp 33 direct path read temp 5
결과가 완전히 다른 것을 확인하실 수 있습니다. "elapsed/CPU time"이 크게 증가했으며, 마지막 Row Source Operation 라인을 보면 그 이유를 이해할 수 있습니다. 정렬 작업은 디스크 상에서 수행되었으며, 물리적 쓰기(physical write) 작업이 "pw=891"회 발생했습니다. 또 다이렉트 경로를 통한 읽기/쓰기 작업이 발생했습니다. (10 개가 아닌) 100,000 개의 레코드가 디스크 상에서 정렬되었으며, 이로 인해 쿼리의 실행 시간과 런타임 리소스가 급증하였습니다.
ROWNUM을 이용한 페이지네이션
필자가 ROWNUM을 가장 즐겨 사용하는 대상이 바로 페이지네이션(pagination)입니다. 필자는 결과 셋의 로우 N 에서 로우 M까지를 가져오기 위해 ROWNUM을 사용합니다. 쿼리의 일반적인 형식이 아래와 같습니다.
select *
from ( select /*+ FIRST_ROWS(n) */
a.*, ROWNUM rnum
from ( your_query_goes_here,
with order by ) a
where ROWNUM <=
:MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
where
여기서,
이 시나리오는 웹 브라우저를 통해 접속한 사용자가 검색을 마치고 그 결과를 기다리고 있는 상황을 가정하고 있습니다. 따라서 첫 번째 결과 페이지(그리고 이어서 두 번째, 세 번째 결과 페이지)를 최대한 빨리 반환해야 할 것입니다. 쿼리를 자세히 살펴 보면, (처음의 :MAX_ROW_TO_FETCH 로우를 반환하는) Top-N 쿼리가 사용되고 있으며, 따라서 위에서 설명한 최적화된 기능을 이용할 수 있음을 알 수 있습니다. 또 네트워크를 통해 클라이언트가 관심을 갖는 로우만을 반환하며, 조회 대상이 아닌 로우는 네트워크로 전송되지 않습니다.
페이지네이션 쿼리를 사용할 때 주의할 점이 하나 있습니다. ORDER BY 구문은 유니크한 컬럼을 대상으로 적용되어야 합니다. 유니크하지 않은 컬럼 값을 대상으로 정렬을 수행해야 한다면 ORDER BY 조건에 별도의 조건을 추가해 주어야 합니다. 예를 들어 SALARY를 기준으로 100 개의 레코드를 정렬하는 상황에서 100 개의 레코드가 모두 동일한 SALARY 값을 갖는다면, 로우의 수를 20-25 개로 제한하는 것은 의미가 없을 것입니다. 여러 개의 중복된 ID 값을 갖는 작은 테이블을 예로 들어 설명해 보겠습니다.
SQL> create table t
2 as
3 select mod(level,5) id,
trunc(dbms_random.value(1,100)) data
4 from dual
5 connect by level <= 10000;
Table created.
ID 컬럼을 정렬한 후 148-150 번째 로우, 그리고 148?151 번째 로우를 쿼리해 보겠습니다.
SQL> select * 2 from 3 (select a.*, rownum rnum 4 from 5 (select id, data 6 from t 7 order by id) a 8 where rownum <= 150 9 ) 10 where rnum >= 148; ID DATA RNUM ------- ---------- ----------- 0 38 148 0 64 149 0 53 150 SQL> SQL> select * 2 from 3 (select a.*, rownum rnum 4 from 5 (select id, data 6 from t 7 order by id) a 8 where rownum <= 151 9 ) 10 where rnum >= 148; ID DATA RNUM ------- ---------- ----------- 0 59 148 0 38 149 0 64 150 0 53 151
로우 148의 경우 DATA=38의 결과가 반환되었습니다. 두 번째 쿼리에서는 DATA=59의 결과가 반환되었습니다. 두 가지 쿼리 모두 올바른 결과를 반환하고 있습니다. 쿼리는 데이터를 ID 기준으로 정렬한 후 앞부분의 147 개 로우를 버린 후 그 다음의 3 개 또는 4 개의 로우를 반환합니다. 하지만 ID에 중복값이 너무 많기 때문에, 쿼리는 항상 동일한 결과를 반환함을 보장할 수 없습니다. 이 문제를 해결하려면 ORDER BY 조건에 유니크한 값을 추가해 주어야 합니다. 위의 경우에는 ROWID를 사용하면 됩니다.
SQL> select * 2 from 3 (select a.*, rownum rnum 4 from 5 (select id, data 6 from t 7 order by id, rowid) a 8 where rownum <= 150 9 ) 10 where rnum >= 148; ID DATA RNUM ------- ---------- ----------- 0 45 148 0 99 149 0 41 150 SQL> SQL> select * 2 from 3 (select a.*, rownum rnum 4 from 5 (select id, data 6 from t 7 order by id, rowid) a 8 where rownum <= 151 9 ) 10 where rnum >= 148; ID DATA RNUM ------- ---------- ----------- 0 45 148 0 99 149 0 41 150 0 45 151
이제 쿼리를 반복 실행해도 동일한 결과를 보장할 수 있게 되었습니다. ROWID는 테이블 내에서 유니크한 값을 가집니다. 따라서 ORDER BY ID 조건과 ORDER BY ROWID 기준을 함께 사용함으로써 사용자가 기대한 순서대로 페이지네이션 쿼리의 결과를 확인할 수 있습니다.
ROWNUM 개념 정리
지금까지 ROWNUM에 관련하여 아래와 같은 개념을 설명하였습니다.
출처 : Tong - mercurii님의 TechTip통
Posted by web20korea
,
ROWNUM
web20korea's
- web20korea
