|
전편에 이은 파티션드 인덱스에 대한 내용을 기술해 보고자 합니다. 파티션드 테이블은 로그성 데이터같이 시간순서로 계속 쌓이면서 일정 기간이 지나면 버려지는 데이터에 대해서 적합한 솔루션 입니다. 대표적인 예가 전편에 소개한 슬라이딩 윈도우입니다.
그러나 성능을 높이기 위한 OLTP 에서 파티션드 테이블을 고려하기 위해서는 많은 고민이 필요할 것 같습니다.
아래 TEST 는 다음 버전에서 수행 되었습니다.
|
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation Developer Edition
on Windows NT 5.1 (Build 2600: Service Pack 2) |
1. 데이터 베이스가 있으면 삭제합니다.
IF EXISTS
(
SELECT *
FROM MASTER. DBO. SYSDATABASES
WHERE NAME = 'PDB'
)
BEGIN
DECLARE @KILL_LIST VARCHAR (MAX )
SET @KILL_LIST = ''
SELECT @KILL_LIST =
(
SELECT 'KILL ' + CAST (SPID AS VARCHAR (10))+ '; '
FROM MASTER. DBO. SYSPROCESSES
WHERE DBID = DB_ID ('PDB' )
FOR XML PATH ('' )
)
-- PRINT @KILL_LIST
EXEC (@KILL_LIST)
DROP DATABASE PDB
END
GO
|
2. 파일그룹을 생성합니다.
FG1-8 파티션드 테이블을 테스트 할 목적의 파일그룹이고, 나머지 두개는 일반 테이블을 만들어 테스트 할 목적입니다.
ALTER DATABASE PDB
ADD FILEGROUP FG1;
ALTER DATABASE PDB
ADD FILEGROUP FG2;
ALTER DATABASE PDB
ADD FILEGROUP FG3;
ALTER DATABASE PDB
ADD FILEGROUP FG4;
ALTER DATABASE PDB
ADD FILEGROUP FG5;
ALTER DATABASE PDB
ADD FILEGROUP FG6;
ALTER DATABASE PDB
ADD FILEGROUP FG7;
ALTER DATABASE PDB
ADD FILEGROUP FG8;
ALTER DATABASE PDB
ADD FILEGROUP FG_DATA;
ALTER DATABASE PDB
ADD FILEGROUP FG_INDEX;
|
3. 파일그룹에 파일을 할당 합니다.
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT1, FILENAME = 'C:\FG1\PDB_DAT1.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG1
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT2, FILENAME = 'C:\FG2\PDB_DAT2.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG2
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT3, FILENAME = 'C:\FG3\PDB_DAT3.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG3
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT4, FILENAME = 'C:\FG4\PDB_DAT4.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG4
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT5, FILENAME = 'C:\FG5\PDB_DAT5.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG5
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT6, FILENAME = 'C:\FG6\PDB_DAT6.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG6
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT7, FILENAME = 'C:\FG7\PDB_DAT7.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG7
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT8, FILENAME = 'C:\FG8\PDB_DAT8.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG8
ALTER DATABASE PDB ADD FILE (NAME = PDB_DATA, FILENAME = 'C:\FG8\PDB_DATA.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG_DATA
ALTER DATABASE PDB ADD FILE (NAME = PDB_INDEX, FILENAME = 'C:\FG8\PDB_INDEX.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG_INDEX
-- PDB 실제파일이어떻게구성되어있는지확인합니다 .
SELECT * FROM MASTER. DBO. SYSALTFILES WHERE DBID = DB_ID ('PDB' )
|
4. 파티션 하지 않을 테이블과 파티션 할 테이블을 만들고, 파티션 스위칭을 위해 같은 스키마 구조의 테이블을 추가로 생성 합니다.
-- 파티션하지않고성능테스트할테이블을 FG_DATA 에할당합니다 .
CREATE TABLE [dbo]. [tblx](
[productid] [int] NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineID] [int] NOT NULL,
[TransactionDate] [datetime] NOT NULL,
[TransactionType] [nchar](1) NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON FG_DATA
-- 파티션할테이블생성합니다 .
CREATE TABLE [dbo]. [tbly](
[productid] [int] NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineID] [int] NOT NULL,
[TransactionDate] [datetime] NOT NULL,
[TransactionType] [nchar](1) NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON PSMYSCHEME3([productid])
-- 파티션스위칭을위해같은구조의스키마를가지고있는테이블을만들어둡니다 .
CREATE TABLE [dbo]. [tblZ](
[productid] [int] NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineID] [int] NOT NULL,
[TransactionDate] [datetime] NOT NULL,
[TransactionType] [nchar](1) NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON PSMYSCHEME3([productid])
|
5. 파티션 펑션과 스키마를 만들어 줍니다.
-- 파티션펑션만들기
CREATE PARTITION FUNCTION MYRANGEPF3 (INT)
AS RANGE RIGHT FOR VALUES (0, 100, 200, 300, 400, 500, 600);
GO
CREATE PARTITION FUNCTION MYRANGEPF3_COPY (INT)
AS RANGE RIGHT FOR VALUES (0, 100, 200, 300, 400, 500, 600);
GO
-- 파티션스키마 만들기
CREATE PARTITION SCHEME PSMYSCHEME3
AS PARTITION MYRANGEPF3
TO (FG1, FG2, FG3, FG4, FG5, FG6, FG7, FG8)
GO
IF OBJECT_ID ('DBO.TBL_TEMP') IS NOT NULL
DROP TABLE TBL_TEMP
IF OBJECT_ID ('DBO.TBLX') IS NOT NULL
DROP TABLE TBLX
IF OBJECT_ID ('DBO.TBLY') IS NOT NULL
DROP TABLE TBLY
IF OBJECT_ID ('DBO.TBLZ') IS NOT NULL
DROP TABLE TBLZ
GO
|
6. 파티션 테스트를 위한 데이터를 카피해 옵니다.
SELECT A.* INTO PDB. DBO. TBL_TEMP
FROM
(
SELECT
CASE
WHEN PRODUCTID > 600 THEN PRODUCTID - 600
ELSE PRODUCTID END PRODUCTID
, REFERENCEORDERID
, REFERENCEORDERLINEID, TRANSACTIONDATE, TRANSACTIONTYPE
, QUANTITY, ACTUALCOST, MODIFIEDDATE
FROM
ADVENTUREWORKS . PRODUCTION. TRANSACTIONHISTORYARCHIVE
) A
GO
|
7. tblx 와 tbly 라는 테이블에 데이터를 삽입 합니다.
INSERT INTO TBLX
SELECT * FROM TBL_TEMP
INSERT INTO TBLY
SELECT * FROM TBL_TEMP
|
8. 위 절차를 수행한 후 데이터가 어떠한 파일 그룹에 위치해 있는지 확인 합니다.
DBCC SHOWFILESTATS
/*-----------------------------------------------------------------------
TBL_TEMP 는 파일그룹을 지정하지 않았으므로 PDB_PRIM 에 생성
TBLX 는 PDB_DATA 에
TBLY 는 지정한 파일 그룹에 나누어져서
1 1 160 93 PDB_PRIM C:\FG0\FG0_PRI.MDF
-- TBL_TEMP
3 2 80 1 PDB_DAT1 C:\FG1\PDB_DAT1.NDF --
4 3 80 2 PDB_DAT2 C:\FG2\PDB_DAT2.NDF |
5 4 80 33 PDB_DAT3 C:\FG3\PDB_DAT3.NDF |
6 5 80 22 PDB_DAT4 C:\FG4\PDB_DAT4.NDF |
7 6 80 15 PDB_DAT5 C:\FG5\PDB_DAT5.NDF |
8 7 80 4 PDB_DAT6 C:\FG6\PDB_DAT6.NDF |
9 8 80 8 PDB_DAT7 C:\FG7\PDB_DAT7.NDF |
10 9 80 1 PDB_DAT8 C:\FG8\PDB_DAT8.NDF --
-- TBLY, (TBLZ 영역정의 )
11 10 80 73 PDB_DATA C:\FG8\PDB_DATA.NDF
-- TBLX
12 11 80 1 PDB_INDEX C:\FG8\PDB_INDEX.NDF
------------------------------------------------------------------------*/
|
9. SYSPARTITION META 정보확인
SELECT OBJECT_NAME (OBJECT_ID ), *
FROM SYS.PARTITIONS
WHERE OBJECT_ID = OBJECT_ID ('tbly' )
ORDER BY PARTITION_NUMBER, INDEX_ID;
SELECT OBJECT_NAME (OBJECT_ID ), *
FROM SYS.PARTITIONS
WHERE OBJECT_ID = OBJECT_ID ('tblZ' )
ORDER BY PARTITION_NUMBER, INDEX_ID;
|
10. 인덱스를 만들어 봅니다.
그러나 실수 할 수 있으니 주의 하시길 바랍니다. SQL Server 에서 Clustered index 라는 것은 데이터 구조 입니다. 이러한 Clustered index 가 없는 데이터 구조는 HEAP 이라는 데이터 구조를 가지게 되고 [Fileid, Pageid, slotid] 로 데이터에 접근하게 됩니다. 그러므로 인덱스와 파일 구조를 분리하기 위해서 Clustered index 를 index 파일 그룹에 위치시킨다면 모든 데이터가 인덱스 파일 그룹으로 옮겨가게 되는 현상이 생기게 됩니다. 반드시 클러스터 인덱스는 데이터 파일 그룹에 형성 하시길 바랍니다.
이제 파티션 되지 않은 tblx 에 Clustered index 를 만들어 보도록 하겠습니다.
만들고 난 후 DBCC SHOWFILESTATS 명령어로 확인해 볼 수 있습니다 .
CREATE CLUSTERED INDEX CL_TBLX
ON TBLX(PRODUCTID, TRANSACTIONDATE)
ON FG_DATA
|
11. 파티션드 테이블에도 Clustered index 를 만들어 줍니다.
IF EXISTS (SELECT * FROM SYSINDEXES WHERE ID = OBJECT_ID ('DBO.TBLY') AND NAME = 'CL_TBLY' )
DROP INDEX TBLY. CL_TBLY
IF EXISTS (SELECT * FROM SYSINDEXES WHERE ID = OBJECT_ID ('DBO.TBLZ' ) AND NAME = 'CL_TBLZ' )
DROP INDEX TBLZ. CL_TBLZ
GO
-- 파티션 키로 분배되어 들어간 데이터 그대로 CLUSTERED INDEX 생성
CREATE CLUSTERED INDEX CL_TBLY
ON TBLY(PRODUCTID, TRANSACTIONDATE)
ON PSMYSCHEME3(PRODUCTID);
-- 동일인덱스 생성
CREATE CLUSTERED INDEX CL_TBLZ
ON TBLZ(PRODUCTID, TRANSACTIONDATE)
ON PSMYSCHEME3 (PRODUCTID);
GO
IF EXISTS (SELECT * FROM SYSINDEXES WHERE ID = OBJECT_ID ('DBO.TBLY') AND NAME = 'NC_TBLY_01')
DROP INDEX TBLY. NC_TBLY_01
GO
-- LOCAL NONCLUSTERED INDEX 생성
CREATE NONCLUSTERED INDEX NC_TBLY_01
ON TBLY(TRANSACTIONDATE)
GO
|
12. 잠깐 용어 정리를 하도록 하겠습니다. 아직까지 SQL Server 2005 에서 관련 용어를 정의 하지 않았으므로, 먼저 이러한 기술을 도입한 다른 DBMS의 용어를 그대로 차용해 이 강좌에서 설명하도록 하겠습니다.
* LOCAL INDEX : CØ 당 파티션에 해당 영역의 인덱스만 존재 하는 경우
* GLOBAL INDEX : 하나의 인데스 파일그룹이 모든 파티션을 통합한 인덱스를 가지는 경우
두 형태의 인덱스를 어떠한 상황에서 사용해야 하는가에 대해서 간단하게 설명하자면 LOCAL INDEX 의 경우 CLUSTERED INDEX 이든 NONCLUSTERED INDEX 이든 해당 파티션만의 억세스로 모든 억세스가 끝나는 경우이고 GLOBAL INDEX 의 경우는 찾고자 하는 데이터가 어떤 파티션에 있을지 모르는 상황에서의 인덱스를 정의 하시면 됩니다.그러나 LOCAL INDEX 와 GLOBAL INDEX 의 여부에 따라서 파티션 스위칭의 제한 사항이 있으므로 각자의 상황에 따라 적절한 인덱스를 사용하시면 됩니다.
13. 다음은 CLUSTERED INDEX 와 NONCLUSTERED INDEX 가 있는 상황에서의 물리적 상황을 조회하는 DMV 입니다.
SELECT
OBJECT_NAME (OBJECT_ID)
, INDEX_TYPE_DESC
, ALLOC_UNIT_TYPE_DESC -- , *
FROM
sys.dm_db_index_physical_stats
(db_id ( ), object_id ('tblY'), NULL, NULL, NULL);
GO
/* ---------------------------------------------------------------------------
tbly CLUSTERED INDEX IN_ROW_DATA
tbly CLUSTERED INDEX IN_ROW_DATA
tbly CLUSTERED INDEX IN_ROW_DATA
tbly CLUSTERED INDEX IN_ROW_DATA
tbly CLUSTERED INDEX IN_ROW_DATA
tbly CLUSTERED INDEX IN_ROW_DATA
tbly CLUSTERED INDEX IN_ROW_DATA
tbly CLUSTERED INDEX IN_ROW_DATA
tbly NONCLUSTERED INDEX IN_ROW_DATA
tbly NONCLUSTERED INDEX IN_ROW_DATA
tbly NONCLUSTERED INDEX IN_ROW_DATA
tbly NONCLUSTERED INDEX IN_ROW_DATA
tbly NONCLUSTERED INDEX IN_ROW_DATA
tbly NONCLUSTERED INDEX IN_ROW_DATA
tbly NONCLUSTERED INDEX IN_ROW_DATA
tbly NONCLUSTERED INDEX IN_ROW_DATA
LOCAL NONCLUSTERED INDEX 가 생성 되었음
--------------------------------------------------------------------------- */
|
위의 결과를 출력해보면 CLUSTERED INDEX 의 파티션 된 개수와 NONCLUSTERED INDEX 의 상황을 볼 수 있습니다. 실제로 (결과값에서) 파티션드 키를 조건절에 넣지 못하는 상황에서 NONCLUSTERED INDEX 로 조회하는 상황이 발생한다면, NONCLUSTERED INDEX 모든 파티션에 접근해야 한다는 겁니다. 다음 테스트를 통해서 확인해 보도록 하겠습니다.
14. TEST
1. 원본테이블에서 2 개의 파티션에만 데이터가 있도록 가공
DELETE Y
--SELECT *
FROM TBLY Y
WHERE
TRANSACTIONDATE = '20030708'
AND PRODUCTID <> 106
AND PRODUCTID <> 533
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM TBLY WHERE transactionDate = '20030708'
GO
SET SHOWPLAN_TEXT OFF
GO
|--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1006], [Uniq1002], [PDB].[dbo].[tbly].[productid], [PDB].[dbo].[tbly].[TransactionDate], [Expr1008]) PARTITION ID:([PtnIds1006]) WITH UNORDERED PREFETCH)
|--Compute Scalar(DEFINE:([PtnIds1006]=RangePartitionNew([PDB].[dbo].[tbly].[productid],(1),(0),(100),(200),(300),(400),(500),(600))))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1007]) PARTITION ID:([PtnIds1007]))
| |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8))))
| |--Index Seek(OBJECT:([PDB].[dbo].[tbly].[NC_TBLY_01]), SEEK:([PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000') ORDERED FORWARD PARTITION ID:([PtnIds1007]))
|--Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=[PDB].[dbo].[tbly].[productid] AND [PDB].[dbo].[tbly].[TransactionDate]=[PDB].[dbo].[tbly].[TransactionDate] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD PARTITION ID:([PtnIds1006]))
** Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8)))) 부분을주의깊게보길바랍니다 . 모든 파티션에 접근하게 됩니다 .
IF EXISTS (SELECT * FROM SYSINDEXES WHERE ID = OBJECT_ID ('DBO.TBLY') AND NAME = 'NC_TBLY_01' )
DROP INDEX TBLY. NC_TBLY_01
GO
CREATE NONCLUSTERED INDEX NC_TBLY_01
ON TBLY(TRANSACTIONDATE)
ON FG_INDEX
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM TBLY WHERE transactionDate = '20030708'
GO
SET SHOWPLAN_TEXT OFF
GO
/*-------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1006], [Uniq1002], [PDB].[dbo].[tbly].[productid], [PDB].[dbo].[tbly].[TransactionDate], [Expr1007]) PARTITION ID:([PtnIds1006]) WITH UNORDERED PREFETCH)
|--Compute Scalar(DEFINE:([PtnIds1006]=RangePartitionNew([PDB].[dbo].[tbly].[productid],(1),(0),(100),(200),(300),(400),(500),(600))))
| |--Index Seek(OBJECT :([PDB].[dbo].[tbly].[NC_TBLY_01]), SEEK:([PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000') ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=[PDB].[dbo].[tbly].[productid] AND [PDB].[dbo].[tbly].[TransactionDate]=[PDB].[dbo].[tbly].[TransactionDate] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD PARTITION ID:([PtnIds1006]))
** 이번에는 모든 파일그룹과 조인 하는 실행계획이 빠진 것을 확인 할 수 있습니다.
|
위 테스트는 파티션드 인덱스에서 매우 중요한 사항이므로 반드시 알고 있어야 할 사항입니다.
그렇다고 하나의 인덱스 파티션(GLOBAL INDEX)으로 만들 경우 파티션 스위칭시 하나의 인덱스 파일그룹으로 되어 있으면 해당하는 인덱스를 DISABLE 해두고 스위칭 후 다시 REBUILD 해야 합니다. REBUILD가 완료될 때 까지 해당하는 인덱스를 사용할 수 없다는 점을 알고 있어야 합니다.
15. TEST
ALTER TABLE TBLY SWITCH PARTITION 3 TO TBLZ PARTITION 3 ;
GO
--Msg 7733, Level 16, State 4, Line 1
--'ALTER TABLE SWITCH' statement failed. The table 'PDB.dbo.TBLY' is partitioned while index 'NC_TBLY_01' is not partitioned.
-- NC_TBLY_01 이파티셔닝되지않았으므로스위칭할수없다는에러메시지입니다 .
ALTER INDEX [NC_TBLY_01] ON [dbo]. [tbly] DISABLE
GO
-- 성공적으로실행완료
ALTER TABLE TBLY SWITCH PARTITION 3 TO TBLZ PARTITION 3 ;
GO
-- DISABLE 된 [NC_TBLY_01] 를다시리빌드함
ALTER INDEX [NC_TBLY_01] ON [DBO]. [TBLY] REBUILD
GO
-- NONCLUSTERED INDEX 를 LOCAL INDEX 로구성
IF EXISTS (SELECT * FROM SYSINDEXES WHERE ID = OBJECT_ID ('DBO.TBLY') AND NAME = 'NC_TBLY_01')
DROP INDEX TBLY. NC_TBLY_01
GO
CREATE NONCLUSTERED INDEX NC_TBLY_01
ON TBLY(TRANSACTIONDATE)
GO
ALTER TABLE TBLZ SWITCH PARTITION 3 TO TBLY PARTITION 3 ;
GO
--Msg 4947, Level 16, State 1, Line 1
--ALTER TABLE SWITCH statement failed. There is no identical index in source table 'PDB.dbo.TBLZ' for the index 'NC_TBLY_01' in target table 'PDB.dbo.TBLY' .
-- 소스파티션에없는인덱스구조가타겟에있으므로옮길수없다
CREATE NONCLUSTERED INDEX NC_TBLZ_01
ON TBLZ(TRANSACTIONDATE)
GO
-- 스위칭성공
ALTER TABLE TBLZ SWITCH PARTITION 3 TO TBLY PARTITION 3 ;
GO
-- 이제는모두 LOCAL INDEX 이므로자유롭게타겟이비어있으면스위칭됩니다 .
ALTER TABLE TBLY SWITCH PARTITION 3 TO TBLZ PARTITION 3 ;
GO
ALTER TABLE TBLZ SWITCH PARTITION 3 TO TBLY PARTITION 3 ;
GO
** 인덱스재구성
특정 파티션 테이블을 온라인으로 리빌드하는 것은 문법상 불가능 합니다 .
이게 되면 참 좋을 것 같은데 , 아쉽습니다 .
-- 단일파일그룹테이블온라인리인덱스
ALTER INDEX ALL ON DBO. TBLX
REBUILD WITH (ONLINE = ON);
-- 특정파티션인덱스리빌드
ALTER INDEX CL_TBLY ON DBO. TBLY
REBUILD Partition = 3;
-- 파티션테이블을가지는테이블의온라인리인덱스
-- Online index operations are available only in SQL Server 2005 Enterprise Edition.
-- 파티션된테이블의모든인덱스온라인리빌드
ALTER INDEX ALL ON DBO. TBLY
REBUILD WITH (ONLINE = ON);
-- 클러스터인덱스만리빌드 (UNIQUE 일때는클러스터인덱스만리빌딩됩니다 .)
-- UNIQUE 제약조건이빠지면 NONCLUSTERED INDEX 도클러스터인덱스가리빌드될때같이됩니다 .
ALTER INDEX CL_TBLY ON DBO. TBLY
REBUILD WITH (ONLINE = ON);
-- 특정 NONCLUSTERED INDEX REBUILD
ALTER INDEX NC_TBLY_01 ON DBO. TBLY
REBUILD WITH (ONLINE = ON);
-- 파티션테이블의특정파티션만리빌드하면서 CPU 동시성을 1 로제한
ALTER INDEX NC_TBLY_01 ON DBO. TBLY
REBUILD Partition = 3 WITH (MAXDOP = 1)
|
마지막으로 파티션드 테이블을 온라인 리빌딩 할 수 있었고, 데이터가 많아지면 많이 느려지지만 무중단 시스템에서 점검 시간없이 인덱스 리빌딩이 가능하다는 결론을 얻을 수 있습니다. 실제 서비스 시스템에서 이것을 적용 할 지 못할지는 해당 DBA 가 업무특성에 따라 잘 결정해야 합니다.
다음은 SQL MVP 이신 김연욱 선배님이 테스트 해주신 자료 입니다. 참고 하시길 바랍니다.
et statistics profile on
set statistics io on
GO
SELECT * FROM TBLX WHERE transactionDate = '20030708'
테이블 'tblx' . 검색수 1, 논리적읽기수 369
117 1 SELECT * FROM [TBLX] WHERE [transactionDate]= @1
117 1 | --Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [PDB].[dbo].[tblx].[productid], [PDB].[dbo].[tblx].[TransactionDate], [Expr1005]) WITH UNORDERED PREFETCH)
117 1 | --Index Seek(OBJECT:([PDB].[dbo].[tblx].[NC_TBLX_01]), SEEK:([PDB].[dbo].[tblx].[TransactionDate]='2003-07-08 00:00:00.000') ORDERED FORWARD)
117 117 | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tblx].[CL_TBLX]), SEEK:([PDB].[dbo].[tblx].[productid]=[PDB].[dbo].[tblx].[productid] AND [PDB].[dbo].[tblx].[TransactionDate]=[PDB].[dbo].[tblx].[TransactionDate] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)
SELECT * FROM TBLY WHERE transactionDate = '20030708'
테이블 'tbly' . 검색수 8, 논리적읽기수 289
117 1 SELECT * FROM [TBLY] WHERE [transactionDate]= @1
117 1 | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1006], [Uniq1002], [PDB].[dbo].[tbly].[productid], [PDB].[dbo].[tbly].[TransactionDate], [Expr1008]) PARTITION ID:([PtnIds1006]) WITH UNORDERED PREFETCH)
0 0 | --Compute Scalar(DEFINE:([PtnIds1006]=RangePartitionNew([PDB].[dbo].[tbly].[productid],(1),(0),(100),(200),(300),(400),(500),(600))))
117 1 | | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1007]) PARTITION ID:([PtnIds1007]))
8 1 | | --Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8))))
117 8 | | --Index Seek(OBJECT:([PDB].[dbo].[tbly].[NC_TBLY_01]), SEEK:([PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000') ORDERED FORWARD PARTITION ID:([PtnIds1007]))
117 117 | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=[PDB].[dbo].[tbly].[productid] AND [PDB].[dbo].[tbly].[TransactionDate]=[PDB].[dbo].[tbly].[TransactionDate] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD PARTITION ID:([PtnIds1006]))
SELECT count (*) FROM TBLX WHERE transactionDate = '20030708'
1 1 SELECT COUNT (*) FROM [TBLX] WHERE [transactionDate]= @1
0 0 | --Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1006],0)))
1 1 | --Stream Aggregate(DEFINE:([Expr1006]=Count(*)))
117 1 | --Index Seek(OBJECT:([PDB].[dbo].[tblx].[NC_TBLX_01]), SEEK:([PDB].[dbo].[tblx].[TransactionDate]=CONVERT_IMPLICIT(datetime,[@1],0)) ORDERED FORWARD)
테이블 'tblx' . 검색수 1, 논리적읽기수 2
SELECT count (*) FROM TBLY WHERE transactionDate = '20030708'
1 1 SELECT COUNT (*) FROM [TBLY] WHERE [transactionDate]= @1
0 0 | --Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))
1 1 | --Stream Aggregate(DEFINE:([Expr1010]=Count(*)))
117 1 | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1009]) PARTITION ID:([PtnIds1009]))
8 1 | --Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8))))
117 8 | --Index Seek(OBJECT:([PDB].[dbo].[tbly].[NC_TBLY_01]), SEEK:([PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000') ORDERED FORWARD PARTITION ID:([PtnIds1009]))
테이블 'tbly' . 검색수 8, 논리적읽기수 12
SELECT * FROM TBLX WHERE productid = '111'
테이블 'tblx' . 검색수 1, 논리적읽기수 11
870 1 SELECT * FROM [TBLX] WHERE [productid]= @1
870 1 | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tblx].[CL_TBLX]), SEEK:([PDB].[dbo].[tblx].[productid]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
SELECT * FROM TBLY WHERE productid = '111'
테이블 'tbly' . 검색수 1, 논리적읽기수 10
870 1 SELECT * FROM [TBLY] WHERE [productid]= @1
870 1 | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=(111)) ORDERED FORWARD PARTITION ID:((3)))
SELECT * FROM TBLX WHERE productid = '111' and transactionDate = '20030708'
4 1 SELECT * FROM [TBLX] WHERE [productid]= @1 AND [transactionDate]= @2
4 1 | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tblx].[CL_TBLX]), SEEK:([PDB].[dbo].[tblx].[productid]=CONVERT_IMPLICIT(int,[@1],0) AND [PDB].[dbo].[tblx].[TransactionDate]=CONVERT_IMPLICIT(datetime,[@2],0)) ORDERED FORWARD)
테이블 'tblx' . 검색수 1, 논리적읽기수 3
SELECT * FROM TBLY WHERE productid = '111' and transactionDate = '20030708'
4 1 SELECT * FROM [TBLY] WHERE [productid]= @1 AND [transactionDate]= @2
4 1 | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=(111) AND [PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000') ORDERED FORWARD PARTITION ID:((3)))
테이블 'tbly' . 검색수 1, 논리적읽기수 3
SELECT count (*) FROM TBLX WHERE productid in ('2' , '111' ) and transactionDate = '20030708'
1 1 SELECT count (*) FROM TBLX WHERE productid in ('2' , '111' ) and transactionDate = '20030708'
0 0 | --Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
1 1 | --Stream Aggregate(DEFINE:([Expr1007]=Count(*)))
4 1 | --Index Seek(OBJECT:([PDB].[dbo].[tblx].[NC_TBLX_01]), SEEK:([PDB].[dbo].[tblx].[TransactionDate]='2003-07-08 00:00:00.000' AND [PDB].[dbo].[tblx].[productid]=(2) OR [PDB].[dbo].[tblx].[TransactionDate]='2003-07-08 00:00:00.000' AND [PDB].[dbo].[tblx].[productid]=(111)) ORDERED FORWARD)
테이블 'tblx' . 검색수 2, 논리적읽기수 4
SELECT count (*) FROM TBLY WHERE productid in ('2' , '111' ) and transactionDate = '20030708'
1 1 SELECT count (*) FROM TBLY WHERE productid in ('2' , '111' ) and transactionDate = '20030708'
0 0 | --Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1012],0)))
1 1 | --Stream Aggregate(DEFINE:([Expr1012]=Count(*)))
4 1 | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1005]) PARTITION ID:([PtnIds1005]))
2 1 | --Constant Scan(VALUES:(((2)),((3))))
4 2 | --Index Seek(OBJECT:([PDB].[dbo].[tbly].[NC_TBLY_01]), SEEK:([PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000' AND [PDB].[dbo].[tbly].[productid]=(2) OR [PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000' AND [PDB].[dbo].[tbly].[productid]=(111)) ORDERED FORWARD PARTITION ID:([PtnIds1005]))
테이블 'tbly' . 검색수 4, 논리적읽기수 8
SELECT * FROM TBLX WHERE productid IN ('2' , '111' )
테이블 'tblx' . 검색수 2, 논리적읽기수 15
875 1 SELECT * FROM TBLX WHERE productid IN ('2' , '111' )
875 1 | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tblx].[CL_TBLX]), SEEK:([PDB].[dbo].[tblx].[productid]=(2) OR [PDB].[dbo].[tblx].[productid]=(111)) ORDERED FORWARD)
SELECT * FROM TBLY WHERE productid IN ('2' , '111' )
테이블 'tbly' . 검색수 4, 논리적읽기수 20
875 1 SELECT * FROM TBLY WHERE productid IN ('2' , '111' )
875 1 | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1004]) PARTITION ID:([PtnIds1004]))
2 1 | --Constant Scan(VALUES:(((2)),((3))))
875 2 | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=(2) OR [PDB].[dbo].[tbly].[productid]=(111)) ORDERED FORWARD PARTITION ID:([PtnIds1004]))
SELECT productid, count (*) FROM TBLX WHERE productid IN ('2' , '111' ) group by productid
2 1 SELECT productid, count (*) FROM TBLX WHERE productid IN ('2' , '111' ) group by productid
0 0 | --Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
2 1 | --Stream Aggregate(GROUP BY:([PDB].[dbo].[tblx].[productid]) DEFINE:([Expr1007]=Count(*)))
875 1 | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tblx].[CL_TBLX]), SEEK:([PDB].[dbo].[tblx].[productid]=(2) OR [PDB].[dbo].[tblx].[productid]=(111)) ORDERED FORWARD)
테이블 'tblx' . 검색수 2, 논리적읽기수 15
SELECT productid, count (*) FROM TBLY WHERE productid IN ('2' , '111' ) group by productid
2 1 SELECT productid, count (*) FROM TBLY WHERE productid IN ('2' , '111' ) group by productid
0 0 | --Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1013],0)))
2 1 | --Stream Aggregate(GROUP BY:([PDB].[dbo].[tbly].[productid]) DEFINE:([Expr1013]=Count(*)))
875 1 | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1005]) PARTITION ID:([PtnIds1005]))
2 1 | --Constant Scan(VALUES:(((2)),((3))))
875 2 | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=(2) OR [PDB].[dbo].[tbly].[productid]=(111)) ORDERED FORWARD PARTITION ID:([PtnIds1005]))
테이블 'tbly' . 검색수 4, 논리적읽기수 20
SELECT * FROM TBLX WHERE productid BETWEEN '90' AND '111'
2875 1 SELECT * FROM [TBLX] WHERE [productid]>= @1 AND [productid]<= @2
2875 1 | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tblx].[CL_TBLX]), SEEK:([PDB].[dbo].[tblx].[productid] >= CONVERT_IMPLICIT(int,[@1],0) AND [PDB].[dbo].[tblx].[productid] <= CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)
테이블 'tblx' . 검색수 1, 논리적읽기수 25
SELECT * FROM TBLY WHERE productid BETWEEN '90' AND '111'
2875 1 SELECT * FROM [TBLY] WHERE [productid]>= @1 AND [productid]<= @2
2875 1 | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1006]) PARTITION ID:([PtnIds1006]))
2 1 | --Constant Scan(VALUES:(((2)),((3))))
2875 2 | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid] >= (90) AND [PDB].[dbo].[tbly].[productid] <= (111)) ORDERED FORWARD PARTITION ID:([PtnIds1006]))
테이블 'tbly' . 검색수 2, 논리적읽기수 27
| 감사합니다. 이상으로 SQL 2005의 파티션드 테이블 인덱스 특성에 대해서 조금이나마 이해 할 수 있는 자료가 되었으면 합니다. 언제나 글을 적고 나면 저 당시 왜그리 지식의 수준이 그것밖에 되지 않았을까 부끄러울 때가 많습니다. 서로 돕고 마음의 문을 열고 지식을 공유할 때 그 지식이 아름다운 꽃이 되지 않을까 생각합니다. 윗 글에 대한 의문사항이나 질문 있으시면 언제나 연락 주시길 바랍니다. SQLER.PE.KR 에서 또 뵙겠습니다.
* 도움주신분들
- NHN ORACLE 엔지니어
- 김연욱 (SQL MVP)
- 송혁 (SQL MVP)
- 김경연 (http://www.enuri.com/) DBA
* 참고도서
- EFFECTIVE ORACLE
- SQL2005 BOOKS ONLINE
|
