수안이의 컴퓨터 연구실

Abstract: This document describes how the Microsoft® SQL Server™ 2000 query processor interacts with an OLE DB provider to enable distributed and heterogeneous queries. It is intended primarily for OLE DB provider developers, and assumes a solid understanding of the OLE DB specification.

조금 오래된 문서이긴 하지만, 정리가 잘 되어있는 워드 문서이다.

이 기사는 SQL Server 2000 엔터프라이즈 버전의 데이터 웨어하우스의 관리 효율, 쿼리 성능 및 로드 속도를 향상시키는 파티션 사용법을 설명합니다. 관계형 데이터베이스와 Analysis Services 큐브에서 차원 스키마의 행 분할을 다루었습니다.

Microsoft SQL Server 2000
데이터 웨어하우스에서 파티션 사용
만든 이: Joy Mundy
기고가: Stuart Ozer, Lubor Kollar, Len Wyatt


요약: 이 기사는 SQL Server 2000 엔터프라이즈 버전의 데이터 웨어하우스의 관리 효율, 쿼리 성능 및 로드 속도를 향상시키는 파티션 사용법을 설명합니다. 관계형 데이터베이스와 Analysis Services 큐브에서 차원 스키마의 행 분할을 다루었습니다.

목차
개요
SQL Server 2000 관계형 데이터 웨어하우스에서 파티션 사용
SQL Server 2000 Analysis Services에서 파티션 사용
결론
추가 정보
부록: 파티션 복제를 위한 VBScript 코드 예제

개요
이 기사는 데이터 웨어하우스에서 데이터 파티션 작업의 역할을 설명합니다. 관계형 데이터 웨어하우스와 Analysis Services 큐브는 모두 데이터의 분할을 지원합니다. 파티션 작업의 기본 논리적 개념은 날짜와 같은 키에 의해 데이터를 행 분할하는 Microsoft® SQL Server™의 두 엔진과 동일합니다. 관계형 데이터베이스에서, 파티션 작업은 별도의 실제 테이블을 생성하여 실행할 수 있습니다. 예를 들어, 각 월의 데이터마다 한 개의 테이블을 생성하고 구성원 테이블의 통합 뷰를 정의합니다. 이와 유사하게, SQL Server 엔터프라이즈 버전의 Analysis Services는 큐브의 명시적인 분할을 지원합니다. 두 관계형 데이터베이스와 온라인 분석 프로세스(OLAP) 엔진에서는, 사용자에게 복잡한 실제 저장소가 표시되지 않습니다.

데이터 웨어하우스 파티션 작업의 장점은 다음과 같습니다.

쿼리 시간을 충분히 단축합니다.


데이터베이스의 로드 시간 및 유지 관리 가능성을 향상시킵니다.


현재 데이터베이스에서 오래된 데이터를 제거하는 것과 관련된 데이터 정리 문제를 해결합니다.
이 기술은 분할되지 않은 시스템보다 훨씬 복잡한 데이터 스테이징 응용 프로그램을 작성해야 합니다. 이 기사는 행 분할된 데이터 웨어하우스의 디자인, 구현, 유지 관리에 대해 가장 좋은 실습 방법을 설명합니다.

효율적인 파티션 계획은 쿼리 성능을 충분히 향상시키므로 규모가 큰 Analysis Services 시스템에서는 파티션이 절실히 요구됩니다. 관계형 데이터 웨어하우스의 파티션 작업은 몇몇 특정 웨어하우스의 유지 관리 문제를 해결하는 데 효율적이라해도 일반적으로는 바람직한 방법은 아닙니다.


SQL Server 2000 관계형 데이터 웨어하우스에서 파티션 사용
분할된 뷰는 구성원 집합에서 행 분할된 데이터를 조인하여 해당 데이터가 한 개의 데이블로 나타나도록 합니다. SQL Server 2000에서는 로컬 분할 뷰와 분산 분할 뷰가 구별됩니다. 로컬 분할 뷰에서는, 참여 중인 모든 테이블과 해당 뷰가 SQL Server의 동일한 인스턴스에 상주합니다. 분산 분할 뷰에서는, 적어도 한 개의 참여 테이블이 별도의(원격) 서버에 상주합니다. 분산 분할 뷰는 데이터 웨어하우스 응용 프로그램에는 적합하지 않습니다.

차원 데이터 웨어하우스는 팩트와 차원 주변에 구조화되어 별모양 스키마, 눈송이 스키마로 인스턴스가 물리적으로 생성되며, 드물게는 팩트와 차원을 결합하는 기본 테이블을 완전히 비정상화합니다. 차원 스키마가 관계형 데이터 웨어하우스에 대해 가장 일반적인 구조이므로, 이 기사에서는 차원 스키마와 함께 파티션을 사용하는 방법에 초점을 맞춰 설명합니다. 여기에서 언급한 권장 사항은 일반적인 데이터 웨어하우스 스키마에 적용할 수 있습니다.

파티션의 장점
데이터 정리
많은 데이터 웨어하우스 관리자들은 특정한 시간 이후에 오래된 데이터를 보관합니다. 예를 들어, 클릭스트림 데이터 웨어하우스는 자세한 데이터를 3개월 내지 4개월 동안만 온라인으로 유지할 수 있습니다. 다른 일반적인 방법으로 데이터베이스에서 오래된 데이터가 현재 창을 지나 롤링할 때 보관하거나 제거하여 13개월, 37개월 또는 10년동안 온라인으로 유지할 수 있습니다. 이러한 롤링 창 구조는 규모가 큰 데이터 웨어하우스에 일반적으로 사용하는 방법입니다.

분할된 테이블없이 데이터베이스에서 오래된 데이터를 제거하는 프로세스에는 다음과 같은 아주 방대한 DELETE 문이 필요합니다.

DELETE FROM fact_table WHERE date_key < 19990101

이 명령문을 실행하는 데는 비용이 많이 들고, 동일한 테이블로의 로드 프로세스 보다 시간이 많이 걸릴 수 있습니다. 하지만, 분할된 테이블이 있는 경우에는 관리자가 UNION ALL 뷰를 재정의하여 가장 오래된 테이블을 제외시키고 그 테이블을 데이터베이스(백업된 것을 확인한 후)에서 제거합니다.

아래 설명한 바와 같이, 분할된 테이블을 유지 관리하는 데는 비용이 많이 듭니다. 데이터 정리만을 목적으로 분할하려는 경우, 분할되지 않은 테이블에서 오래된 데이터를 제거하려면 데이터를 조금씩 소모하는 방법을 연구해야 합니다. 한 번에 1000개의 행을 제거("set rowcount 1000" 명령어 사용)하는 스크립트는 원하는 만큼 데이터를 제거할 때까지 우선 순위가 낮은 프로세스에서 지속적으로 실행할 수 있습니다. 이 기술은 규모가 큰 시스템에서 효과적으로 사용되며 필요한 파티션 관리 시스템을 만드는 것보다 더 간단합니다. 이 기술은 로드 볼륨과 시스템 사용률에 따라 몇몇 시스템에 적합하며 시스템에 벤치마크되도록 고려해야 합니다.

로드 속도
데이터를 로드하는 가장 빠른 방법은 빈 데이블 또는 인덱스 없는 테이블에 로드하는 것입니다. 좀 더 작게 분할된 테이블에 로드함으로써 증분 로드 프로세스의 효율성이 더욱 향상될 수 있습니다.

유지 관리 가능성
데이터 웨어하우스 스테이징 응용 프로그램이 파티션 작업을 지원하도록 작성되면 전체 시스템을 유지 관리하기가 쉽습니다. 데이터 로드, 백업, 테이블 복원을 포함한 유지 관리 작업은 병렬로 실행할 수 있고 성능도 많이 향상시킬 수 있습니다. 다운스트림 큐브를 증분 채우기하는 프로세스의 속도가 빨라지고 간소화될 수 있습니다.

쿼리 속도
쿼리 속도는 데이터 웨어하우스 관계형 데이터베이스를 분할하는 목적으로는 고려하지 않아야 합니다. 분할된 팩트 테이블과 분할되지 않은 팩트 테이블의 쿼리 성능은 유사합니다. 분할된 데이터베이스가 올바르게 디자인되면 관계형 엔진이 쿼리 해결에 필요한 파티션만 쿼리 계획에 포함시킵니다. 예를 들어, 데이터베이스가 월 단위로 분할되고 쿼리가 2000년 1월인 경우, 2000년 1월에 대한 파티션만 쿼리 계획에 포함됩니다. 결과 쿼리는 파티션 키 상에서 클러스터된 인덱스에 따라 올바르게 인덱스된 결합 테이블에 대해서도 제대로 실행되듯이 분할된 테이블에서 제대로 실행됩니다.

파티션의 단점
복잡성
파티션의 주요 단점은 관리자가 파티션을 관리하기 위해 응용 프로그램을 작성해야 한다는 점입니다. 파티션을 관리하기 위해 응용 프로그램을 처음으로 디자인, 테스트, 롤링 아웃하지 않고는 관계형 데이터베이스에서 행 분할을 사용하는 데이터 웨어하우스를 제품에 적용하는 것은 적절하지 않습니다. 이 기사의 목표 중 하나는 파티션 관리 응용 프로그램과 관련된 문제와 디자인 결정에 대해 설명하는 것입니다.

쿼리 디자인 제약 조건
최상의 쿼리 성능을 위해 모든 쿼리가 필터 키의 조건을 팩트 테이블에 직접 배치해야 합니다. Dates 차원 테이블과 같이 두 번째 테이블에 제약 조건을 배치하는 쿼리는 쿼리의 모든 파티션을 포함합니다.



디자인 고려 사항
차원 데이터 웨어하우스는 팩트와 차원 주변에 구조화되어 별모양 스키마, 눈송이 스키마로 인스턴스가 물리적으로 생성되며, 드물게는 팩트와 차원을 결합하는 기본 테이블을 완전히 비정상화합니다. 차원 데이터 웨어하우스 관리자는 일반적으로 팩트 테이블만 분할합니다. 차원 테이블을 분할하는 것은 장점이 거의 없습니다. 몇몇 환경에서는 1,000만 개 이상의 구성원이 있는 규모가 아주 큰 차원 테이블을 분할하는 것이 이점이 있을 수 있습니다. 비 차원 관계형 데이터 웨어하우스도 분할될 수 있으며 이 기사에서 일반적으로 언급된 사항을 적용할 수 있습니다.

효과적인 파티션 계획은 시스템 아키텍처와 디자인 목표의 컨텍스트에서 개발됩니다. 동일한 스키마 디자인에서도 Analysis Services 큐브만 채우기 위해 존재하는 관계형 데이터 웨어하우스는 분석가가 직접 쿼리한 파티션 구조와는 다른 파티션 구조를 내포할 수도 있습니다. 롤링 창이 있는 시스템은 필연적으로 시간별로 분할되고 다른 시스템은 분할되지 않을 수도 있습니다.

데이터 웨어하우스에 Analysis Services 큐브가 포함된 경우에는 관계형 데이터 웨어하우스와 Analysis Services 데이터베이스의 파티션을 병렬로 구조화하는 것이 바람직합니다. 이렇게 하면 유지 관리 응용 프로그램이 간소화되고 응용 프로그램이 관계형 데이터베이스에 새 테이블을 만들 때 새 큐브 파티션을 생성합니다. 관리자는 한 개의 파티션 방법만을 익히면 됩니다. 하지만, 응용 프로그램이 두 개의 데이터베이스를 별도로 분할해야 하는 경우도 있어 유지 관리 응용 프로그램의 아래쪽만 복잡하게 될 수 있습니다.

파티션 디자인 개요
SQL Server 데이터베이스의 분할된 테이블은 업데이트하거나 쿼리할 수 있는(업데이트할 수 없는) 분할된 뷰를 사용할 수 있습니다. 두 경우에서, 테이블 파티션은 각 파티션에 올바른 데이터가 있는 CHECK 제약 조건과 함께 생성됩니다. 업데이트할 수 있는 분할된 뷰는 뷰에 대한 INSERT(또는 UPDATE 또는 DELETE)를 지원하여 적합한 원본으로 사용하는 테이블에서 작동하도록 합니다. 이러한 방법이 편리한 반면, 데이터 웨어하우스 응용 프로그램은 뷰에서 실행할 수 없는 대량의 로드가 필요합니다. 아래의 테이블은 업데이트할 수 있고 쿼리할 수 있는 분할된 뷰의 요구 사항, 장점, 단점을 요약합니다.

요구 사항 장점 단점
업데이트할 수 있는 분할된 뷰
CHECK 제약 조건에 의해 적용된 파티션 키


기본 키의 파티션 키 부분


다른 데이터베이스 제약 조건이 없는 파티션 키 부분


구성원 테이블에 정의된 UNION ALL 뷰
쿼리 성능: 쿼리 계획은 쿼리 해결에 필요한 구성원 테이블만 포함합니다.


유지 관리 응용 프로그램의 간소화: 데이터가 UNION ALL 뷰에 로드될 수 있고 적합한 구성원 테이블에 삽입됩니다.
로드 성능: 뷰를 통해 데이터를 로드하면 대부분의 데이터 웨어하우스 응용 프로그램에서 데이터를 사용할 때 속도가 느려집니다.


불변성: 데이터베이스 디자인은 파티션 키 상에서 추가 제약 조건이 필요할 수도 있습니다.

쿼리할 수 있는 분할된 뷰
CHECK 제약 조건에 의해 적용된 파티션 키


구성원 테이블에 정의된 UNION ALL 뷰
쿼리 성능: 쿼리 계획은 쿼리 해결에 필요한 구성원 테이블만 포함합니다.


로드 성능: 고 성능으로 구성원 테이블에 직접 대량 로드합니다.


저장소: 기본 키를 선언하고 그 위에 인덱스를 생성하는 것이 권장하는 방법이지만 분할된 뷰에 대해 기본 키 인덱스가 필요하지 않습니다.
뷰는 256개의 구성원 테이블로 제한됩니다.


파티션과 로드를 관리하기위해 유지 관리 응용 프로그램을 작성해야 합니다.



Microsoft가 권장하는 방법은 정의된 기본 키가 있는 단일 서버 상의 로컬 분할 통합 뷰로 팩트 테이블을 디자인하는 것입니다. 대부분의 경우 이 정의는 분할된 뷰의 결과가 되고 업데이트할 수 있지만, 데이터 웨어하우스 유지 관리 응용 프로그램은 대부분의 데이터를 뷰를 통하지 않고 구성원 테이블에 직접 대량 로드할 수 있도록 디자인되어야 합니다.





예제 구문
다음 코드 예제는 구성원 테이블과 통합 뷰를 정의하고 뷰에 데이터를 삽입하기 위한 구문을 나타냅니다.

--1999년의 팩트 테이블을 만듭니다. CREATE TABLE [dbo].[sales_fact_19990101] ( [date_key] [int] NOT NULL CHECK ([date_key] BETWEEN 19990101 AND 19991231), [product_key] [int] NOT NULL , [customer_key] [int] NOT NULL , [promotion_key] [int] NOT NULL , [store_key] [int] NOT NULL , [store_sales] [money] NULL , [store_cost] [money] NULL , [unit_sales] [float] NULL ) ALTER TABLE [sales_fact_19990101] ADD PRIMARY KEY ( [date_key], [product_key], [customer_key], [promotion_key], [store_key]) ; -- 2000년의 팩트 테이블을 만듭니다. CREATE TABLE [dbo].[sales_fact_20000101] ( [date_key] [int] NOT NULL CHECK ([date_key] BETWEEN 20000101 AND 20001231), [product_key] [int] NOT NULL , [customer_key] [int] NOT NULL , [promotion_key] [int] NOT NULL , [store_key] [int] NOT NULL , [store_sales] [money] NULL , [store_cost] [money] NULL , [unit_sales] [float] NULL ) ALTER TABLE [sales_fact_20000101] ADD PRIMARY KEY ( [date_key], [product_key], [customer_key], [promotion_key], [store_key]) ; --UNION ALL 뷰를 만듭니다. CREATE VIEW [dbo].[sales_fact] AS SELECT * FROM [dbo].[sales_fact_19990101] UNION ALL SELECT * FROM [dbo].[sales_fact_20000101] --몇 개의 데이터 행을 다음과 같이 삽입합니다. INSERT INTO [sales_fact] VALUES (19990125, 347, 8901, 0, 13, 5.3100, 1.8585, 3.0) INSERT INTO [sales_fact] VALUES (19990324, 576, 7203, 0, 13, 2.1000, 0.9450, 3.0) INSERT INTO [sales_fact] VALUES (19990604, 139, 7203, 0, 13, 5.3700, 2.2017, 3.0) INSERT INTO [sales_fact] VALUES (20000914, 396, 8814, 0, 13, 6.4800, 2.0736, 2.0) INSERT INTO [sales_fact] VALUES (20001113, 260, 8269, 0, 13, 5.5200, 2.4840, 3.0)

