[통신] SQL 서버 파티션 테이블 요약

공식 문서

파티션 테이블을 생성하는 고급 단계

1. 파일 그룹을 생성하세요(Oracle 테이블스페이스와 유사합니다). 물론 생성할 수는 없고, 모든 파티션을 하나의 파일 그룹에 넣을 수 있습니다

2. 파티션 함수를 설정하고 데이터를 범위에 따라 분배합니다

3. 파티션 체계를 설정하고, 파티션 함수를 연관시키며, 파일 그룹도 연관시키세요. 파티션 함수는 데이터를 여러 범위로 나누며, 여러 파일 그룹을 연관해야 합니다. 물론 이 파티션 범위들을 같은 파일 그룹에 넣을 수도 있습니다

4. 표를 만들고 구역 계획을 연관 짓습니다

버그 발견

테이블 구조를 내보내기 위해 직접 마우스 오른쪽 버튼을 누르면 파티션 정보를 내보낼 수 없으며, 데이터베이스-task-generate 스크립트를 우클릭해야만 테이블의 파티션 정보를 내보낼 수 있습니다

분할표에서 얻은 몇 가지 결론:

1. 분할된 필드는 반드시 인덱싱될 필요가 없습니다

2. 분할된 필드는 클러스터 인덱스 또는 클러스터 없는 인덱스로 만들 수 있습니다

3. 클러스터 인덱스든 비클러스터 인덱스든 분할된 필드는 관련 파티션 방식 없이 클러스터 인덱스로 재구성되며, 파티션된 테이블은 비파티션 테이블이 됩니다

4. 일반 테이블을 파티션 테이블로 변환하세요. 테이블에 클러스터 인덱스를 만들고 클러스터 인덱스에 파티션 방식을 적용하세요. 예를 들어, 비분할 테이블의 필드 1이 클러스터 인덱스를 생성하고 파티션 방식과 연관되어 있을 때, 테이블은 파티션된 테이블로 변환되고, 파티션된 필드는 필드 1이 됩니다.

5. 파티션 필드가 인덱스가 있다면 파티션 필드를 클러스터 인덱스로 재구성하고 파티션 방식과 연관하지 않습니다. 파티션 필드에 인덱스가 없으면 파티션 필드가 새로운 클러스터 인덱스를 생성할 수 있고 파티션 방식과 연관되지 않습니다

6. 일반 테이블을 파티션 테이블로 변경하거나 파티션 테이블을 일반 테이블로 변경하는 것은 클러스터 인덱스를 사용해야만 가능한데, 클러스터 인덱스는 인덱스 조직 테이블을 의미하며, 테이블의 재분배는 클러스터 인덱스의 재구성을 통해 이루어집니다. 일반 테이블이 파티션 테이블이 되면, 파티션 필드를 클러스터 인덱스로 재구성하고 파티션 스킴과 연관시키고, 파티션 테이블이 일반 테이블이 되면 파티션 스킴을 연관하지 않고 클러스터 인덱스로 재구성합니다.

7. 분할 테이블을 생성하려면 유일성 제약 조건에 분할 열이 포함되어야 합니다

8. 파티션 방식을 만들 때는 파일 그룹 수가 파티션 함수의 파티션 범위 세그먼트와 일치하는지 확인해야 하며, 파일 그룹 이름이 중복되든 상관없습니다.

9. 분할 함수와 분할 방식은 전체 인스턴스를 향하지 않고 데이터베이스에 있습니다

10. 파티션 테이블이 너무 커서 많은 디스크 공간을 차지하지 못하고, 일부 필드를 삭제해도 크기는 변하지 않습니다

파티션 테이블 생성 단계

1.1. 파일 그룹 설정 예시

alter database test1 add filegroup part1;

Alter database test1 Add FileGroup part1000;

Alter database test1 Add FileGroup part2000;

alter database test1 Add FileGroup Part3000;

alter database test1 Add FileGroup part4000;

1.2. 파일 예시를 설정하고 파일 그룹을 연관