파티션 작업이 제대로 진행되는지 확인하려면, 쿼리 분석기를 사용하여 다음과 같은 쿼리에 대한 쿼리 계획을 나타냅니다.

SELECT TOP 2 * FROM sales_fact WHERE date_key = 19990324

그러면 쿼리 계획에 포함된 1999년 테이블만 보입니다. 이 쿼리 계획을 기본 키가 제거된 동일한 두 개의 테이블에 의해 생성된 쿼리 계획과 비교해보면, 2000년 테이블은 여전히 포함되지 않습니다. 이 계획을 date_key에 대한 제약 조건이 제거된 스키마에 대해 생성된 쿼리 계획과 대조합니다. 제약 조건을 제거하면 1999년 테이블과 2000년 테이블이 모두 쿼리에 포함됩니다.

일반적으로 "TOP N" 구문은 최소한의 서버 리소스로 결과를 빨리 반환하기 때문에 규모가 큰 테이블에 대해 쿼리를 탐색할 때 유용한 실습 방법입니다. "SELECT *" 문에 의해 생성된 쿼리 계획은 구문 분석하기가 어렵기 때문에 분할된 테이블 쿼리 계획을 검토할 때 "TOP N" 구문을 사용하는 것이 더욱 중요합니다. 쿼리를 실행하는 시간에 적합한 테이블만 쿼리에서 사용되지만, 언뜻 보면 쿼리 계획에 UNION ALL 뷰의 모든 구성 요소 테이블이 포함된 것처럼 보입니다.



팩트 테이블에 조건을 직접 적용
최상의 쿼리 성능을 위해 모든 쿼리가 필터 키의 조건을 팩트 테이블에 직접 배치해야 합니다. Dates 차원 테이블과 같이 두 번째 테이블에 제약 조건을 배치하는 쿼리는 쿼리의 모든 파티션을 포함합니다. 표준 시작 조인 쿼리는 UNION ALL 팩트 테이블에서 다음과 같이 작동합니다.

분할되지 않은 모든 차원 테이블의 특성에 조건을 배치하여 표준 방법으로 시작 쿼리 WHERE 절을 생성합니다.


파티션 차원(Dates)의 특성을 포함합니다.


사용자가 분할되지 않은 스키마에 대해 쿼리를 디자인하는 것과 같이 분할된 차원 스키마에 대해 쿼리를 디자인합니다. 단, 팩트 테이블의 날짜 키에 날짜 조건을 직접 배치하는 경우에 가장 효과적입니다.
각 파티션 테이블에 인덱스의 첫 번째 열로서 날짜가 있는 클러스터된 인덱스가 있는 경우, 임의 쿼리를 해결하기 위해 사용하는 모든 파티션 비용은 상대적으로 적습니다. 표준 보고서를 생성하거나 다운스트림 데이터베이스를 증분 업데이트하는 쿼리와 같이 미리 정의된 쿼리는 가능한 한 효율적으로 작성되어야 합니다.

파티션 키 선택
팩트 테이블은 여러 차원에서 분할될 수 있지만 대부분의 실무자들은 날짜만 분할하게 됩니다. 앞서 설명한 바와 같이, 날짜 분할은 "롤링 창" 관리를 쉽게 하고 오래된 파티션이 별도의 위치에 있거나 초기 파티션보다 간단히 인덱스될 수도 있습니다. 게다가, 날짜에 따라 대부분의 쿼리를 데이터 웨어하우스에 필터합니다.

날짜에 의해 분할된 응용 프로그램의 경우 다음과 같이 여러 가지 방법으로 결정할 수 있습니다.

온라인으로 유지할 수 있는 데이터의 양 이러한 결정은 대개 비지니스 요구 사항에서 결정되고 거대한 데이터 볼륨을 온라인으로 유지하는 비용 대 수익 비율에 의해 조절되어야 합니다.


날짜 키의 형식 차원 테이블과 팩트 테이블에 대한 대리인 키를 사용하기 위한 가장 좋은 실습 방법으로 데이터 웨어하우스가 널리 사용됩니다. 날짜에 의해 분할된 팩트 테이블의 경우 yyyymmdd 형식의 "스마트" 정수 대리인 키를 사용하는 것이 바람직합니다. 정수로서의 이 키는 4바이트만 사용하게 되어 datetime 키의 8바이트와 비교됩니다. 많은 데이터 웨어하우스가 datetime 형식의 일반 날짜 키를 사용합니다.


파티션의 단위 위의 예제에서는 연 단위 파티션을 사용하였지만, 대부분의 시스템은 월, 주, 일과 같이 더 세부적인 단위를 사용합니다. 사용자 쿼리가 월 또는 주 경계를 따르도록 고려하는 것도 다소 중요하지만, 그 보다 시스템의 전반적인 크기와 관리 효율성이 가장 중요한 요소입니다. 최대 256개의 테이블을 참조할 수 있는 모든 SQL 쿼리를 회수합니다. 수 개월 이상의 데이터를 유지 관리하는 데이터 웨어하우스의 경우에는 일 단위 파티션의 UNION ALL 뷰는 한계에 부딪히게 됩니다. 경험에 의한 방법으로 날짜로만 분할된 팩트 테이블은 주 단위로 분할하는 경우가 많습니다.


파티션 범위 정의 방법 가장 간단하고 사람이 읽을 수 있는 BETWEEN 구문이 효율적으로 실행됩니다. 예를 들어, 월 단위 파티션 형식은 다음과 같습니다.
date_key < 19990101 date_key BETWEEN 1990101 AND 19990131 date_key BETWEEN 19990201 AND 19990229 date_key BETWEEN 19991201 AND 19991231 date_key > 19991231
위 예문에서 첫 번째와 마지막 파티션에 데이터를 입력하지 않지만, 모든 가능한 날짜 값의 영역을 적용하기 위해서는 두 파티션을 정의하는 것이 바람직합니다. 또한,1999년이 윤년이 아니지만 2월 파티션은 2월 29일까지 포함합니다. 이 구조를 사용하면 파티션과 제약 조건을 생성하는 응용 프로그램의 디자인에 윤년 논리를 포함하지 않아도 됩니다.

파티션 병합의 소요 시간 현재 파티션의 수를 최소화하기 위해 데이터베이스 관리자는 일 단위 파티션을 주 또는 월 단위 파티션에 병합하는 방법으로 응용 프로그램 분할을 작성하기도 합니다. 이러한 접근 방법은 아래의 파티션 채우기 및 유지 관리 영역에서 상세히 설명하였습니다.
날짜로 분할하는 방법에 대한 자세한 설명은 미래의 다른 파티션 키에 대한 설명도 포함되어야 합니다.

데이터 로드: 들어오는 데이터가 다른 차원에 의해 정렬되는 경향이 강하거나 각 저장소 또는 보조에 대한 데이터가 별도의 시스템에 의해 전송되는 경우, 해당 데이터가 일반 파티션 키입니다.

큐브의 데이터 쿼리: 관계형 데이터베이스와 Analysis Services 큐브가 동일한 방법으로 분할되는 데 기술적인 이유가 없지만 이 파티션 방법은 일반적입니다. 이러한 가정이 성립되면 유지 관리 응용 프로그램이 더욱 간소화됩니다. 따라서, 관계형 데이터베이스가 Analysis Services 큐브를 채우기 위해서만 존재할지라도 파티션 키를 선택할 때 일반 쿼리 패턴을 고려해야 합니다.

명명 규칙
행 분할된 팩트 테이블의 구성원 테이블을 명명하는 규칙은 파티션 디자인에서 기본적으로 이루어져야 합니다. 가장 보편적인 규칙으로서, 제목에 완전한 파티션 시작 날짜를 사용합니다. 연 단위 파티션이라도 [sales_fact_yyyy]보다는 [sales_fact_yyyymmdd] 형식이 좋습니다.

데이터베이스가 여러 개의 단위로 파티션을 지원하는 경우, 명명 규칙은 각 파티션 내에 있는 시간 범위를 반영해야 합니다. 예를 들어, 월 단위 파티션에는 sales_fact_20001101m 형식을 사용하고 일 단위 파티션에는 sales_fact_20001101d 형식을 사용합니다.

구성원 테이블의 이름은 뷰를 통해 데이터를 액세스하는 최종 사용자에게는 나타나지 않습니다. 따라서 구성원 테이블의 이름은 유지 관리 응용 프로그램 지향적이어야 합니다.

다운스트림 큐브에 대한 파티션 작업
관계형 데이터 웨어하우스를 Analysis Services 큐브를 지원하는 데만 사용하는 경우에는 UNION ALL 뷰를 정의할 필요가 없습니다. 이 경우, 응용 프로그램에 256개의 테이블 제한이 적용되지는 않지만 UNION ALL 뷰를 정의할 수 없는 방법으로 관계형 데이터 웨어하우스를 분할하는 것은 바람직하지 않습니다.



분할된 팩트 테이블 관리
파티션 관리가 자동화되고 테스트된 후에 분할된 데이터 웨어하우스를 제품에 적용해야 합니다. 파티션 관리 시스템은 단순한 응용 프로그램으로서 여기에서는 일반적인 시스템 요구 사항의 개요를 설명합니다.

아래의 설명에서는 날짜에 따라 분할한다고 가정합니다.

메타데이터
강력한 파티션 관리 시스템은 메타데이터에 의해 추진됩니다. 메타데이터를 프로그램 방식으로 액세스할 수 있으면 어디에나 저장할 수 있습니다. 대부분의 데이터 웨어하우스 시스템은 데이터 웨어하우스 SQL Server 또는 Microsoft SQL Server 메타데이터 서비스에 정의된 사용자 지정 메타데이터 테이블을 사용합니다.

메타데이터 저장소 메커니즘과 상관 없이 메타데이터의 내용에는 다음과 같은 각 파티션의 정보를 포함해야 합니다.

파티션 이름


생성된 Date 파티션


파티션에 있는 데이터의 Date 범위


온라인으로 이동한 Date 파티션(UNION ALL 뷰에 포함된 Date 파티션)


오프라인으로 이동한 Date 파티션(뷰에서 삭제된 Date 파티션)


삭제된 Date 파티션
데이터 웨어하우스의 전체 관리 시스템의 일부인 다른 메타데이터 테이블은 각 파티션에 데이터가 로드된 시기와 양을 추적해야 합니다.

새 파티션 만들기
파티션 관리 시스템의 첫 번째 작업은 새 파티션을 만드는 것입니다. 다음 파티션으로 사용될 새 테이블을 만들도록 작업을 정기적으로 예약해야 합니다.

이 작업을 실행하는 여러 가지 효과적인 방법이 있습니다. 바람직한 접근 방법으로는 SQL-DMO(분산 관리 개체)를 사용하여 기존 파티션과 동일한 구조와 인덱스로 새 테이블을 만드는 것입니다. 하지만, 새 테이블은 새 테이블 이름, 인덱스 이름, 파티션 키 제약 조건 정의, 파일그룹 등과 함께 만들어야 합니다.

템플릿 테이블 정의(가장 최근의 파티션)를 사용합니다.


테이블과 인덱스 Name 속성을 수정하고, 제약 조건 Text 속성 및 다른 속성을 확인합니다.


ADD 메서드를 사용하여 테이블 인스턴스를 만듭니다.
인텔리전트 명명 규칙을 사용하여 코드를 거의 사용하지 않고 작업을 완료할 수 있습니다.

이 기사에서 나중에 설명하는 바와 같이, 사용자의 응용 프로그램은 데이터 웨어하우스의 큐브에 대해 Analysis Services 파티션을 사용할 수도 있습니다. 이 경우, RDBMS에 파티션 테이블을 만드는 스크립트나 프로그램은 DSO(Decision Support Objects)를 사용하여 해당 큐브 파티션을 계속 만들 수 있습니다.

파티션 채우기
위에 설명한 바와 같이, 데이터를 UNION ALL 뷰에 로드할 수 있습니다. 이것은 이론상으로는 테이블 분할 구조의 훌륭한 기능이지만, 실제로는 데이터 웨어하우스 응용 프로그램에 바람직한 기능은 아닙니다. UNION ALL 뷰에 데이터를 로드하는 작업은 대량으로 실행할 수 없습니다. 분할된 테이블을 보증할 만큼 규모가 큰 데이터 웨어하우스에 대해 로드 프로세스의 속도가 너무 느려집니다.

대신, 각 시기에 따라 적합한 대상 테이블에 데이터를 빠르게 로드하도록 데이터 웨어하우스 로드 응용 프로그램을 디자인해야 합니다. 데이터 스테이징 응용 프로그램이 SQL Server 데이터 변환 서비스(DTS)에서 구현되면 동적 속성 작업이 데이터 펌프 작업이나 대량 삽입 작업의 대상 테이블 이름을 쉽게 변경할 수 있습니다.

새 파티션이 UNION ALL 뷰에 참여하지 않는 동안은 데이터를 로드할 때 시스템 중단 시간이 필요하지 않습니다.

데이터 웨어하우스 스테이징 응용 프로그램은 현재 파티션에 속하지 않은 들어오는 데이터를 처리하도록 디자인되어야 합니다. 이러한 상황은 데이터 웨어하우스 로드 프로세스가 하루 동안 발생하지 않았을 경우 정상적인 이벤트에 예외로서 발생합니다. 다른 시스템은 진행 중에 새로 도착한 오래된 데이터로 되어 있습니다. 시스템을 디자인할 때는 이러한 예외의 가능성, 빈도, 데이터 볼륨을 고려해야 합니다.

오래된 데이터가 충분히 낮은 볼륨으로 도착하면, 가장 단순한 디자인이 업데이트할 수 있는 UNION ALL 뷰을 사용하여 현재 파티션에 속하지 않은 모든 데이터를 로드합니다.

UNION ALL 뷰 정의
증분 로드를 성공적으로 마치면 UNION ALL 뷰를 수정해야 합니다. 이 작업에서도 SQL-DMO가 바람직한 접근 방법입니다. ALTER 메서드를 사용하여 VIEW 개체의 TEXT 속성을 변경합니다. 뷰 정의에 포함되는 파티션 목록은 위에 설명한 메타데이터 테이블에서 대부분 파생됩니다.

파티션 병합
여러 파티션을 더 큰 단일 파티션에 병합하는 개념은 표면상으로는 프로세스를 낭비하는 것처럼 보입니다. 하지만, 규모가 큰 일 단위의 로드 볼륨과 규모가 작은 로드 창이 있는 데이터 웨어하우스는 다음과 같은 사항에 의해 로드 성능에서 중요한 이득을 찾을 수 있습니다.

데이터가 로드되도록 텍스트 파일을 생성하면 클러스터된 인덱스 순서대로 정렬됩니다.


비어있는 일 단위 파티션에 대량으로 로드합니다.


클러스터되지 않은 인덱스를 모두 만듭니다.


UNION ALL 뷰를 재생성하여 새 파티션을 온라인으로 가져옵니다.


일 단위 파티션에서 삽입하고 인덱스를 재작성하고 UNION ALL 뷰를 재생성하여 새로운 주 단위 파티션을 매주 만들고 채웁니다. 그런 다음 일 단위 파티션이 삭제될 수 있습니다.


데이터가 오래되면 주 단위 또는 월 단위 파티션으로 이동하여 더 많은 파티션이 UNION ALL 뷰에 온라인으로 유지할 수 있습니다.


SQL Server 2000 Analysis Services에서 파티션 사용
SQL Server 엔터프라이즈 버전의 Analysis Services는 관계형 데이터베이스의 분할된 테이블과 유사한 분할된 큐브를 명시적으로 지원합니다. 큰 크기에 적당한 큐브인 경우, 파티션이 쿼리 성능, 로드 성능을 향상시키며 큐브의 유지 관리도 용이합니다. 파티션은 한 차원 이상으로 디자인될 수 있지만, 큐브는 대개 Dates 차원으로만 분할됩니다. 새 파티션 생성을 포함한 분할된 큐브의 증분 로드는 사용자 지정 응용 프로그램에 의해 실행되어야 합니다.

참고 파티션은 여러 개의 실제 서버에 로컬 또는 분산하여 저장할 수 있습니다. 여러 개의 서버 간에 있는 분산 파티션이 규모가 아주 큰 시스템에도 유리하지만, 테스트 결과 큐브가 멀티테라바이트 크기의 범위에 있을 때 분산 파티션 솔루션이 가장 효과적입니다. 이 기사에서는 로컬 분할된 큐브만 고려합니다. 새 파티션 생성을 포함한 분할된 큐브의 증분 로드는 사용자 지정 응용 프로그램에 의해 실행되어야 합니다.

파티션의 장점
쿼리 성능
쿼리의 성능은 큐브를 분할하는 작업에 의해 충분히 향상됩니다. 관계형 데이터베이스의 100 기가바이트(GB)의 데이터만을 기반으로 하는 알맞은 크기의 큐브도 파티션 작업으로 이득을 봅니다. 큐브 파티션 작업의 장점은 다중 사용자가 로드할 때 더욱 두드러집니다.

각 응용 프로그램의 쿼리 성능 향상은 큐브의 구조, 사용 패턴, 파티션 디자인에 따라 달라집니다. 월 단위로 분할된 큐브에서 1개월의 데이터만 필요한 쿼리는 한 개의 파티션만 액세스합니다. 일반적으로, 단일 파티션에 있는 규모가 큰 큐브를 제대로 디자인된 로컬 파티션 작업 방법으로 이동하는 것은 쿼리의 성능을 100퍼센트에서 1000퍼센트까지 향상시킵니다.

오래된 데이터 정리
Analysis Services 시스템 관리자는 관계형 데이터 웨어하우스로 최근의 데이터만 큐브에 보관할 수 있습니다. 단일 파티션으로 오래된 데이터를 제거하는 방법은 큐브를 다시 처리하는 것뿐입니다. 관리자는 Dates 차원으로 분할하여 시스템의 중단 시간 없이 오래된 파티션을 제거할 수 있습니다.

유지 관리
관리 측면에서 보면, 파티션은 다른 파티션에 영향을 주지 않고 추가하거나 제거할 수 있는 독립적인 데이터 단위입니다. 이것은 시스템의 데이터 수명을 관리하는 데 유용합니다. 각 큐브 파티션은 별도의 파일 집합에 저장됩니다. 이 데이터 파일의 작업을 백업하고 복원하면 더 작은 파티션 파일을 쉽게 관리할 수 있습니다. 각 파티션 파일의 크기가 2GB 미만일 때 더욱 효율적입니다. 이러한 경우에는 보관 및 복원 유틸리티가 효과적입니다. 큐브의 일부가 손상되었거나 올바르지 않은 데이터 또는 일치하지 않은 데이터를 포함하는 경우에는 파티션이 전체 큐브보다 더 빠르게 다시 처리될 수 있습니다. 또한, 저장소 모드와 오래된 파티션의 집계 디자인을 변경하여 공간을 절약할 수 있습니다.

별도의 파티션은 별도의 데이터 원본을 사용할 수 있습니다. 단일 큐브는 여러 관계형 데이터베이스에서 나오는 데이터를 결합할 수 있습니다. 예를 들어, 유럽의 데이터와 북미의 데이터가 별도의 서버에 호스트되도록 회사 데이터 웨어하우스를 구성할 수 있습니다. 지리적으로 분할된 큐브는 전혀 다른 데이터 원본을 논리적으로 결합할 수 있습니다. 단일 큐브 정의가 제대로 작동하려면 원본 서버의 관계형 스키마가 실제로 동일해야 합니다.

로드 성능
분할된 큐브는 여러 개의 파티션이 병렬로 로드되므로 분할되지 않은 큐브보다 훨씬 빠르게 로드될 수 있습니다. 아래 설명한 바와 같이, 파티션을 병렬로 처리하려면 타사 도구를 구입하거나 간단한 사용자 지정 도구를 작성해야 합니다. 다중 프로세서 시스템에서 이러한 로드 성능의 장점이 중요합니다. 병렬 처리 도구는 CPU 사용률이 90퍼센트가 되어야 합니다. 일반적으로 이 성능은 두 개의 프로세서 단위로 한 개의 파티션과 두 개의 파티션 간에 동시에 처리됩니다. 예를 들어, 큐브를 처리하는 데 모두 사용되는 네 개의 프로세서가 있는 시스템에서는 두 개의 파티션과 네 개의 파티션 간에 동시에 처리하게 됩니다. 시스템에 있는 프로세서보다 더 많은 파티션을 처리하면 성능이 현저히 떨어집니다. 두 개의 프로세스를 한 단위로 하는 한 개의 파티션이 무난합니다. 이상적인 개수는 원본 데이터베이스에서 이동하는 데이터의 속도, 집계 디자인, 저장소 및 기타 요소에 따라 다릅니다.

몇몇 환경에서는 파티션을 증분 처리하는 것보다 파티션을 다시 작성하는 것이 더 효율적인 경우도 있습니다. 전체 큐브가 단일 파티션에 있으면 이러한 경우는 드뭅니다.

파티션의 단점
복잡성
파티션의 주요 단점은 관리자가 파티션을 관리하기 위해 응용 프로그램을 작성해야 한다는 점입니다. 파티션을 관리하기 위해 응용 프로그램을 처음으로 디자인, 테스트, 롤링 아웃하지 않고는 분할된 큐브를 제품에 적용하는 것은 적절하지 않습니다. 이 기사의 목표 중 하나는 파티션 관리 응용 프로그램과 관련된 문제와 디자인 결정에 대해 설명하는 것입니다.

메타데이터 작업
파티션의 수가 증가함에 따라 큐브 정의 탐색과 같은 메타데이터 작업의 성능이 떨어집니다. 메타데이터 작업의 성능 저하는 최종 사용자보다는 관리자의 책임이 됩니다. 하지만 과도하게 분할된 큐브는 관리하기 어렵게 됩니다.





디자인 고려 사항
파티션의 개요
효과적인 쿼리 계획으로 여러 가지 사항을 균형 있게 고려할 수 있습니다.

파티션의 개수: Analysis Services는 큐브의 파티션 수를 실제로는 제한하지 않지만 수 천개로 분할된 큐브는 관리하기 어렵습니다. 또한, 여러 파티션으로부터 결과 집합을 결합하는 비용이 파티션을 선택하는 데 유익한 쿼리 성능보다 더 중요합니다. 이러한 문제는 큐브 디자인, 쿼리 패턴, 하드웨어에 따라 달라지기 때문에 대강 제공하는 것은 어렵지만, 큐브 저장소의 각 기가바이트 또는 팩트 데이터의 1,000만 개의 행마다 하나의 파티션을 갖는 것이 안전할 수 있습니다. 다시 말해, 해당 데이터 볼륨에 적합한 하드웨어의 100GB 큐브(또는 10억 팩트)는 100개의 파티션을 쉽게 지원해야 합니다. 파티션 디자인에 이보다 더 많은 파티션이 필요하면 대체 파티션 계획의 성능을 테스트하는 것이 현명합니다.


로드 및 유지 관리: 데이터는 기본적으로 시간과 같이 특정 차원에 따라 큐브로 이동합니다. 큐브를 채우고 새로 고치는 스테이징 응용 프로그램을 지원하기 위해서는 차원이 기본 파티션 슬라이스가 될 수도 있습니다. 예를 들어, Dates 차원은 일반적으로 첫 번째 파티션 차원입니다. 다른 응용 프로그램은 지역별, 고객 세그먼트 등으로 분할된 데이터를 받을 수도 있습니다. 별도의 파티션은 별도의 데이터 원본을 사용할 수 있기 때문에 큐브 채우기 프로그램은 분산된 데이터 웨어하우스나 다른 원본 시스템에서 데이터를 효율적으로 로드할 수 있습니다.


쿼리 성능: 파티션을 효과적으로 디자인하려면 일반적인 사용자 쿼리 패턴을 인식해야 합니다. 대부분의 세부적인 사용자 쿼리에 대해 이상적인 파티션 차원은 매우 선택적입니다. 예를 들어, 아주 최근의 기간에는 많은 쿼리가 세부적으로 초점을 맞추기 때문에 Date에 따른 파티션 작업은 쿼리 성능을 향상시키기도 합니다. 이와 유사하게, 많은 사용자들이 지리적으로나 조직적으로 쿼리에 초점을 맞추기도 합니다. 최상의 쿼리 성능 향상을 위해, 가능한 한 아주 적은 수의 파티션으로 쿼리를 다룹니다.
정적인 차원 또는 Date와 같이 예측할 수 있는 방법으로 변경하는 파티션을 관리하는 것이 더 쉽습니다. 예를 들어, "미국의 주(state)"에 따른 파티션은 응용 프로그램 디자이너가 51번째 주로부터 많은 경고를 받을 수 있기 때문에 상대적으로 정적입니다. 이와 반대로, Product 차원에 따른 파티션은 새 제품이 자주 추가되기 때문에 시간이 지나면 변경될 수 있습니다. 동적 차원에 따라 분할하는 것도 적합할 수 있지만, 필연적으로 관리 시스템은 더 복잡해야 합니다. "변경 중"으로 표시된 차원인 경우에는 해당 차원으로 분할할 수 없습니다. 어떠한 경우라도, 예상하지 못한 구성원에 대한 데이터를 보유하기 위해 "다른 모든" 파티션을 생성하는 것이 바람직합니다.

슬라이스 및 필터
관계형 파티션의 경우, Analysis Services 파티션은 각 파티션에서 볼 수 있는 데이터를 정의하는 관리자에 의존합니다. RDBMS는 CHECK CONSTRAINT를 사용하여 이 기능을 실행하고 Analysis Services는 슬라이스를 사용하여 이 기능을 실행합니다. 슬라이스는 [Dates].[1999] 또는 [Dates].[1999].[Q1].와 같은 차원의 단일 구성원에 설정됩니다. 분석 관리자 파티션 마법사에서는 슬라이스가 "데이터 슬라이스를 선택하십시오(선택 사항)."라는 제목으로 화면에 설정됩니다. DSO에서는, 파티션의 차원 수준 개체의 SliceValue 속성을 사용하여 슬라이스를 액세스하고 설정합니다. 이 문서에서는 나중에 간단한 구문을 제공합니다.

각 파티션의 정의에도 이 파티션으로 이동하는 원본 데이터에 대한 정보가 있습니다. 파티션 메타데이터가 필요한 정보를 저장하여 파티션을 채웁니다. 관리자는 데이터 원본과 팩트 테이블을 파티션 마법사를 사용하여 설정하거나 DSO와 함께 프로그래밍 방식을 사용하여 설정할 수 있습니다. 파티션이 처리되는 시간에 파티션의 SliceValue 속성 설정이 원본에 대한 필터에 자동으로 변환됩니다. 파티션 정의는 파티션을 채우게 될 쿼리를 구체화하는 데 사용될 수 있는 추가 필터, SourceTableFilter 속성을 선택적으로 포함합니다. 파티션이 처리되는 시간에 원본 데이터에 대해 생성된 쿼리의 WHERE 절에는 슬라이스 정의를 기반으로하는 기본 조건과 SourceTableFilter 속성이 정의하는 모든 추가 필터를 모두 포함합니다.

파티션이 올바르게 작동하려면 슬라이스와 필터를 제대로 정의해야 합니다. 슬라이스의 역할은 쿼리 성능을 향상시키는 것입니다. Analysis Services 엔진은 파티션 슬라이스 정의에 있는 정보를 사용하여 원본으로 사용하는 데이터가 있는 파티션에만 쿼리를 직접 연관시킵니다. 쿼리는 정의된 파티션 슬라이스 없이 분할된 큐브에서 정확히 확인하지만, 슬라이스 정의가 없을 때 각 쿼리가 모든 파티션을 검사해야 하기 때문에 쿼리 성능은 최적화되지 않습니다.

필터와 원본 메타데이터의 역할은 파티션으로 이동하는 데이터를 정의하는 것입니다. 이러한 요소들이 올바르게 정의되지 않으면 전체 큐브에 잘못된 데이터가 있게 됩니다. 파티션이 처리될 때 Analysis Services는 슬라이스와 일치하도록 큐브에 저장된 데이터를 제한합니다. 하지만, 데이터가 다른 파티션에도 로드되지 않도록 확인하는 작업은 실행하지 않습니다.

예를 들면, 연 단위로 큐브를 분할하고 1998년 파티션에 대한 슬라이스를 [Dates].[Year].[1997]로 잘못 설정해도 필터는 1998년으로 제약된 경우입니다. 파티션이 처리될 때 행을 전혀 포함하지 않는 것은 올바른 결과라고 볼 수 있습니다. 이와 반대로, 1998년에 대한 기존 파티션이 있고 1998년 12월에 대한 새 파티션을 추가한 경우에는 1998년 12월 데이터를 두 번 로드하기 쉽게 되고, Analysis Services로부터 이 이벤트가 발생했다는 알림을 받지 않게 됩니다.

파티션 슬라이스와 필터를 정렬되게 유지하기는 어렵지 않지만 파티션 관리 시스템 디자이너가 해당 문제점들을 인식하는 것은 반드시 필요합니다.

고급 슬라이스 및 필터
대부분의 파티션 방법은 파티션에 맞는 차원 수준을 확인하고 해당 차원의 각 구성원의 데이터를 그 파티션에 입력하는 것입니다. 예를 들면, "연 단위 파티션" 또는 "주(state) 단위 파티션"입니다.

큐브의 한 부분에 드릴다운하는 파티션 계획을 정의하는 방법도 일반적입니다. 예를 들어, 최근 데이터는 일 단위 또는 주 단위로 분할되고 오래된 데이터는 월 단위 또는 연 단위로 분할될 수 있습니다.

사용 패턴과 데이터 카디널리티에 따라 더 복잡한 파티션 계획을 디자인하는 것이 바람직합니다. 예를 들면, 고객의 80퍼센트는 캘리포니아에 거주하고 10퍼센트는 오레곤에, 나머지 10퍼센트는 나머지 지역에 고루 분산되어 거주하고 있는 경우입니다. 게다가 대부분의 분석은 로컬 고객(캘리포니아)에 초점을 맞춥니다. 이 경우, 관리자는 캘리포니아에 대한 지역 수준 파티션, 오레곤에 대한 주(state) 수준 파티션, 나머지 지역에 대한 한 개의 파티션을 생성할 수 있습니다.

해당 슬라이스는 다음과 같습니다.

캘리포니아 지역: [All USA].[CA].[Amador] ... [All USA].[CA].[Yolo]


오레곤 주: [All USA].[OR]


나머지 지역: [All USA]


위에 설명한 바와 같이, 이러한 파티션을 제대로 채우기 위해서는 원본 데이터 필터가 올바르게 정의되어야 합니다. 캘리포니아와 오레곤의 데이터와 결합해야 하는 쿼리도 "나머지 지역" 파티션을 검토해야 합니다. 관련 데이터가 없음을 확인하기 위해 "나머지 지역"의 맵을 검토하는 데 Analysis Services를 사용하는 비용이 비싸지 않은 반면, 큐브를 CA에 드릴다운하여 주(state) 단위로 일정하게 분할하면 쿼리 성능이 향상될 수 있습니다. 일정하지 않은 파티션을 유지 관리하기 위해 필요한 응용 프로그램 논리도 더 복잡하며, 일반적으로 이 분할 방법은 바람직하지 않습니다. 하지만, 유지 관리 응용 프로그램을 주의 깊게 디자인하고 쿼리 성능 보완을 이해하면 특정 디자인 문제점들을 해결할 수도 있습니다.
파티션 정렬
이 기사의 처음의 반은 RDBMS의 파티션을 설명하기 때문에 관계형 파티션에 따라 Analysis Services 파티션을 정렬해야 할지를 고려해야 합니다. 두 가지 파티션 방법이 동일할 필요는 없지만, 파티션을 디자인하고 작성하며 파티션이 비슷한지를 이해하는 데는 파티션 관리 응용 프로그램이 더 쉽습니다. 날짜에 따라 두 시스템에 동일하게 분할하는 것이 일반적인 방법입니다. 큐브의 두 번째 또는 세 번째 차원에 따라 슬라이스로 분할할 수도 있습니다.

가장 간단한 방법은 모든 큐브 파티션에 대해 UNION ALL 뷰를 원본 팩트 테이블로 사용하는 것입니다. 큐브 파티션이 관계형 파티션에 정렬되면, 각 큐브 파티션은 UNION ALL 뷰의 주위에서 관련된 관계형 파티션을 직접 나타낼 수 있습니다. 관계형 데이터베이스로부터 데이터를 추출하는 쿼리를 처리하는 큐브는 이러한 구성에서 가장 빠르게 실행합니다. 이러한 성능 향상의 보완으로서 유지 관리 응용 프로그램은 각 파티션에 원본 테이블이 올바르게 연결되었는지를 확인해야 합니다.