ALTER DATABASE test1 파일 추가(NAME = test1part1, FILENAME = 'G:\test1part1.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) 파일 그룹 PART1에 추가;

ALTER DATABASE test1 파일 그룹 PART1000에 파일(NAME = TEST1PART1000,FILENAME = 'G:\test1part1000.ndf',SIZE = 5MB, MAXSIZE = 100MB,FILEGROWTH = 5MB)을 PART1000에 추가합니다;

ALTER DATABASE TEST1 파일 추가(NAME = test1part2000,FILENAME = 'G:\test1part2000.ndf',SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) PART2000에 추가;

ALTER DATABASE TEST1 파일 그룹 PART3000에 파일 추가(NAME = test1part3000,FILENAME = 'G:\test1part3000.ndf',SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)를 PART3000에 추가합니다;

ALTER DATABASE TEST1 파일 ADD FILE(NAME = TEST1part4000,FILENAME = 'G:\test1part4000.ndf',SIZE = 5MB, MAXSIZE = 100MB,FILEGROWTH = 5MB) PART4000에 추가;

2. 분할 함수를 설정하는 예로, 분할 함수는 partfun1이라고 불립니다.

파티션 함수 생성 partfun1 (int)

값 왼쪽 범위('1000', '2000', '3000', '4000')

--값 ('1000', '2000', '3000', '4000')은 테이블이 5개의 구역으로 나뉘며, 테이블 필드의 값 크기에 따라 나뉘며, 다섯 구역이 가장 작습니다 - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-

3. 스킴을 설정하는 예로, 파티션 함수 partfun1을 연관시키고, 파일 그룹을 연관시킵니다

파티션 구성 구성 구성 partschema1

AS PARTITION PARTFUN1

TO (1부, 1000부, 2000부, 3000부, 4000부);

--part1, part1000, part2000, part3000, part4000의 여러 파일 그룹을 기반으로 구축됨

파티션 스킴 생성 partschema2

AS PARTITION PARTFUN1

TO(1부,[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);

--part1, [PRIMARY] 파일 그룹에 구축된 것으로, part1을 [PRIMARY]로 교체하는 데 문제가 없으므로 [PRIMARY] 파일 그룹을 빌드하는 것과 유사합니다

파티션 스킴 생성 partschema3

AS PARTITION PARTFUN1

올 투 (1부);

--모두 part1 파일 그룹에서 구축되었습니다

파티션 구성 만들기 partschema4

AS PARTITION PARTFUN1

모두 ([PRIMARY]);

--모두 [PRIMARY] 파일 그룹에 기반해 구축되었습니다

4. 분할 테이블 설정의 예시

CREATE TABLE PARTTABLE1(

[ID] [내성] 무효가 아니다,

[IDText] [은바르차르] (맥스) 영

[날짜] [데이트 타임] NULL)

ON [partschema1](ID);

parttable1에 값 (1,'1',getdate()-4)를 삽입합니다;

parttable1에 값 (1001,'1001',getdate()-3)을 삽입합니다;

parttable1에 값 삽입 (2001, '2001', getdate()-2);

parttable1에 값 (3001,'3001',getdate()-1)을 삽입합니다;

parttable1에 값 (4001,'4001',getdate())를 삽입합니다;

5. 파티션 테이블의 데이터 검증

SELECT * PARTTABLE1에서 선택;

--파티션 테이블의 모든 행을 반환합니다

특정 $PARTITION을 선택하세요. [partfun1] (4) 부분표 1에서;

--반환 항목은 ID 필드 값이 4인 행을 분할하여 다음에 속합니다.

$PARTITION 부분 테이블1에서 * 선택하세요. [partfun1] (ID)=2

--두 번째 파티션의 모든 행을 반환하며, ID는 파티션 필드 ID입니다.

참고: $PARTITION 있는 곳에서는 SELECT * FROM parttable1을 사용할 수 없습니다. [partfun1] (ID)=2는 결과가 나오며, 이는 파티션된 테이블임을 의미하며, 이 논문의 마지막 테스트 7은 파티션이 없는 테이블이지만, 여기서 $PARTITION SELECT * FROM parttable1입니다. [partfun1] (ID)=2 경우에도 결과가 나타납니다

새로운 분할

1. 파티션 스킴에 사용할 수 있는 파일 그룹을 지정한다(파티션 스킴을 위한 파일 그룹 추가).

2. 파티션 함수를 수정한다(파티션 함수의 데이터 범위를 추가함)

파티션 스킴 변경 PARTSCHEMA1 다음에 사용됨 [primary]

ALTER PARTITION FUNCTION PARTFUN1() 분할 범위 ('4500')

sys.indexes i 에서 p.partition_number,p.rows를 선택하고, p.object_id = i.object_id 와 i.object_id = object_id('parttable2') 순서를 1로 결합합니다

--첫 번째 명제, 분할 방식이 ALL TO([primary])를 사용한다면, 이 명제는 실행할 필요가 없습니다

--두 번째 문장에 새로운 파티션이 추가되며, 범위는 4000-4500입니다

--세 번째 명제는 새로운 분할이 존재하는지, 그리고 행이 있는지 검증합니다

파티션 삭제/병합

ALTER PARTITION FUNCTION PARTFUN1() MERGE RANGE ('2000')

1000-2000 파티션은 삭제되어 1000-3000으로 통합되었습니다

--oracle처럼 ALTER TABLE NAME TABLENAME DROP PARTITION PARTITIONNAME을 실행할 수 없음;

파티션 테이블과 해당 파일 그룹을 삭제하세요

삭제 순서는 다음과 같습니다: 파티션 테이블을 삭제하고, 파티션 방식을 삭제하고, 파티션 함수를 삭제한 후 파일 그룹을 삭제하며, 해당 파일 그룹도 삭제됩니다

파티션된 테이블을 일반 테이블로, 일반 테이블을 파티션된 테이블로 변환하는 예시

드롭 테이블 부분표1;

CREATE TABLE PARTTABLE1(

[Id] [int] IDENTITY(1,1) null이 아님,

[이름] [바르차르] (16) 무효가 아니라,

[Id2] [내성] 영주가 아닙니다

) ON partschema1(Id2);

parttable1에 값 ('1',1)을 삽입합니다;

parttable1에 값 ('1001', 1001)을 삽입합니다;

parttable1 값에 삽입 ('2001', 2001);

parttable1 값 ('3001',3001)에 삽입;

parttable1 값 ('4001', 4001)에 삽입;

1. 파티션 테이블에 생성된 유일한 제약 조건은 파티션 열을 포함해야 합니다.

ALTER TABLE PARTTABLE1 ADD PRIMARY Key 클러스터 ([ID] ASC) PK_prattable1_id 제약 추가(ADD TABLE PARTTABLE)

오류 열 'Id2'는 인덱스 'PK_prattable1_id'의 분할 열입니다. 고유 인덱스의 분할 열은 인덱스 키의 부분집합이어야 합니다.

2. 파티션 열인 id2에 새로운 클러스터 인덱스를 생성하고, parttable1은 여전히 파티션된 테이블입니다

parttable1(id2)에서 클러스터 인덱스 CI_prattable1_id2 생성;

3. 파티션 열 id2는 비클러스터 인덱스를 생성하며, parttable1은 여전히 파티션된 테이블입니다

parttable1의 인덱스 CI_prattable1_id2을 드롭;

parttable1(id2)에서 비클러스터 인덱스 NCI_prattable1_id2 생성;

4. 비분할 열인 ID 열은 클러스터 인덱스인 parttable1 또는 파티션 테이블을 생성하는데, 이는 비분할 열이 클러스터 인덱스 열이 될 수 있음을 나타냅니다

parttable1(id)에 클러스터 인덱스 CI_prattable1_id 생성;

5. 파티션 열 ID2는 비클러스터 인덱스로 재구성되며 파티션 방식을 사용하지 않으며, parttable1은 여전히 파티션 테이블입니다

PARTTABLE1(ID2)에서 [PRIMARY] 위에 (DROP_EXISTING = ON)를 두고 비클러스터 인덱스 NCI_prattable1_id2을 생성합니다;

6. 파티션 열인 id2는 ON 조건 없이 클러스터 인덱스로 재구성되며, parttable1은 여전히 파티션 테이블입니다

parttable1에서 인덱스 CI_prattable1_id을 드롭;

parttable1에서 인덱스 NCI_prattable1_id2을 드롭;

parttable1(id2)에서 클러스터 인덱스 CI_prattable1_id2 생성;

parttable1(id2)에서 클러스터 인덱스 CI_prattable1_id2 생성하며, (DROP_EXISTING = ON);

7. 분할된 열인 id2가 ON 조건을 가진 클러스터 인덱스로 재구성되지만, 분할 방식은 사용하지 않으며, parttable1은 비분할 테이블이 됩니다

PARTTABLE1(ID2)에서 클러스터 인덱스 CI_prattable1_id2을 생성하고, [PRIMARY]에 (DROP_EXISTING = ON)을 생성합니다;

8. 파티션 열 id2를 클러스터 인덱스로 재구성하고, 분할 방식을 사용하여 parttable1이 파티션 테이블이 됩니다

parttable1(id2)에서 클러스터 인덱스 CI_prattable1_id2을 생성하고, partschema1(Id2)에서 (DROP_EXISTING = ON)를 생성합니다;

9. 위 8에서 클러스터 인덱스를 삭제한 후에도 parttable1은 여전히 파티션된 테이블입니다

parttable1의 인덱스 CI_prattable1_id2을 드롭;

10. 파티션 열 ID2는 클러스터 인덱스로 생성되며 파티션 방식을 사용하지 않으며, parttable1은 비분할 테이블이 됩니다

parttable1(id2) [PRIMARY]에서 클러스터 인덱스 CI_prattable1_id2 생성;

11. 위 10의 클러스터 인덱스를 삭제한 후에도 parttable1은 여전히 비분할 테이블입니다

parttable1의 인덱스 CI_prattable1_id2을 드롭;

12. 파티션 열 id2는 비클러스터 인덱스로 새로 생성되지만, 파티셔닝 방식이 사용되었음에도 불구하고 여전히 비파티션 테이블입니다

parttable1(id2)에서 partschema1(Id2)에서 비클러스터 인덱스 NCI_prattable1_id2 생성;

파티션된 테이블이 일반 테이블로 변환되면 기본 키 제약 조건이 삭제되고, 클러스터 인덱스가 원래 기본 키 필드의 기본 키로 재구성되거나 재구성되지만, 이는 파티셔닝 방식과는 관련이 없습니다

ALTER TABLE Table_name 제약 PK_NAME 드롭 WITH (>

Table_name(열)에 클러스터 인덱스 PK_NAME을 생성하여 ([PRIMARY];

또는

ALTER TABLE PK_NAME Table_name CLUSTER (COLUMN) WITH (on [primary];

일반 테이블이 파티션 테이블로 변환되고, 원래 기본 키를 유지하려면 기본 키 제약이 삭제되고, 그 후 집계 인덱스로 설정하지 않고 기본 키가 생성되고, 새로운 집계 인덱스가 생성되어 집계 인덱스에 분할 방식이 사용됩니다

ALTER TABLE Table_name 제약 PK_NAME 드롭 WITH (>

ALTER TABLE Table_name NONCLUSTERED(COLUMN) PK_NAME 제약 조건을 추가하고, (ON [PRIMARY];

--기본 키를 만들되 클러스터 인덱스로 설정하지 마세요

파티션 스킴(파티션 필드)에서 Table_name(열) 위에 클러스터 인덱스 index_name 생성

--분할 방식을 사용하는 새로운 클러스터 인덱스를 만든다

예를 들어, 분할된 테이블의 전체 행 수와 크기를 쿼리합니다. 예를 들어, 테이블은 CRM입니다. 이메일로그

'crm' sp_spaceused 임원. 이메일로그';

분할된 테이블의 정보를 쿼리하고, 각 파티션이 몇 행을 가지고 있는지 확인하세요. 예를 들어, 테이블은 CRM입니다. 이메일로그

선택(Convert) (varchar(50), ps.name

) partition_scheme,

p.partition_number,

개종(바르차르(10), ds2.name

) 파일 그룹으로,

convert(varchar(19), isnull(v.value, ''), 120)를 range_boundary,

str(p.rows, 9) 행으로서

sys.indexes i에서

sys.partition_schemes PS를 i.data_space_id = ps.data_space_id에 합쳐

DDS에 가입sys.destination_data_spaces

ps.data_space_id = dds.partition_scheme_id

dds.data_space_id sys.data_spaces DS2에 합류하세요 = ds2.data_space_id

Join Sys.partitions p on dds.destination_id = p.partition_number

그리고 p.object_id = i.object_id, p.index_id = i.index_id

sys.partition_functions PF에 ps.function_id = pf.function_id

왼쪽 시스템 가입. Partition_Range_values v on pf.function_id = v.function_id

그리고 v.boundary_id = p.partition_number - pf.boundary_value_on_right

여기서 i.object_id = object_id('crm. 이메일로그')

그리고 (0, 1)에서 i.index_id

p.partition_number 순서

쿼리 파티션 함수

선택: * sys.partition_functions

파티션 아키텍처 보기

선택 * sys.partition_schemes