관계형 데이터베이스가 Analysis Services 큐브를 채우기 위해서만 존재하고 다른 쿼리를 제공하지 않는 경우에는 시스템 관리자가 UNION ALL 뷰를 생성하거나 관리하지 않도록 할 수도 있습니다. 관계형 테이블의 인덱스는 큐브에 데이터를 로드하는 단일 쿼리를 최적화하도록 디자인됩니다. 이 경우, 관계형 데이터베이스는 완전한 데이터 웨어하우스보다는 스테이징 영역으로서의 역할을 합니다.

저장소 모드 및 집계 계획
각 파티션에는 자체 저장소와 집계 계획이 있습니다. 자주 액세스되지 않는 데이터는 MOLAP이 아닌 ROLAP 또는 HOLAP으로서 간단히 집계되거나 저장됩니다. 매개 변수를 변경하면 파티션을 다시 처리해야 하므로 시간을 초과하여 증분 로드된 큐브는 자체 파티션의 시간 차원에 따라 이 기능을 사용하지 않을 수 있습니다. 대부분의 상황에서 처리 시간의 비용 및 시스템의 복잡성으로 최소한의 공간을 절약하기는 어렵습니다.

이와 반대로, 다른 차원에 따른 파티션에는 별도의 집계 계획이 있는 경우가 있습니다. 사용 빈도 기반 최적화 마법사는 각 파티션에 대한 집계를 디자인합니다. 시스템 관리자는 최적화 마법사를 가장 최신의 파티션에 초점을 맞추고 현재 파티션에 있는 각각의 새 파티션 집합에 대한 집계 디자인을 기반으로 하여 집계 디자인을 가능한 한 최신 상태로 유지해야 합니다.


분할된 큐브 관리
개발자는 다양한 도구를 사용하여 관계형 파티션에 대해 관리 시스템을 작성할 수 있습니다. SQL-DMO를 사용하는 것이 가장 좋지만, 저장 프로시저, 확장된 저장 프로시저, 테이블 정의를 포함하는 텍스트 파일을 구문 분석하는 Perl 스크립트를 사용하여 효과적인 시스템을 작성할 수 있습니다. 이와 반대로, 큐브 파티션 유지 관리 프로그램은 반드시 DSO를 사용해야 합니다.

전통적인 데이터베이스에 대한 기본 지식이 있는 시스템 개발자에게는 개체 모델을 사용하여 데이터베이스 개체 인스턴스를 생성하는 것이 낯설 수도 있습니다. 개발자는 Microsoft® Visual Basic® Scripting Edition (VBScript), Microsoft® JScript®, Perl과 같은 익숙한 스크립트 언어를 사용하거나 Visual Basic (VB) 또는 C++와 같은 개발 환경을 사용하여 DMO 및 DSO를 사용하는 모듈을 개발할 수 있습니다. 이러한 모듈은 운영 체제, SQL-Agent로부터 예약하거나 DTS 패키지로부터 호출할 수 있습니다. 이전에 개체 모델을 사용한 경험이 없는 개발자일지라도 관리 시스템을 작성하기 위해 DSO를 사용하는 것이 파티션 사용보다 우선하는 이유로 보여서는 안됩니다. 이 기사의 후반에서 파티션을 복제하기 위한 스크립트 사용법을 나타내는 VBScript 예제를 설명합니다.

관계형 데이터 웨어하우스가 파티션을 사용하면, 큐브 파티션 관리 시스템을 관계형 데이터베이스 파티션 관리 시스템의 일부로서 디자인해야 합니다. 큐브 파티션 관리 시스템은 다음과 같은 기능을 수행해야 합니다.

필요에 따라 새 파티션을 생성합니다. 일반적으로 Dates 차원과 관련된 일정에 따라 생성합니다.


데이터를 파티션에 로드합니다.


오래된 파티션을 삭제합니다(선택적).


파티션을 병합합니다(선택적).

새 파티션을 생성
파티션 관리 시스템이 관계형 데이터베이스에 새 날짜 파티션을 생성할 때, 그 날짜에 해당하는 모든 필요한 큐브 파티션을 함께 생성해야 합니다. 새 차원 구성원이 파티션 슬라이스 중 하나에 따라 추가될 수도 있기 때문에 큐브의 차원을 증분 업데이트하고 새 파티션을 생성하는 것이 바람직합니다.

가장 단순한 예는 날짜로만 큐브를 분할하는 경우입니다. 파티션 관리 시스템은 적절한 일정(일, 주, 월 등)으로 한 개의 새 파티션을 생성합니다.

큐브가 날짜뿐만 아니라 다른 차원으로 분할된 경우에는 파티션 관리 시스템이 많은 파티션을 한 번에 추가합니다. 월 단위와 미국의 주(state) 단위로 분할된 큐브를 예로 들 수 있습니다. 시스템이 50개의 새로운 주(state) 파티션을 매달 생성하게 됩니다. 이 경우, 마지막 달의 파티션을 복제하고 슬라이스 및 원본 테이블 이름과 같은 필요한 특성을 편집하고 큐브에 파티션 정의를 업데이트하여 현재 달의 파티션을 생성하는 것이 안전합니다.

하지만, 월 단위와 제품 브랜드로 분할된 큐브를 고려해야 합니다. 제품 브랜드는 주(state) 또는 지방(province)보다는 오래 지속되지 못하기 때문에 큐브가 지속되는 동안 제품 계층 구조에 새 브랜드를 추가해야 합니다. 유지 관리 응용 프로그램은 새 브랜드의 데이터를 유지하기 위해 파티션을 생성해야 합니다. 다음과 같은 방법이 바람직합니다.

차원을 처리한 후에 새 파티션을 생성합니다.


기존 파티션이 저장소 모드 및 집계 계획에 계속 유지되도록 복제합니다.


파티션 작업 수준의 모든 새 구성원에 대한 파티션을 생성하면서 새 구성원에 대해 처리된 차원을 검색합니다. 시스템은 기본 저장소 모드 및 집계 계획을 지정해야 합니다.
파티션 슬라이스와 필터 정의가 시간이 지나도 정렬되어 정확하게 남아 있도록 파티션 관리 시스템을 주의 깊게 디자인해야 합니다. 관계형 데이터베이스가 분할되고 파티션이 앞서 언급했듯이 주기적으로 병합되면, 파티션 관리 시스템은 큐브 파티션 정의를 업데이트하여 원본 데이터와 동기화해야 합니다. 큐브 파티션은 다시 처리해야 할 필요가 없지만 해당 정의는 나중에 다시 처리해야 하는 경우를 대비해서 변경해야 합니다.

데이터 무결성
데이터 무결성은 큐브 디자인과 파티션 관리 시스템이 단지 한 개의 파티션에만 데이터를 처리하도록 하기 위한 작업입니다. Analysis Services는 팩트 테이블의 모든 행이 큐브에서 인스턴트가 생성되는 것을 검사하지 않고, 한 개의 파티션에만 행이 로드되는 것도 검사하지 않습니다. 팩트 행이 우연히 두 파티션에 로드된 경우에, Analysis Services는 별도의 팩트로 인식하게 됩니다. 그러면 이 데이터는 두 번 집계되고 쿼리는 잘못된 결과를 반환합니다.

파티션 처리
파티션 처리는 큐브를 처리하는 것과 기본적으로 동일합니다. 처리 작업의 기본 단위는 한 개의 파티션입니다. 분석 관리자 처리 마법사는 큐브 또는 파티션 처리에 대해 다음과 같은 세 개의 모드를 제공합니다.

증분 업데이트는 기존 큐브 또는 파티션에 새 데이터를 추가하고 새 데이터에 의해 영향을 받는 집계를 업데이트하고 추가합니다.


데이터 새로 고침 기능이 큐브 또는 파티션의 모든 데이터와 집계를 삭제하고 큐브 또는 파티션에 있는 데이터를 다시 작성합니다.


전체 처리 기능이 큐브 또는 파티션의 구조를 완전히 재작성한 다음, 데이터와 집계를 새로 고칩니다.
증분 처리에서는 관리자가 원본 쿼리의 필터 조건을 정의하여 큐브에 대한 새 데이터 집합을 확인해야 합니다. 일반적으로 이 필터는 날짜를 기반으로 하는데, 이 날짜는 팩트 테이블에 저장된 이벤트 날짜나 처리 날짜입니다.

DTS 큐브 처리 작업에서도 이와 동일한 기능을 사용할 수 있습니다. 대부분의 시스템에서는 DTS 큐브 처리 작업을 사용하여 큐브 처리의 일정을 예약합니다. 증분 처리된 큐브는 동적 속성 작업을 사용하여 원본 필터를 변경합니다. 데이터를 새로 고치는 데 필요한 코드의 수보다 증분 업데이트를 하는 데 좀 더 많은 코드가 필요하지만 DSO에서 사용자 지정 코드를 작성할 때도 이러한 기능을 사용할 수 있습니다.

파티션 관리 시스템을 디자인할 때는 증분 큐브 또는 파티션 처리에 이전에 처리된 파티션이 필요합니다. 처리되지 않은 큐브 또는 파티션에 증분 처리를 사용하지 않아야 합니다.

Dates 차원으로만 분할된 큐브에는 간단한 로드 관리 요구 사항이 있습니다. 일반적으로 단일 파티션은 각 로드 주기에 대해 업데이트합니다. 데이터를 증분 업데이트할지 또는 새로 고칠지에 따라 업데이트를 결정합니다. 대부분의 Date 차원 큐브는 간단한 DTS 패키지로부터 관리됩니다.

여러 차원으로 분할된 큐브에는 다음과 같은 추가적인 문제점 및 장점이 있습니다.

문제점: 처리해야 할 파티션이 많습니다.


문제점: 파티션의 개수가 변경될 수 있습니다.


장점: 파티션 병렬 로드를 할 수 있습니다.


장점: 쿼리의 선택 폭이 넓어 향상된 쿼리 성능을 사용할 수 있습니다.
여러 차원에서 분할하는 대부분의 응용 프로그램은 파티션을 병렬로 로드하기 위해 큐브 처리 시스템을 디자인합니다. 병렬 로드 시스템에서는 매개 변수가 동적 속성 작업으로 업데이트된 여러 개의 DTS 패키지를 동시에 시작할 수 있습니다. 이 구조가 적합하긴 해도 다루기가 힘들기 때문에 많은 시스템에서는 파티션을 업데이트하기 위해 기본 DSO 코드를 대신 사용합니다. 파티션을 병렬로 처리하는 예제 도구를 사용할 수 있습니다.

파티션 병합
Date에 따라 분할된 큐브에서는 시간이 지나면 파티션의 수가 증가하게 됩니다. 위에 설명한 바와 같이, 파티션의 수가 증가함에 따라 쿼리의 성능은 저하됩니다. 500개 이상의 파티션이 있는 큐브의 개발을 포함한 테스트 결과 쿼리의 성능이 저하되지 않았습니다. 메타데이터 작업의 속도가 느린 점과 같이 많은 파티션의 기타 단점으로 인해 데이터베이스를 관리하기 어려워질 수 있기 때문에 시스템 관리자는 이 테스트 결과에 도달하기 전에는 동의하지 않을 수도 있습니다.

Analysis Services는 DSO와 분석 관리자를 통해 파티션을 병합할 수 있는 기능을 지원합니다. 두 파티션을 병합할 때, 첫 번째 파티션의 데이터가 두 번째 파티션에 포함됩니다. 두 파티션에는 반드시 동일한 저장소 모드와 집계 계획이 있어야 합니다. 병합이 완료되면 첫 번째 파티션은 삭제되고 두 번째 파티션에 결합된 데이터가 포함됩니다. 병합 처리 작업은 큐브 데이터에서만 발생합니다. 데이터 원본은 병합이 처리되는 동안에는 액세스되지 않습니다. 두 개의 파티션을 병합하는 프로세스는 매우 효율적입니다.

시스템 디자인에 병합된 파티션이 포함되어 있으면, 병합 프로세스는 분석 관리자를 통해서 보다는 프로그래밍 방식으로 발생해야 합니다. 파티션 병합은 간단하며 다른 DSO 작업과 마찬가지로 코드가 거의 필요없습니다. 파티션 병합 시스템은 파티션이 필요에 따라 다시 처리될 수 있도록 원본 필터에 대한 정확한 메타데이터 정보가 있는 최종 병합 파티션을 확인해야 합니다. 파티션 병합 프로세스는 슬라이스 정의를 올바르게 변경하고 필터 정의도 결합합니다. 하지만 병합 프로세스에는 두 파티션이 동일한 테이블이나 데이터 원본에서 채워질 필요가 없기 때문에 다시 채울 수 없는 두 파티션을 병합할 수 있습니다.

다음으로 고려해야할 문제는 모든 파티션과 같은 병합된 파티션의 이름을 바꿀 수 없다는 것입니다.

이러한 문제점들은 다음과 같은 실용적인 시스템 디자인을 사용하여 해결할 수 있습니다.

명확한 명명 규칙을 사용합니다.


일관적인 파티션 병합 계획을 따릅니다.


큐브 파티션이 관계형 파티션과 일치하도록 주의해야 합니다. 일치하지 않으면 관계형 데이터 웨어하우스를 분할하지 않아야 합니다.
주 단위로 데이터를 분할하는 Sales 큐브를 예로 들 수 있습니다. 현재 주가 일 단위로 분할된 다음, 그 주의 마지막에 병합됩니다. 여기에서 사용한 파티션의 이름은 Sales_yyyymmdd입니다. 이 이름의 날짜는 파티션에 있는 데이터의 첫 번째 날입니다. 2000년 11월의 경우, Sales_20001105, Sales_20001112, Sales_20001119, Sales_20001126과 같은 주 단위의 파티션을 얻을 수 있습니다. 그 다음 주에는, Sales_20001203, Sales_20001204 순서로 Sales_20001209까지 생성하여 처리합니다. 시스템을 거의 사용하지 않을 때, 일요일 프로세스 창에서 주 단위 파티션만 남겨두고 20001204에서 20001209까지를 Sales_20001203에 병합할 수 있습니다. 비어 있는 새로운 파티션을 원하는 이름으로 생성하고 여기에 다른 파티션을 병합하여 파티션의 이름을 효과적으로 바꿀 수도 있습니다.

오래된 파티션 롤링 오프
Date로 분할된 큐브에서 오래된 데이터를 삭제하는 것은 가장 오래된 파티션(파티션 집합)을 삭제하는 것만큼 간단합니다. 여기에서 언급한 다른 작업과 마찬가지로 이 프로세스는 분석 관리자를 통해 임의로 관리하기 보다는 프로그래밍 방식으로 관리해야 합니다. 지금까지 설명한 내용을 이해하면, 이 모듈의 코드를 쉽게 작성하고 테스트할 수 있습니다.


결론
1억 개 이상의 팩트 행이 있는, 중간 크기에서 큰 크기의 Analysis Services 큐브에 로컬 파티션을 사용하는 것이 바람직합니다. 파티션 작업으로 Analysis Services 데이터베이스의 쿼리 성능이 향상됩니다. 큐브에서 오래된 데이터를 제거하면 분할된 큐브를 유지 관리하기가 더 쉽습니다. 하지만, 큐브를 분할하면 이러한 파티션을 관리할 응용 프로그램이 필요합니다.

관계형 데이터 웨어하우스 데이터베이스에서 분할하는 것은 Analysis Services에서 분할하는 것과 비슷한 개념입니다. Analysis Services의 경우, 관계형 파티션을 관리할 응용 프로그램을 반드시 작성해야 합니다. 관계형 데이터 웨어하우스에서 분할하는 경우에 인수가 반드시 필요한 것은 아닙니다. 파티션 작업은 오래된 데이터 정리 등과 같은 몇 가지 유지 관리에 대한 문제점을 처리하지만 시스템이 복잡해지는 부담이 있습니다. 제대로 인덱스된 단일 테이블과 비교해보면 쿼리 성능은 향상되지 않습니다.

Analysis Services와 SQL Server 관계형 데이터베이스는 모두 파티션이 별도의 서버에 위치한 분산 파티션을 지원합니다. Analysis Services의 분산 파티션에 대해서는 다른 기사에서 설명하겠습니다. 임의 쿼리를 지원하는 SQL Server 2000 데이터 웨어하우스 시스템에 대한 관계형 파티션을 분산하는 것은 바람직하지 않습니다.

분할된 큐브는 수 많은 파티션과 함께 향상된 쿼리 성능을 나타냅니다. 규모가 큰 큐브의 개발자는 사용자 쿼리의 선택 폭을 최대화하고 병렬 처리에 의해 프로세스 성능을 향상시키기 위해 여러 차원에 따라 파티션 작업을 고려해야 합니다.

규모가 큰 Analysis Services 시스템에 대해서는 파티션이 바람직합니다. 관계형 데이터 웨어하우스의 파티션 작업은 몇몇 특정 웨어하우스의 유지 관리 문제를 해결하는 데 효율적이라해도 일반적으로는 바람직한 방법은 아닙니다.

추가 정보
Microsoft SQL Server 온라인 설명서에는 인덱스된 뷰에 대한 더 자세한 정보가 있습니다. 추가 정보에 대해서는 다음과 같은 리소스를 참조하십시오.

http://www.microsoft.com/korea/sql/의 Microsoft SQL Server 웹 사이트.


http://www.sqlmag.com 의 SQL Server Magazine.


news://news.microsoft.com 의 microsoft.public.sqlserver.server 및 microsoft.public.sqlserver.datawarehouse 뉴스 그룹.


SQL Server의 Microsoft Official Curriculum 코스. 최신 코스 정보에 대해서는 http://www.microsoft.com/korea/traincert를 참조하십시오.



부록: 파티션 복제를 위한 VBScript 코드 예제
'/********************************************************************* ' 파일: ClonePart.vbs ' '설명: 이 예제 스크립트는 큐브의 최신 파티션을 기반으로 FoodMart ' 2000 Sales 큐브에 새 파티션을 생성합니다.이 스크립트의 목적은 ' 파티션을 복제하는 데 사용되는 DSO 호출의 종류를 나타내는 ' 것입니다.결과 파티션은 처리되지만 ' 데이터를 큐브에 추가하지 않습니다. ' ' 이 스크립트를 실행하여 결과를 얻은 후에는 ' 결과 파티션을 삭제할 수도 있습니다. ' ' 매개 변수: 없음 '*********************************************************************/ Call ClonePart Sub ClonePart() On Error Resume Next Dim intDimCounter, intErrNumber Dim strOlapDB, strCube, strDB, strAnalysisServer, strPartitionNew Dim dsoServer, dsoDB, dsoCube, dsoPartition, dsoPartitionNew ' 서버, 데이터베이스, 큐브 이름 변수를 초기화합니다. strAnalysisServer = "LocalHost" strOlapDB = "FoodMart 2000" strCube = "Sales" ' VBScript는 열거된 상수를 직접 사용하도록 지원하지 않습니다. ' 하지만, 열거된 상수를 대신하도록 상수를 정의할 수 있습니다. Const stateFailed = 2 Const olapEditionUnlimited = 0 ' 분석 서버에 연결합니다. Set dsoServer = CreateObject("DSO.Server") dsoServer.Connect strAnalysisServer ' 연결되지 않으면 스크립트를 종료합니다. If dsoServer.State = stateFailed Then MsgBox "오류-'" & strAnalysisServer _ & "' 분석 서버에 연결할 수 없습니다.", ,"ClonePart.vbs" Err.Clear Exit Sub End if ' Analysis Services의 엔터프라이즈 버전과 ' 개발자 버전 릴리스에서만 특정 파티션 ' 관리 기능을 사용할 수 있습니다. If dsoServer.Edition <> olapEditionUnlimited Then MsgBox "오류-파티션을 관리하기 위해 이 기능은 엔터프라이즈 또는" & _ "SQL Server의 개발자 버전" & _ "이 필요합니다.", , "ClonePart.vbs" Exit Sub End If ' 데이터베이스에 유효한 데이터 원본이 있어야 합니다. Set dsoDB = dsoServer.mdStores(strOlapDB) If dsoDB.Datasources.Count = 0 Then MsgBox "오류-'" & _ strOlapDB & "' 데이터베이스에 데이터 원본이 없습니다." , , "ClonePart.vbs" Err.Clear Exit Sub End If ' 큐브를 검색합니다. If (dsoDB.mdStores.Find(strCube)) = 0 then MsgBox "오류-'" & strCube & "' 큐브가 없습니다.", , _ "ClonePart.vbs" Err.Clear Exit Sub End If ' dsoCube 변수를 원하는 큐브에 설정합니다. Set dsoCube = dsoDB.MDStores(strCube) ' 파티션을 검색합니다. If dsoCube.mdStores.Count = 0 Then MsgBox "오류-'" & strCube & _ "' 큐브에 파티션이 없습니다.", , "ClonePart.vbs" Err.Clear Exit Sub End If ' dsoPartition 변수를 원하는 파티션에 설정합니다. Set dsoPartition = dsoCube.MDStores(dsoCube.MDStores.Count) MsgBox "새 파티션은 기존 파티션을 기반으로 합니다: " _ & chr(13) & chr(10) & _ dsoDB.Name & "." & dsoCube.Name & "." & _ dsoPartition.Name, , "ClonePart.vbs" ' 별도의 데이터베이스는 별도의 인용 부호를 사용하므로 ' 해당 데이터원본에서 인용 부호를 가져옵니다. Dim sLQuote, sRQuote sLQuote = dsoPartition.DataSources(1).OpenQuoteChar sRQuote = dsoPartition.DataSources(1).CloseQuoteChar '********************************************************************* ' 원하는 파티션을 기반으로 새 파티션을 생성합니다. '********************************************************************* ' 새 파티션을 임시로 생성합니다. strPartitionNew = "NewPartition" & dsoCube.MDStores.Count Set dsoPartitionNew = dsoCube.MDStores.AddNew("~temp") ' 원하는 파티션에서 새 파티션으로 ' 속성을 복제합니다. dsoPartition.Clone dsoPartitionNew ' 파티션 이름을 "~temp"에서 ' 새 파티션에 맞게 변경합니다. dsoPartitionNew.Name = strPartitionNew dsoPartitionNew.AggregationPrefix = strPartitionNew & "_" ' 새 파티션에 대한 팩트 테이블을 설정합니다. dsoPartitionNew.SourceTable = _ sLQuote & "sales_fact_dec_1998" & sRQuote ' 새 파티션의 FromClause 및 JoinClause 속성을 ' 설정합니다. dsoPartitionNew.FromClause = Replace(dsoPartition.FromClause, _ dsoPartition.SourceTable, dsoPartitionNew.SourceTable) dsoPartitionNew.JoinClause = Replace(dsoPartition.JoinClause, _ dsoPartition.SourceTable, dsoPartitionNew.SourceTable) ' 영향이 미친 수준 및 차원의 SliceValue 속성을 원하는 값으로 ' 변경하여, 새 파티션에 의해 사용된 데이터 ' 슬라이스의 정의를 변경합니다. dsoPartitionNew.Dimensions("Time").Levels("Year").SliceValue = "1998" dsoPartitionNew.Dimensions("Time").Levels("Quarter").SliceValue = "Q4" dsoPartitionNew.Dimensions("Time").Levels("Month").SliceValue = "12" ' 행의 개수를 추정합니다. dsoPartitionNew.EstimatedRows = 18325 ' 다른 필터를 추가합니다. SourceTableFilter는 ' 이 파티션을 채우는 SQL 쿼리에 ' WHERE 절을 추가할 수 있도록 합니다. 이 필터를 사용하여 ' 새 파티션에 포함된 행이 없도록 확인합니다. 이 예제 코드의 목적을 위해 ' FoodMart 큐브의 데이터를 변경하지 않습니다. 새 파티션에서 데이터를 보려면 ' 이 코드 라인에 주석을 달아 놓습니다. dsoPartitionNew.SourceTableFilter = dsoPartitionNew.SourceTable _ & "." & sLQuote & "time_id" & sRQuote & "=100" ' 메타데이터 리포지토리에 파티션 정의를 저장합니다. dsoPartitionNew.Update ' 새 파티션 구조의 유효성을 확인합니다. IF NOT dsoPartitionNew.IsValid Then MsgBox "오류-새 파티션 구조가 잘못되었습니다." Err.Clear Exit Sub End If MsgBox "새 파티션 " & strPartitionNew & "가 생성되고 " _ & "처리되었습니다. 분석 관리자의 새 파티션을 보려면, FoodMart 2000의 " _ & "Sales 큐브의 파티션 목록을 새로 고쳐야 할 경우도 있습니다." _ & " 새 파티션에는 데이터가 없습니다.", , _ "ClonePart.vbs" ' 주석을 달아 놓은 다음 상태문이 파티션을 처리합니다. ' 실제 파티션 관리 시스템에서는 별도로 처리되고 ' DTS. ' dsoPartitionNew.Process를 통해 관리되는 경향이 있습니다. ' 완료합니다. Set dsoPartition = Nothing Set dsoPartitionNew = Nothing Set dsoCube = Nothing Set dsoDB = Nothing dsoServer.CloseServer Set dsoServer = Nothing End Sub

"이 문서의 내용은 현재 게시된 날짜에 발행된 Microsoft Corporation에서 제공하는 정보입니다. Microsoft는 시장 변화에 대응해야 하므로 이 정보에 대해 책임지지 않으며 게시된 날짜 이후의 정보에 대해서도 보증하지 않습니다."

"이 기사는 정보 제공의 목적으로만 제공됩니다. 이 문서의 정보에 대해 Microsoft는 명시적이든 묵시적이든 어떠한 보증도 하지 않습니다."

"해당 저작권법을 준수하는 것은 사용자의 책임입니다. 저작권에서의 권리와는 별도로, 이 설명서의 어떠한 부분도 Microsoft의 명시적인 서면 승인 없이는 어떠한 형식이나 수단(전기적, 기계적, 복사기에 의한 복사, 디스크 복사 또는 다른 방법) 또는 목적으로도 복제되거나, 검색 시스템에 저장 또는 도입되거나, 전송될 수 없습니다."

"Microsoft가 이 문서 본안에 관련된 특허권, 상표권, 저작권, 또는 기타 지적 소유권 등을 보유할 수도 있습니다. 서면 사용권 계약에 따라 Microsoft부터 귀하에게 명시적으로 제공된 권리 이외에, 이 문서의 제공은 귀하에게 이러한 특허권, 상표권, 저작권, 또는 기타 지적 소유권 등에 대한 어떠한 사용권도 허여하지 않습니다."

© 2001 Microsoft Corporation. All rights reserved.

"Microsoft, JScript, Visual Basic, Visual C++는 미국 및 기타 국가에서 Microsoft Corporation의 등록 상표 또는 상표입니다."

"여기에 인용된 실제 회사와 제품 이름은 해당 소유자의 상표일 수 있습니다."


[이 자료는 MSDN 라이브러리에서 가져왔습니다]

정재우 | ㈜엔코아컨설팅 선임 컨설턴트

SQL Server 2000에서 더욱 강화된 기능 가운데 하나가 바로 Top N 쿼리이다. 이미 알려진 대로 Top N 쿼리는 order by와 함께 사용할 경우 조건에 해당하는 데이터 중에서 상위 또는 하위의 일부분만을 추출할 수 있는 기능을 제공한다.
아마 이 기능을 가장 유용하게 사용할 수 있는 곳은 웹 게시판일 것이다. 강력한 Top N 쿼리의 기능을 활용하여 효율적인 웹 게시판을 구현하는 솔루션에 대한 내용은 다음 회에서 소개하기로 하겠다.
SQL Server 2000은 'Top N Engine'을 통해서 Top N 쿼리를 효율적으로 처리한다. 이 엔진의 개선된 알고리즘은 다음과 같다.
SQL Server 7.0에서는 조건에 해당하는 데이터를 모두 읽어 들인 후 full sort 방식을 통해서 결과 집합을 추출했지만, SQL Server 2000의 'Top N Engine'은 N개의 데이터를 임시로 저장할 수 있는 개체(여기서는 '버퍼'라는 용어로 대체)를 메모리에 생성한 후 이 버퍼에 저장된 가장 크거나 작은 값만을 비교 대상으로 처리한다.
여러분이 'select top 5 * from Northwind.dbo.[Order Details] order by Quantity desc'와 같은 SQL을 실행하면 다음의 알고리즘에 의해서 처리된다. 우선 5개의 데이터를 저장할 수 있는 버퍼를 생성한 후, [Order Details] 테이블의 데이터를 순차적으로 읽어 들여서 이 버퍼를 채우기 시작한다. 5개의 데이터가 버퍼에 채워지고 나면 5번째 이후에 읽혀진 데이터는 이 버퍼에 저장된 데이터의 가장 작은 Quantity 값과 비교된다. 만약 6번째로 읽어 들인 데이터의 Quantity 값이 버퍼의 최소 Quantity 값보다 크다면 이 6번째의 데이터가 버퍼에 존재하는 데이터(최소 Quantity 값을 가진)를 밀어내고 그 자리를 차지하게 된다.
Top N 쿼리는 결과 집합의 행 수를 제한하는 기본적인 기능 외에 옵티마이저의 실행 계획을 제어하거나 OR 연산자와 함께 사용시 STOP KEY의 역할 등 알려지지 않은 유용한 기능을 제공한다. 이전 회에서 소개했던 '멀티 캐시의 효과'도 상관 하위쿼리에 'Top 1' 키워드를 기술하지 않으면 전혀 다른 실행 계획을 수립하게 된다.
간단한 예제를 통해서 'Top 1' 키워드가 옵티마이저의 실행 계획에 어떠한 영향을 미치는지 확인해 보자.

[리스트 1]의 DDL을 살펴보자. '지역' 테이블과 '판매' 테이블이 존재하며, 두 테이블은 1:M의 관계를 가지고 있다. '지역' 테이블의 데이터는 겨우 10 건에 불과하며, '판매' 테이블의 데이터는 매일 수천 건 이상 발생한다고 가정하자.
이런 상황에서 고객이 다음과 같은 요청을 한다면 고려해야 할 사항은 무엇일까?
"2003년 12월 1일부터 12월 10일 사이에 발생한 판매내역을 조회하고자 한다. 조회시 각 판매지역에 해당하는 지역명도 같이 보여줘야 한다. 또한 '판매' 테이블의 '판매지역' 컬럼의 값이 프로그램의 버그로 인해서 가끔 공백이나 NULL 값이 들어가는 경우가 있으므로, 이런 데이터는 지역명을 무시하고 '판매' 테이블의 데이터만 보여주면 된다."
'판매지역' 컬럼의 값이 '지역' 테이블에 존재하지 않더라도 '판매' 테이블의 데이터를 보여줘야 하므로 당연히 외부(Outer) 조인을 사용해야 한다. 물론, 스칼라 서브쿼리를 사용해도 외부 조인과 동일한 결과를 얻을 수 있으며 내부적으로 Outer Join 방식으로 실행된다.
명시적인 외부 조인 대신 스칼라 서브쿼리를 사용하여 SQL을 작성해 보자. 필자가 작성한 SQL과 실행 계획은 [그림 1]과 같다.



[그림 1]의 실행 계획을 살펴보자. 밑에서 3번째 줄을 보면 Nested Loops(Left Outer Join, ...)로 기술된 부분이 있다. 괄호 안의 Left Outer Join 연산자는 이 SQL이 예상한대로 외부 조인 방식으로 수행되었음을 알려준다.
그런데, 실행 계획을 보면 뭔가 아쉬운 부분이 있다. Outer 측의 '판매' 테이블에서 추출된 2000건의 데이터에 대해서 Inner 측의 '지역' 테이블이 에누리없이 2000번 액세스 되었다. 지난 회에 소개한 스칼라 서브쿼리에서의 '멀티 캐시의 효과'는 어디로 사라진 것일까?
바로 이 부분에서 Top 1 키워드가 옵티마이저의 실행 계획에 큰 영향을 미칠 수 있음을 소개하고자 한다. 스칼라 서브쿼리의 결과 집합은 항상 1건이어야 하므로 서브쿼리의 SQL에 'Top 1'을 추가해도 논리적으로 [그림 1]의 SQL과 동일하다. 수정 후 실행 결과는 [그림 2]와 같다.



[그림 2]의 실행 계획을 보면 '지역' 테이블의 스캔 수 및 논리적 읽기 수가 [그림 1]의 실행 계획에 비해서 100분의 1 이하로 줄어들었으며 실행 계획 자체도 달라졌음을 알 수 있다. [그림 2] 실행계획의 밑에서 4번째 줄에 '멀티 캐시의 효과'가 있었음을 나타내는 Hash Match(Cache...) 연산자가 추가되었다.
이상의 테스트를 통해서 Top N 쿼리가 옵티마이저의 실행 계획에 큰 영향을 미칠 수 있음을 확인하였다. 이번에는 Top N 쿼리를 OR 연산자와 함께 사용할 경우 옵티마이저가 얼마나 효율적인 실행 계획을 만드는지 알아보자. 참고로, OR 연산자는 대상 집합을 줄이는 것이 아니라 더 확장시키므로 옵티마이저가 최적의 실행 계획을 세우지 못하면 예상치 못한 비효율이 발생할 수 있다.



[리스트 2]의 DDL을 살펴보자. 공지 사항이나 뉴스 등을 게시하는 간단한 형태의 게시판이다. 이 경우 각 게시판 별로 가장 최근에 작성된 글 순서대로 20 건씩 조회하고자 하는 경우 어떻게 SQL을 작성해야 할까? 단, 화면에는 이전(◀) 및 다음(▶) 버튼만 존재한다.
다양한 아이디어가 있겠지만 필자는 [리스트 3]과 같은 형태로 작성하였다. [리스트 3]의 SQL에서 사용된 4개의 변수 값은 실제로는 클라이언트 프로그램에서 넘겨주는 값이다. 지면 관계상 이전(◀) 버튼을 클릭하는 경우는 생략하고, 다음(▶) 버튼을 클릭하는 경우에 대해서만 설명하도록 하겠다.



[리스트 3]의 SQL을 보면 'TOP 21' 구문을 사용하여 데이터를 21건씩 조회하고 있다. 이것은 21번째의 데이터가 존재하지 않으면 다음 페이지의 데이터가 없다는 의미이므로 다음(▶) 버튼을 비활성화하기 위함이다. 또한, 가장 최근에 작성된 글 순서대로 조회해야 하므로 'ORDER BY 작성일자 DESC, 글번호 DESC' 구문을 사용하였다.
이 SQL의 실행 계획은 [그림 3]과 같다.




[그림 3]의 실행 계획을 보면 '게시판' 테이블의 스캔 수가 2이다. 이것은 WHERE 조건의OR 연산자에 의해서 내부적으로 두 개의 조건이 분리되어 실행되었음을 의미한다. 즉, (작성일자 < @작성일자) 조건과 (작성일자 = @작성일자 AND 글번호 <= @글번호) 조건이 따로 분리되어 실행된 것이다.
[리스트 3]의 SQL을 논리적으로 풀어서 재작성하면 [리스트 4]의 SQL과 논리적으로 동일하다.

[리스트 4]의 SQL을 보면 약간 의아한 생각이 들 수도 있다. 인라인 뷰(파생 테이블)를 살펴 보면 서로 배타적인 조건으로 21 건씩을 추출한 후 UNION ALL을 통해서 두 결과 집합을 결합시킨다. 잘못하면 불필요하게 42 건의 중간 집합을 만든 후 절반을 버리는 비효율이 발생하지 않을까?
이 질문에 대한 대답은 [그림 4]의 실행 계획에 나와 있다.



[그림 4]의 실행 계획을 살펴보자. 우려했던 것처럼 불필요한 데이터를 읽어 들이는 비효율은 발생하지 않았다. 실행 계획의 Rows를 보면 UNION ALL의 상위에서 10건이 추출되고 하위에서 11 건이 추출되었음을 알 수 있다. 기특하게도 옵티마이저는 UNION ALL의 위쪽 SQL에서 추출한 건수를 제외한 나머지 개수의 데이터만을 아래쪽 SQL에서 추출하였다. 즉, Top N 쿼리는 STOP KEY의 역할까지 훌륭하게 수행한 것이다.
이러한 Top N 쿼리의 기능을 잘 활용하면 웹 게시판을 효율적으로 구현할 수 있다. 이전 및 다음 버튼만 존재하는 간단한 게시판에서부터 응답글이 존재하는 복잡한 게시판에 이르기까지 다양한 형태의 응용이 가능하다.
지금까지 두 개의 예제를 통해서 Top N 쿼리의 숨겨진 유용한 기능에 대해서 알아보았다.
첫 번째는 스칼라 서브쿼리에 'Top 1' 구문을 추가하여 실행 계획을 변경하는 방법에 대해서 설명했다. 'Top 1' 구문을 잘 활용하면 '멀티 캐시의 효과'를 볼 수 있음을 기억하기 바란다.
두 번째는 Top N 쿼리가 STOP KEY의 기능을 포함하고 있음을 예제를 통해 설명했다. 이 기능은 효율적인 웹 게시판의 구현을 위한 필수 요소이므로 정확히 이해하기 바란다.
다음 회에는 Top N 쿼리의 강력한 기능을 활용하여 웹 게시판의 어느 페이지로 이동하더라도 동일한 조회 속도를 보장받을 수 있으며 불필요한 데이터는 전혀 액세스하지 않는 효율적인 웹 게시판 솔루션에 대해서 설명하도록 하겠다.

출처 : 엔코아 컨설팅

MySQL을 Microsoft SQL Server 2000으로 마이그레이션

이 문서는 몇 가지 내장된 SQL Server의 툴과 유틸리티를 통해 MySQL을 Microsoft SQL Server 2000으로 마이그레이션 하는 방법을 설명합니다. 이 문서는 또한 MySQL 어플리케이션이 SQL Server 2000에서 동작하도록 수정하는 방법에 대한 지침도 제공합니다. 기존에 MySQL 기반의 어플리케이션을 가지고 있다면, 기존의 투자를 보전하면서도 어플리케이션 아키텍처에 SQL Server 2000의 향상된 기능을 추가할 수 있습니다.

개요

MySQL은 오픈 소스 데이터베이스 관리 시스템(DBMS)으로써, 클라이언트/서버 아키텍처를 사용하고 멀티 스레드 및 복수 사용자를 지원하는 데이터베이스 서버입니다. MySQL은 빠른 속도를 염두에 두고 설계되었기 때문에, 관계형 데이터베이스 시스템들이 제공하는 많은 기능들, 즉 하부 질의 (sub-query), 외부 키 (foreign key), 참조 무결성, 저장 프로시저, 트리거, 및 뷰 같은 기능을 지원하지 않습니다. 또한, MySQL의 잠금 기법은 여러 사용자에 의해 동시에 많은 쓰기 동작이 일어나는 테이블에 적합하지 않으며, 소프트웨어 어플리케이션과 툴의 개발을 지원할 참고 문헌도 부족합니다.
SQL Server 2000은 완벽한 관계형 데이터베이스 관리 시스템(RDBMS)으로써, OLAP 및 데이터 마이닝을 위한 통합 분석 기능도 포함하고 있습니다. SQL Server 2000은 대형 규모의 데이터 처리 시스템과 상용 웹 사이트에 필요한 데이터 및 분석용 저장소로써의 요구사항을 만족시키는 동시에, 개인이나 소규모 기업을 위한 사용하기 쉬운 데이터 저장소로써의 요구사항도 만족시킵니다.
Microsoft SQL Server의 아키텍처는 낮은 수준의 잠금, 향상된 질의 최적화, 데이터 복제, 분산 데이터베이스 관리, 및 분석 서비스와 같이 향상된 서버 기능을 지원합니다. Transact-SQL (T-SQL)은 SQL Server 2000이 지원하는 SQL 언어의 변형입니다.
여기에서 소개되는 아키텍처 상의 기능은 SQL Server 2000에 의해 제공되는 기능의 일부에 불과합니다. 서버의 설치 과정에서 함께 설치되는 SQL Server 2000 온라인 설명서는 훌륭한 리소스입니다. 온라인 설명서를 사용하려면, Microsoft SQL Server 프로그램을 열고 온라인 설명서를 클릭합니다.

마이그레이션 절차

이 장에서는 MySQL 및 Microsoft SQL Server 2000의 아키텍처에 대한 설명과 함께 마이그레이션 절차를 설명합니다. 다음과 같은 내용이 포함됩니다:

ㆍ마이그레이션 준비
ㆍ데이터 형식, 예약된 키워드, 및 연산자
ㆍ데이터 마이그레이션에 사용되는 MySQL 툴
ㆍ마이그레이션을 위한 SQL Server 툴
ㆍ직접 마이그레이션: 데이터 변환 서비스(DTS)
ㆍ데이터 로드 기능의 사용: 질의 분석기
ㆍ어플리케이션의 확장
ㆍ문제 해결

마이그레이션 준비

적절한 마이그레이션 계획은 성공적인 결과를 위해 매우 중요합니다. 마이그레이션을 시작하기 전에 마이그레이션 대상 MySQL 데이터베이스의 스키마를 검토하십시오. MySQL의 데이터 형식과 SQL Server 2000의 데이터 형식을 비교하고 차이점을 기록합니다. 이 백서의 "MySQL과 Microsoft SQL Server의 비교" 부분에서는 비교 가능한 데이터 형식을 설명합니다. 일부 MySQL 데이터베이스 객체는 SQL Server 2000의 예약된 키워드와 충돌한다는 사실을 명심하십시오. 이러한 키워드 또한 다음 장에서 설명됩니다. 사용하고 있는 MySQL 데이터베이스 파일은 DTS를 사용하여 SQL Server 2000으로 마이그레이션 하기 전에 백업해야 합니다.

데이터 형식, 예약된 키워드, 및 연산자

이 장에서는 SQL Server 2000에서의 데이터 형식에 대하여 설명하고, 마이그레이션을 쉽게 할 수 있도록 MySQL 데이터 형식과 SQL Server 2000 데이터 형식을 비교하는 표도 제공됩니다. 이 장에서는 또한 Microsoft SQL Server의 예약된 키워드 목록을 제공합니다. 이 장에 포함되는 정보는 다음과 같습니다:

ㆍ지원되는 SQL Server 데이터 형식
ㆍMySQL과 SQL Server 2000의 비교
ㆍSQL Server의 예약된 키워드

지원되는 SQL Server 데이터 형식
데이터 형식
설명

BIGINT

-2^63 (-9223372036854775808)에서 2^63-1 (9223372036854775807) 사이의 정수 데이터.


INT

-2^31 (-2,147,483,648)에서 2^31 - 1 (2,147,483,647)사이의 정수 데이터.


SMALLINT

2^15 (-32,768)에서 2^15 - 1 (32,767)사이의 정수 데이터.


TINYINT

0에서 255까지의 정수 데이터.


BIT

1이나 0 값을 가지는 정수 데이터.


DECIMAL

-10^38 +1에서 10^38 -1사이의 고정 정밀도 및 배율 숫자 데이터.


NUMERIC

기능적으로 decimal과 동일.


MONEY

통화 단위의 1/1000의 정확성을 가진 -2^63(-922,337,203,685,477.5808)에서 2^63 - 1(+922,337,203,685,477.5807) 사이의 통화 데이터 값.


SMALLMONEY

통화 단위의 1/1000의 정확성을 가진 -214,748.3648에서 +214,748.3647 사이의 통화 데이터 값.


FLOAT

-1.79E + 308에서 -2.23E - 308까지, 0과 2.23E + 308부터 1.79E + 308 사이의 부동 정밀도 숫자 데이터.


REAL

-3.40E + 38부터 -1.18E - 38까지, 0과 1.18E - 38부터 3.40E + 38-3 사이의 부동 정밀도 숫자 데이터.


DATETIME

1753년 1월 1일에서 9999년 12월 31일까지 1/300초 또는 3.33밀리 초의 정확성을 가진 날짜 및 시간 데이터.


SMALLDATETIME

1900년 1월 1일에서 2079년 6월 6일까지 1분의 정확성을 가진 날짜 및 시간 데이터.


CHAR

길이가 최대 8,000자이고 유니코드가 아닌 고정 길이 문자 데이터.


VARCHAR

길이가 최대 8,000자이고 유니코드가 아닌 가변 길이 문자 데이터.


TEXT

길이가 최대 2^31 - 1(2,147,483,647)자이고 유니코드가 아닌 가변 길이 데이터.


NCHAR

길이가 최대 4,000자인 고정 길이 유니코드 데이터.


NVARCHAR

길이가 최대 4,000자인 가변 길이 유니코드 데이터. sysname은 nvarchar(128)과 같은 기능의 시스템 제공 사용자 정의 데이터 형식으로서 데이터베이스 개체 이름을 참조할 때 사용됩니다.


NTEXT

길이가 최대 2^30 - 1(1,073,741,823)자인 가변 길이 유니코드 데이터.


BINARY

길이가 최대 8,000바이트인 고정 길이 이진 데이터.


VARBINARY

길이가 최대 8,000바이트인 가변 길이 이진 데이터.


IMAGE

길이가 최대 2^31 - 1(2,147,483,647)바이트인 가변 길이 이진 데이터.


CURSOR

커서에 대한 참조.


SQL_VARIANT

text, ntext, timestamp, sql_variant를 제외하고 SQL Server에서 제공하는 여러 가지 데이터 형식의 값을 저장하는 데이터 형식.


TABLE

나중에 처리할 수 있도록 결과 집합을 저장하는 특수 데이터 형식.


TIMESTAMP

행이 업데이트될 때마다 업데이트되는 데이터베이스 차원의 고유한 숫자.


UNIQUEIDENTIFIER

전역 고유 식별자 (GUID).




MySQL과 SQL Server 2000의 비교

다음 표는 MySQL과 SQL Server 2000 사이의 데이터 형식을 비교하여 보여줍니다. 일부 MySQL 데이터 형식에 대해서는 하나 이상의 SQL Server 데이터 형식이 사용될 수 있습니다. 이 표는 다음 사항에 대한 정보를 포함합니다:

숫자 형식
데이터 및 시간 형식
문자열 형식

참고
D: 부동 소수점 형식에 적용되며 소수점 이후의 자리 수를 표시합니다. 가능한 최대 값은 30이지만 M-2보다 클 수 없습니다.
L: 컬럼 값의 실제 길이
M: 최대 표시 크기. 합법적인 최대 표시 크기는 255입니다.

숫자 형식

MySQL
크기
SQL Server 2000

TINYINT

1 바이트

TINYINT


SMALLINT

2 바이트

SMALLINT


MEDIUMINT

3 바이트




INT

4 바이트

INT


INTEGER

4 바이트

INT


BIGINT

8 바이트

BIGINT


FLOAT(X<=24)

4 바이트

FLOAT(0)


FLOAT(25<=X<=53)

8 바이트

FLOAT(25)


DOUBLE

8 바이트

FLOAT(25)


DOUBLE PRECISION

8 바이트

FLOAT(53)


REAL

8 바이트

REAL


DECIMAL

M 바이트 (D+2, if M
DECIMAL


NUMERIC

M 바이트 (D+2, if M
NUMERIC




날짜 및 시간 형식

MySQL
크기
SQL Server 2000

DATE

3 바이트

SMALLDATETIME


DATETIME

8 바이트

DATETIME


TIMESTAMP

4 바이트

TIMESTAMP


TIME

3 바이트

SMALLDATETIME


YEAR

1 바이트

SMALLDATETIME




문자열 형식

MySQL
크기
SQL Server 2000

CHAR(m)

M 바이트, 1<=M<=255

CHAR


VARCHAR(m)

L+1 바이트 (L<=M이고 1<=M<=255인 경우)

VARCHAR


TINYBLOB

L + 1 바이트 (L<2^8인 경우)

BINARY


BLOB

L + 2 바이트 (L<2^16인 경우)

VARBINARY


TEXT

L + 2 바이트 (L<2^16인 경우)

TEXT


MEDIUMBLOB

L + 3 바이트 (L<2^24인 경우)

IMAGE


MEDIUMTEXT

L + 3 바이트 (L<2^24인 경우)

TEXT


LONGBLOB

L + 4 바이트 (L<2^32인 경우)

IMAGE


LONGTEXT

L + 4 바이트 (L<2^32인 경우)

TEXT


ENUM (VALUE1, VALUE2, …)

Enum 수에 따라 1 또는 2 바이트. Values (최대 값은 65535)

제공되는 데이터 형식은 없지만, CHECK 제약 조건*이 해당 기능을 제공.


SET (VALUE1, VALUE2, …)

인수의 수에 따라 최대 1, 2, 3, 4 또는 8 바이트




* Check 제약 조건은 열에 들어갈 수 있는 값을 제한하여 데이터 무결성을 보장합니다. 자세한 사항은 온라인 설명서의 "CHECK 제약 조건" 항목을 참조하십시오.

Microsoft SQL Server 2000의 예약된 키워드

ADD

EXCEPT

PERCENT


ALL

EXEC

PLAN


ALTER

EXECUTE

PRECISION


AND

EXISTS

PRIMARY


ANY

EXIT

PRINT


AS

FETCH

PROC


ASC

FILE

PROCEDURE


AUTHORIZATION

FILLFACTOR

PUBLIC


BACKUP

FOR

RAISERROR


BEGIN

FOREIGN

READ


BETWEEN

FREETEXT

READTEXT


BREAK

FREETEXTTABLE

RECONFIGURE


BROWSE

FROM

REFERENCES


BULK

FULL

REPLICATION


BY

FUNCTION

RESTORE


CASCADE

GOTO

RESTRICT


CASE

GRANT

RETURN


CHECK

GROUP

REVOKE


CHECKPOINT

HAVING

RIGHT


CLOSE

HOLDLOCK

ROLLBACK


CLUSTERED

IDENTITY

ROWCOUNT


COALESCE

IDENTITY_INSERT

ROWGUIDCOL


COLLATE

IDENTITYCOL

RULE


COLUMN

IF

SAVE


COMMIT

IN

SCHEMA


COMPUTE

INDEX

SELECT


CONSTRAINT

INNER

SESSION_USER


CONTAINS

INSERT

SET


CONTAINSTABLE

INTERSECT

SETUSER


CONTINUE

INTO

SHUTDOWN


CONVERT

IS

SOME


CREATE

JOIN

STATISTICS


CROSS

KEY

SYSTEM_USER


CURRENT

KILL

TABLE


CURRENT_DATE

LEFT

TEXTSIZE


CURRENT_TIME

LIKE

THEN


CURRENT_TIMESTAMP

LINENO

TO


CURRENT_USER

LOAD

TOP


CURSOR

NATIONAL

TRAN


DATABASE

NOCHECK

TRANSACTION


DBCC

NONCLUSTERED

TRIGGER


DEALLOCATE

NOT

TRUNCATE


DECLARE

NULL

TSEQUAL


DEFAULT

NULLIF

UNION


DELETE

OF

UNIQUE


DENY

OFF

UPDATE


DESC

OFFSETS

UPDATETEXT


DISK

ON

USE


DISTINCT

OPEN

USER


DISTRIBUTED

OPENDATASOURCE

VALUES


DOUBLE

OPENQUERY

VARYING


DROP

OPENROWSET

VIEW


DUMMY

OPENXML

WAITFOR


DUMP

OPTION

WHEN


ELSE

OR

WHERE


END

ORDER

WHILE


ERRLVL

OUTER

WITH


ESCAPE

OVER

WRITETEXT




데이터 마이그레이션에 사용되는 MySQL 툴

MySQL은 몇 가지 클라이언트 툴과 유틸리티를 제공하는데, 이 중에서 가장 많이 사용되는 것은 다음과 같습니다:

ㆍmysql - 데이터베이스에 대해 질의를 실행하고 결과를 볼 수 있도록 해주는 대화형 클라이언트

ㆍmysqldump -MySQL 데이터베이스 내의 스키마와 데이터를 추출하여 파일로 저장하는 툴

ㆍmysqlimport - 파일에서 스키마와 데이터를 읽어서 MySQL 데이터베이스로 로딩하는 툴

ㆍmysqladmin - 데이터베이스의 생성 및 삭제와 같은 관리 작업을 수행할 수 있도록 해주는 툴

ㆍmyODBC - ODBC 호환 어플리케이션이 MySQL에 연결할 수 있도록 ODBC 레벨 0 (레벨 1 및 레벨 2 기능과 함께) 드라이버를 제공하는 32비트 Open DataBase Connectivity 소프트웨어

마이그레이션을 위한 SQL Server 툴

SQL Server는 MySQL로부터의 마이그레이션을 쉽게 해주는 풍부한 툴과 유틸리티를 제공합니다. SQL Server 2000의 데이터 변환 서비스 (DTS: Data Transformation Services)는 서로 다른 여러 가지 원본에서 단일 또는 복수 대상으로 데이터를 추출, 변환, 및 통합 해주는 일련의 그래픽 툴 및 프로그램 가능한 객체의 집합입니다.

데이터 변환 서비스의 기능

Microsoft SQL Server 2000의 데이터 변환 서비스는 서로 다른 여러 원본에서 데이터를 마이그레이션 할 수 있는 몇 가지 방법을 제공합니다. DTS는 마법사를 통해 실행될 수도 있고 DTS 패키지 디자이너를 통해 구축될 수도 있습니다. DTS의 마법사는 직접적인 데이터 복사를 신속하게 수행하고, 패키지 디자이너는 개발자로 하여금 다양한 프로그램 언어를 통해 사용자 정의 변환 스크립트를 작성할 수 있도록 해줍니다. DTS 툴은 다음과 같은 기능을 제공합니다:

ㆍMySQL에서 SQL Server 2000으로 데이터를 마이그레이션
ㆍ마이그레이션 전에 데이터의 모양을 표시
ㆍ테이블과 데이터 형식 (텍스트나 날짜 같은)을 마이그레이션
ㆍMySQL 데이터베이스를 MySQL 테이블과 함께 마이그레이션
ㆍ마이그레이션 보고서를 생성 및 보기
ㆍ테이블과 기본 데이터 형식 매핑 규칙을 사용자 정의
ㆍSQL Server의 예약된 키워드 등에 대한 충돌 사항을 해결
ㆍSQL Server 스키마 모델 내의 객체를 삭제하거나 이름을 변경
ㆍ개별적인 테이블 데이터를 마이그레이션

데이터 변환 서비스 용어집

DTS를 설명하는 데는 다음과 같은 용어가 사용됩니다:
DTS 패키지는 DTS 디자이너를 통해 그래픽 방식으로, 또는 프로그래밍 방식으로 어셈블 될 수 있는 연결, DTS 작업, DTS 변환, 및 워크플로 제약을 잘 구성하여 모아놓은 것입니다.
DTS 작업은 패키지에서 한 단계로 실행되는 기능의 불연속 집합입니다. 각 작업은 데이터 이동 및 데이터 변환 과정의 일부 또는 실행될 작업으로 수행되는 작업 항목을 정의합니다.
DTS 변환은 데이터가 대상에 도착하기 전 한 데이터에 적용되는 하나 이상의 함수 또는 작업입니다.
DTS 패키지 워크플로는 데이터 변환 서비스 (DTS)의 단계 및 사전 제약이 DTS 패키지 내의 작업 항목의 순서를 정하도록 합니다. DTS 패키지 워크플로는 DTS 디자이너를 통해 그래픽 방식으로, 또는 프로그래밍 방식으로 설계할 수 있습니다.
메타 데이터는 DTS에 패키지 메타데이터 및 데이터 계보 정보를 메타데이터 서비스로 저장하는 기능과 이러한 유형의 정보를 연결하는 기능이 포함되어 있습니다. 패키지에서 참조된 데이터베이스의 카탈로그 메타데이터를 저장할 수 있고 데이터 마트 또는 데이터 웨어하우스의 데이터 특정 열 기록에 관한 계정 정보를 저장할 수 있습니다.

직접 마이그레이션

데이터를 MySQL에서 Microsoft SQL Server로 마이그레이션 하는 가장 직접적인 방법은 myODBC 지원을 설치하고 DTS 패키지를 생성하여 데이터베이스를 MySQL에서 가져와 Microsoft SQL Server에 생성하는 것입니다.
다음은 MySQL 데이터베이스의 마이그레이션을 위해 Microsoft SQL Server를 설정하는 방법입니다.

1. MyODBC 지원을 설치합니다. MyODBC는http://www.mysql.com/ 에서 구할 수 있습니다.

2. 설치가 진행되는 동안 다음과 같은 대화 상자가 나타납니다:



다음과 같은 정보를 사용하여 ODBC 설치 설정 사항을 입력합니다:
Windows DSN name:

test


Description:

This is a test database


MySQL Database:

test


Server:

seawolf.microsoft.com


User:

cgunn


Password:

my_password


Port:

3306


위와 같은 설정에서, Windows DSN 이름은 연결하는 컴퓨터 상에서 유일해야 하며, 서버 설정은 FQDN 이거나 (DNS에 의해, 또는 사용자가 직접 모종의 이름 해석을 제공해야 합니다), IP 주소여야 합니다.

3. 다음은 DTS 마법사를 실행합니다. Microsoft SQL Server 프로그램 그룹에서 데이터 가져오기 및 내보내기를 선택하면 다음과 같은 대화 상자가 나타납니다.



Next를 클릭하여 다음 단계로 이동합니다.

4. 이제 필요한 데이터 원본 정보를 입력하는데, 아래 그림에서와 같이 ODBC 데이터 원본은 MySQL, System DSN에는 test를 입력하고 사용자 이름과 암호를 입력한 후 Next를 클릭합니다.



5. 아래 대화 상자에서와 같이 대상에 연결하기 위한 세부 사항을 입력하고 Next를 클릭합니다.



6. Specify Table Copy or Query 대화 상자에서는 원본 (이 경우 MySQL)에서 데이터베이스 객체를 선택할 수 있습니다. Copy Table(s) and View(s) from the source database를 선택합니다. 다시 한번 얘기하지만 MySQL은 뷰 기능을 제공하지 않으므로 이 옵션을 선택해도 테이블 객체 만을 복사할 것입니다. Next를 클릭하여 다음 단계로 넘어갑니다.



7. Select Source Tables and View 대화 상자에서는 원본 테이블과 대상 테이블을 선택할 수 있습니다.



8. 아래 Column Mappings and Transformations 대화 상자에서처럼 데이터 변환을 위한 내용을 지정합니다.



위의 대화 상자에서는 원본 및 대상 데이터 형식이 일치하고 있으며 NULL을 허용하도록 선택되어 있습니다. 작업이 완료되면 OK를 클릭합니다.
다음에는 Save, Schedule, and Replicate Package 대화 상자가 나타납니다. 여기에서는 마이그레이션 작업을 바쁘지 않은 시간 대로 스케줄 하고 DTS 패키지를 다른 위치 및 다른 포맷으로 저장합니다.



9. Save DTS Package 대화 상자는 DTS 패키지에 대하여 두 가지 유형의 암호를 제공합니다. 소유자 암호는 패키지에 포함된 사용자/암호 정보를 보호하는 것이고, 사용자 암호는 실행을 제어하기 위한 것으로 DTS 패키지가 인가되지 않은 사람에 의해 실행되는 것을 방지합니다. Next를 클릭하여 다음 단계로 진행합니다.



10. 마지막으로 Completing the DTS Import/Export Wizard 대화 상자가 DTS 마법사에서 선택된 옵션들을 요약하여 보여줍니다.



Finish를 클릭하여 데이터 마이그레이션 절차를 시작합니다.

11. Executing Package 대화 상자가 각 작업의 실행 상태를 보여줍니다. 녹색 체크 표시는 작업이 성공적으로 완료된 것을 뜻합니다. 오류로 인해 작업이 실패하면 오류 대화 상자가 오류에 대한 정보를 보여줍니다.



이제 당신은 데이터를 MySQL에서 SQL Server 2000으로 성공적으로 마이그레이션 하였습니다.

데이터 로딩의 사용

MySQL Server와 함께 제공되는 클라이언트 프로그램인 mysqldump를 사용하면 MySQL 데이터베이스의 스키마와 데이터를 다양한 포맷으로 .sql/.txt 파일로 저장할 수 있습니다. DTS는 mysqldump의 결과 파일을 사용하여 대규모 테이블에 대하여 오프라인 데이터 로딩 기능을 제공할 수 있습니다. 데이터 로딩 절차에 대해서는 다음과 같은 내용이 설명됩니다:

ㆍmysqldump 데이터 추출 스크립트의 생성
ㆍ스크립트의 전송
ㆍ추출된 스크립트의 사용


mysqldump 데이터 추출 스크립트의 생성

MySQL은 백업 또는 데이터의 전송을 위해 데이터베이스 또는 일련의 데이터베이스를 덤프 해주는 유틸리티를 제공합니다.
mysqldump 유틸리티는 데이터베이스의 SQL 스크립트를 생성하는 기능을 제공합니다.
Mysqldump을 위한 최소한의 문법은 다음과 같습니다:

Shell> mysqldump [OPTIONS] database [tables]

mysqldump에서 사용할 수 있는 옵션에 대한 자세한 정보는 이 백서에서도 설명하지만 MySql의 참조 매뉴얼에서도 자세히 설명하고 있습니다.
Mysqldump을 사용하면 해당 데이터베이스의 SQL 스크립트를 얻을 수 있습니다.

스크립트의 전송

Mysqldump로 생성한 스크립트는 SQL Server로 전송될 수 있습니다. MySQL 호스트에서 SQL Server 2000 컴퓨터로 스크립트를 전송하기 위해서는 FTP와 같은 네트워크 어플리케이션을 사용할 수 있습니다.

추출된 스크립트를 SQL 쿼리 분석기에서 사용

생성된 스크립트는 이제 데이터베이스 객체를 생성하고 데이터를 입력하는데 사용될 수 있습니다. MySQL 스크립트를 데이터베이스 스키마를 구축하는데 있어 가장 좋은 방법은 SQL Server 2000에 포함되어 있는 SQL 쿼리 분석기를 사용하는 것입니다. You can run SQL 쿼리 분석기는 시작 메뉴에서 직접 실행시킬 수도 있고 SQL Server 엔터프라이즈 관리자에서 실행시킬 수도 있습니다. SQL 쿼리 분석기는 명령 프롬프트에서 isqlw 유틸리티를 통해서도 실행시킬 수 있습니다. 스크립트가 제대로 실행되기 위해서는 SQL dialect를 일부 변경하는 등의 약간의 추가 작업이 필요합니다. 또한, SQL 스크립트를 살펴보고 데이터 형식을 SQL Server와 호환되는 형식으로 변경해야 함을 기억하십시오. 아래 그림은 mysqldump를 통해 가져온 스크립트를 보여줍니다. 중요한 사항은 덤프 한 내용이 ASCII 스크립트 파일이라는 것입니다.



Microsoft SQL Server 2000의 SQL 쿼리 분석기는 다음과 같은 기능을 제공합니다:

ㆍ질의 및 다른 SQL 스크립트를 생성하고 이를 SQL Server 데이터베이스에 대하여 실행합니다
ㆍ사전에 정의된 스크립트를 이용하여 자주 사용되는 데이터베이스 객체를 신속하게 생성합니다
ㆍ기존의 데이터베이스 객체를 신속하게 복사합니다
ㆍ필요한 인수를 몰라도 저장 프로시저를 실행시킵니다
ㆍ저장 프로시저를 디버깅 합니다
ㆍ질의의 성능 문제를 디버깅 합니다
ㆍ데이터베이스 내의 객체를 찾거나, 보거나 또는 작업할 수 있습니다
ㆍ테이블에 행을 신속하게 삽입, 갱신, 또는 삭제합니다
ㆍ자주 사용되는 질의에 대하여 키보드 바로가기를 생성합니다
ㆍ자주 사용되는 명령을 Tools 메뉴에 추가합니다

어플리케이션의 확장

MySQL 어플리케이션의 데이터 관리 부분을 Microsoft SQL Server로 옮긴 후에는 SQL Server를 이용하여 데이터를 보호하고 Transact-SQL로 인코딩 한 모든 참조 무결성과 비즈니스 규칙을 보호할 수 있습니다.
ADO, OLE DB, 및 ODBC와 같은 데이터베이스 어플리케이션 프로그램 인터페이스(API)는 다양한 프로그램 언어를 사용하여 데이터베이스의 데이터를 처리할 수 있도록 해줍니다. 이러한 API는 Microsoft Visual C++, Microsoft Visual Basic, 또는 Microsoft Visual J++와 같은 개발 시스템에서 사용할 수 있습니다.
또한 어플리케이션의 규모가 커지는 경우에는 Microsoft SQL Server를 더 큰 컴퓨터로 옮기면 어플리케이션을 변경하지 않고 쉽게 확장할 수 있습니다. SQL Server는 하드웨어 구성을 자동으로 인식하여 메모리, I/O 및 프로세서를 최적으로 사용하도록 스스로 튜닝합니다.

인터넷을 통한 데이터 액세스

SQL Server는 어플리케이션을 웹 기반 인터페이스로 확장시키는 기능을 제공합니다. 이 기능은 언제 어디서나 어플리케이션을 액세스 할 수 있도록 해줍니다. SQL Server는 Microsoft Internet Information Services (IIS)와 통합되므로, IIS 웹 서버와 ActiveX Data Object (ADO) 및 Active Server Page (ASP)를 사용하면 SQL Server에 저장된 데이터에 대하여 빠르고 효율적인 사용자 인터페이스를 제공할 수 있습니다.
자세한 사항은 다음 웹 사이트를 참조하십시오: http://www.microsoft.com/korea/msdn/

보안

SQL Server 2000의 데이터베이스 보안은 강력하면서도 관리하기 쉽습니다. SQL Server와 MySQL 모두에 대해서, 두 단계의 보안을 생각하는 것이 중요합니다: 1) 서버에 대한 액세스 2) 개별 데이터베이스에 대한 액세스.
MySQL은 서버에 대한 액세스를 고유의 방식으로 보호하는데, 클라이언트의 경우에는 소스에 대한 액세스를 제한하는 방식으로, IP 주소나 FQDN에 따라, 또는 '%' 같은 와일드 카드를 사용합니다. SQL Server는 운영체제에 의해 관리되거나 SQL Server 마스터 데이터베이스 내에 저장되는 사용자 계정을 필요로 합니다.
SQL Server는 역할을 통해 그룹 액세스 기능을 제공하는데, 이 기능을 사용하면 그룹에 속한 사용자에 대해 공통적인 액세스를 설정할 수 있으므로 데이터베이스 관리가 쉬워집니다.
다음 단계는 Microsoft SQL Server에서 엔터프라이즈 관리자 툴을 통해 서버 및 데이터베이스에 대한 액세스를 관리하는 방법을 보여줍니다.

1. 엔터프라이즈 관리자를 열고, 보안 폴더로 이동한 후, 로그인 아이콘을 선택하고, 마우스를 오른쪽 클릭하여 새 로그인을 선택합니다.



2. SQL Server Login Properties 대화 상자가 나타나면 로그인 이름을 입력합니다. 로그인 이름은 MySQL에서의 사용자 이름과 유사합니다. SQL Server에서 검증되는 보안 수준을 제공하도록 SQL Server Authentication을 선택합니다.



3. Server Roles 탭에서 서버에 액세스 하는 권한을 지정합니다. 아래 그림에서 선택한 역할은 sysadmins (시스템 관리자)인데, MySQL의 root 권한에 해당합니다.



4. 다음 탭은 Database Access이다. 이 등록정보 페이지에서는 SQL Server 내에 물리적으로 존재하는 모든 데이터베이스에 대한 액세스를 제공합니다. 데이터베이스를 선택된 후에 데이터베이스 역할을 설정합니다. 기본적으로 모든 사용자는 public 역할을 속하지만, 권한을 할당할 때 이 역할도 명시적으로 할당되어야 합니다. 아래 그림에서 추가로 선택된 역할은 db_owner인데, 이 역할은 전체 SQL Server나 다른 데이터베이스에 대해서는 아니지만, 선택한 데이터베이스에 대해서는 전권을 가집니다.



5. OK를 클릭하면 암호를 물어봅니다.



새로운 로그인은 엔터프라이즈 관리자에서 볼 수 있습니다. 아래 그림에서는 'sa'라는 로그인 계정도 볼 수 있는데, 이 시스템 계정은 반드시 암호를 설정해야 합니다. 암호는 SQL Server의 설치 과정에서 지정해야 하며, 빈 암호를 할당하는 옵션이 있기는 하지만, 이 로그인에 대해서는 항상 암호가 할당되어야 합니다.



Microsoft SQL Server 로그인을 생성하는데 대한 자세한 사항은 SQL Server 온라인 설명서의 "보안 관리" 항목을 참조하십시오

데이터베이스 권한

SQL Sever 2000 역시 데이터 정의 언어 (DDL) 및 데이터 조작 언어 (DML)에 대한 액세스를 제한 함으로써 데이터베이스를 보호하는 기능을 제공하는데, 이를 위한 단계는 로그인을 생성하는 것과 유사합니다. SQL Server 데이터베이스에 대한 권한을 설정하는 것은 엔터프라이즈 관리자를 통해 쉽게 수행될 수 있습니다.

데이터 조작 언어 권한

1. 엔터프라이즈 관리자를 열고 데이터베이스 폴더에서 권한을 설정할 데이터베이스를 선택합니다. users 아이콘을 선택한 후 데이터베이스 사용자를 오른쪽 클릭하여 Properties를 선택합니다.



2. permissions 단추를 클릭합니다.



3. 권한 윈도우에서는 테이블, 뷰, 및 저장 프로시저와 같은 모든 데이터베이스 객체에 대하여 DML 문장을 설정할 수 있습니다. 권한이 선택된 후에는 OK를 클릭합니다.





데이터 정의 언어 권한

1.데이터베이스에 DDL 문에 대한 권한을 설정하려면, 해당 데이터베이스의 등록정보를 선택해야 합니다. 해당 데이터베이스를 오른쪽 클릭한 후 Properties를 선택합니다.



2. 다음에 등록정보 윈도우에서 permissions 탭을 선택합니다.



3. 적절한 권한을 선택한 후에 OK를 클릭합니다.

문제 해결

이 장에서는 다음과 같은 사항에 대한 문제 해결 방법 및 정보를 제공합니다:

ㆍ사용자 계정의 정의
ㆍMySQL 데이터의 덤프
ㆍ명령 행 옵션의 최적화

사용자 계정의 정의

MySQL 서버를 시스템에 설치하면 root 사용자 계정이 모든 DBA 권한을 갖도록 기본 설정됩니다. MySQL 서버에는 ODBC를 통해 root 사용자로 로그온 해야 합니다. (참고: 기본적으로 root 사용자는 로컬 호스트에서만 로그온 할 수 있도록 설정되므로, DTS 마법사를 통해 root 사용자가 다른 IP 또는 DNS 주소를 통해서도 로그온 할 수 있도록 설정해야 합니다.)

MySQL 데이터의 덤프

아래 표는 MySQL 데이터를 덤프하고 mysqldump 텍스트 파일에서 데이터베이스를 다시 생성하는데 사용되는 문법을 설명합니다.

명령
설명

mysqldump

MySQL 데이터베이스의 스키마 및 데이터를 파일로 추출할 수 있도록 해주는 툴.


mysql

MySQL를 로드 하여 명령을 수행할 수 있도록 해줍니다.


-u user name

root MySQL 사용자 이름. 이 사용자는 모든 DBA 권한을 가져야 합니다.


-ppassword

MySQL 데이터베이스 서버 root 사용자의 암호.


--opt

테이블 덤프 속도를 최적화 하고 다시 로딩될 빠르게 수행되도록 덤프 파일을 씁니다. 이 옵션은-add-drop-table, --add-locks, --all, --extended-insert, --quick 및 -lock-tables 옵션을 설정한다. -opt에 의해 설정되는 옵션에 대한 설명은 "명령 행 옵션의 최적화" 부분을 참고하십시오.


databasename

텍스트 파일로 덤프 하고자 하는 정보를 포함하고 있는 데이터베이스 이름.


<

UNIX 및 Windows NT/2000에서 입력을 리디렉트 하는데 사용하는 심볼.


filename.sql

MySQL을 포함하는 파일 이름.



MySQL 데이터를 덤프 하려면 다음 명령을 사용합니다:

#> mysqldump –u user name –ppassword –opt databasename < filename.sql

mysqldump로 생성된 텍스트 파일로부터 데이터베이스를 다시 생성하려면 다음 명령을 사용합니다:

#> mysql –u user name –ppassword databasename < filename.sql

명령 행 옵션의 최적화

-opt를 사용하면 mysqldump 명령의 옵션을 자동적으로 설정합니다. MySQL에서 데이터를 덤프 하는 것과 관련된 더 자세한 사항은 "MySQL 데이터의 덤프" 부분을 참조하십시오. 다음 표는 -opt 명령에 대한 설명입니다:

명령
설명

--add-drop-table

각각의 CREATE TABLE 문장 앞에 DROP TABLE If EXISTS 문장을 추가합니다.


--all

MySQL에서 사용하는 생성 옵션을 모두 포함합니다.


--extended-insert

복수 행을 삽입하는 문장을 작성합니다.


--quick

질의를 버퍼링 하지 않고 표준 출력으로 바로 덤프합니다. 이 옵션을 사용하는 중에 mysqldump를 중단시키면 서버를 대기 상태로 만들 수 있으므로 다른 클라이언트에 영향을 미칠 수 있습니다.


--lock-tables

모든 테이블을 읽기 전용으로 잠급니다




MySQL의 오류 메시지

이 장에서는 MySQL 데이터베이스를 SQL Server 2000으로 마이그레이션 하는 동안 접할 수 있는 오류 메시지에 대하여 설명합니다.

오류 메시지

데이터 마이그레이션을 위해 DTS를 사용할 때 다음과 같은 오류 메시지가 나타날 수 있습니다:

오류 메시지
해법

Cannot connect to MSQL Server .
Is there a MySQL server running on the system/port you are trying to connect to?

이 오류는 다음과 같은 이유 때문에 발생할 수 있습니다:
· 소스 포트는 기본적으로 3306으로 설정됩니다. 이 포트 번호는 MySQL이 통신하는 포트인데, 이 포트가 MySQL 상에 다르게 정의되어 있다면 MySQL ODBC 설정에서 포트 설정을 변경합니다.
· 사용자가 MySQL 서버를 액세스 하는데 적절한 권한을 가지고 있는지 확인합니다.
· 사용자 이름이 유효한지 확인합니다.


There is already an object named 'tablename' in the database

DTS 패키지를 실행하는 동안 테이블이 생성되었습니다. 패키지를 실행하는 동안 테이블이 삭제되거나 재생성 되도록 확인합니다.




결론

이 백서에서는 MySQL에서 Microsoft SQL Server 2000으로 데이터베이스 스키마와 데이터를 마이그레이션 하기 위한 기본적인 정보와 배경을 설명하였습니다. SQL Server 2000은 어플리케이션에 대하여 더 높은 수준의 신뢰성, 확장성, 및 기능을 제공합니다.

출처 : 한국마이크로소프트(주)

SQL Server 2000에서는 오라클과는 달리 가변길이(varchar) 문자열과 고정길이(char) 문자열의 비교시 공백에 의한 문제가 발생하지 않는다.

오라클의 경우 문자열 뒤의 공백에 따라서 다른 결과가 나올 수 있지만, SQL Server 2000은 문자열 뒤에 포함된 공백이 비교 결과에 영향을 미치지 않는다.

테스트를 통해서 SQL Server 2000과 오라클의 차이점을 확인해 보도록 하자.

[ 오라클 8.1.7 ]
create table tab1 ( 고정5 char(5), 가변5 varchar2(5), 고정10 char(10), 가변10 varchar2(10) ); insert into tab1 /*공백 없음*/ values('A','A','A','A'); insert into tab1 /*공백 1개*/ values('A ','A ','A ','A '); insert into tab1 /*공백 4개*/ values ('A ','A ','A ','A ');
위와 같이 데이터를 입력하고 실제 데이터를 조회해 보자.
select '[' || 고정5 || ']' as 고정5, '[' || 가변5 || ']' as 가변5,'[' || 고정10 || ']' as 고정10, '[' || 가변10 || ']' as 가변10 from tab1;
고정5 가변5 고정10 가변10
------- ------- ------------ ------------
[A ] [A] [A ] [A]

[A ] [A ] [A ] [A ]

[A ] [A ] [A ] [A ]

조회결과는 예상한 그대로이다. varchar2는 입력한 값(공백을 포함한) 만큼만 저장이 되고, char는 나머지 자리가 모두 공백으로 채워졌다.

이 상태에서 다음 SQL을 실행하면 어떤 결과가 나올까?

SQL #1)
select '[' || a.가변5 || ']' as 가변5, '[' || b.가변10 || ']' as 가변10 from tab1 a, tab1 b where a.가변5 = b.가변10;
가변5 가변10
------- ------------
[A] [A]

[A ] [A ]

[A ] [A ]

문자열 뒤의 공백을 포함한 모든 문자가 동일한 경우에만 결과가 출력된다. 그럼 다음 3개 SQL의 결과는 어떨까?

SQL #2)
select '[' || 고정5 || ']' as 고정5, '[' || 고정10 || ']' as 고정10 from tab1 where 고정5 = 고정10;
고정5 고정10
------- ------------
[A ] [A ]
[A ] [A ]
[A ] [A ]

SQL #3)
select '[' || 가변5 || ']' as 가변5, '[' || 고정5 || ']' as 고정5 from tab1 where 가변5 = 고정5;
가변5 고정5
------- -------
[A ] [A ]

SQL #4)
select '[' || 가변10 || ']' as 가변10, '[' || 고정10 || ']' as 고정10 from tab1 where 가변10 = 고정10;
선택된 레코드가 없습니다.

예상과 일치하는가? 가변길이 컬럼과 고정길이 컬럼이 비교되는 경우, 문자열 뒤의 공백까지 정확히 일치해야만 결과가 나타난다. 자세한 내용은 대용량 데이터베이스 솔루션 I권의 문자타입의 비교법칙에 관한 부분(309 ~ 313 페이지)을 참고하기 바란다.
SQL Server 2000에서는 어떤 결과가 나오는지 확인해 보자.

[ SQL Server 2000 ]
create table tab1 ( 고정5 char(5), 가변5 varchar (5), 고정10 char(10), 가변10 varchar (10) ) go insert into tab1 /*공백 없음*/ values('A','A','A','A') insert into tab1 /*공백 1개*/ values('A ','A ','A ','A ') insert into tab1 /*공백 4개*/ values ('A ','A ','A ','A ')
위와 같이 데이터를 입력하고 실제 데이터를 조회해 보자.
select '[' + 고정5 + ']' as 고정5, '[' + 가변5 + ']' as 가변5,'[' + 고정10 + ']' as 고정10, '[' + 가변10 + ']' as 가변10 from tab1
고정5 가변5 고정10 가변10
------- ------- ------------ ------------
[A ] [A] [A ] [A]
[A ] [A ] [A ] [A ]
[A ] [A ] [A ] [A ]

(3개 행 적용됨)

여기까지는 오라클과 동일하다. 그러면 다음 4개 SQL의 실행결과도 과연 동일할까?

SQL #1)
select '[' + a.가변5 + ']' as 가변5, '[' + b.가변10 + ']' as 가변10 from tab1 a, tab1 b where a.가변5 = b.가변10
가변5 가변10
------- ------------
[A] [A]
[A ] [A]
[A ] [A]
[A] [A ]
[A ] [A ]
[A ] [A ]
[A] [A ]
[A ] [A ]
[A ] [A ]

(9개 행 적용됨)

오라클과는 다른 결과이다. 문자열 뒤의 공백 개수와 상관없이 결과가 출력된다.

오라클 사용자가 SQL Server 2000이나 Sybase ASE를 사용할 경우에 착각하기 쉬운 부분이므로 주의해야 한다.

SQL #2)
select '[' + 고정5 + ']' as 고정5, '[' + 고정10 + ']' as 고정10 from tab1 where 고정5 = 고정10
고정5 고정10
------- ------------
[A ] [A ]
[A ] [A ]
[A ] [A ]

(3개 행 적용됨)

고정길이 컬럼의 경우 대용량 데이터베이스 솔루션 I권에서 설명한 문자타입의 비교법칙이 동일하게 적용된다.

SQL #3)
select '[' + 가변5 + ']' as 가변5, '[' + 고정5 + ']' as 고정5 from tab1 where 가변5 = 고정5;
가변5 고정5
------- -------
[A] [A ]
[A ] [A ]
[A ] [A ]

(3개 행 적용됨)

이 결과도 오라클과 다르다. 분명 문자열 뒤의 공백의 개수가 다르지만 같은 값으로 인식하고 있다.

SQL #4)
select '[' + 가변10 + ']' as 가변10, '[' + 고정10 + ']' as 고정10 from tab1 where 가변10 = 고정10
가변10 고정10
------------ ------------
[A] [A ]
[A ] [A ]
[A ] [A ]

(3개 행 적용됨)

이 결과도 오라클과 다르다. 오라클에서는 [선택된 레코드가 없습니다]라는 메시지가 나왔지만, SQL Server 2000에서는 문자열 뒤의 공백에 상관없이 동일한 값으로 처리하고 있다.

SQL Server 2000에서의 '문자타입의 비교법칙'은 대용량 데이터베이스 솔루션 I권에 소개된 내용과 크게 다르지 않다. 다만 가변길이 컬럼과 고정길이 컬럼의 비교시에만 약간 다른 방식으로 처리되고 있다.

이 내용은 또 다른 테스트를 통해서 확인했으며 다음 회에서 문자타입이 다른 경우의 처리속도 및 타입의 크기(size)에 따른 영향 등을 포함해서 더 자세히 소개하도록 하겠다.

출처 : 엔코아정보컨설팅