[Comunicação] Resumo de Tabelas Particionadas no SQL Server

Documentação oficial

Etapas de alto nível para criar uma tabela de partições

1. Criar um grupo de arquivos (semelhante ao espaço de tabela do Oracle), claro, você não pode criá-lo, pode colocar todas as partições em um único grupo de arquivos

2. Estabelecer uma função de partição e distribuir os dados de acordo com o intervalo

3. Estabeleça um esquema de partição, associe a função de partição e também associe o grupo de arquivos, a função de partição divide os dados em vários intervalos, você precisa associar vários grupos de arquivos, claro, também pode colocar esses intervalos de partições no mesmo grupo de arquivos

4. Criar uma tabela e associar o esquema de zoneamento

Um bug encontrado

Quando você clica diretamente com o botão direito na tabela para exportar a estrutura da tabela, não pode exportar as informações da partição, e só pode exportar as informações da tabela clicando com o botão direito no script database-task-generate

Algumas conclusões da tabela de partições:

1. Campos particionados não precisam necessariamente ser indexados

2. Campos particionados podem ser criados como índices agrupados ou índices sem clusters

3. Campos particionados, sejam índices agrupados ou não agrupados, são reconstruídos em índices agrupados sem esquemas de particionamento associados, e a tabela particionada torna-se uma tabela não particionada

4. Converta uma tabela comum em uma tabela particionada, basta criar um índice agrupado na tabela e usar o esquema de particionamento no índice agrupado. Por exemplo, quando o campo 1 de uma tabela não particionada cria um índice agrupado e está associado a um esquema de particionamento, a tabela é convertida em uma tabela particionada, e o campo particionado é o campo 1.

5. Converter a tabela particionada em uma tabela normal, se houver um campo de partição com índice, reconstruir o campo particionado como um índice agrupado e não associar ao esquema de partição; se o campo de partição não tiver índice, o campo de partição pode criar um novo índice agrupado e não se associar ao esquema de partição

6. Mudar uma tabela normal para uma tabela de partição ou alterar uma tabela de partição para uma tabela comum só pode ser feito usando índices agrupados, pois com índices agrupados existem tabelas de organização de índices, e a redistribuição das tabelas é realizada por meio da reconstrução de índices agrupados. Se a tabela comum se tornar uma tabela particionada, reconstrua o campo de partição em um índice agrupado e associe-o ao esquema de partição, e se a tabela de partição se tornar uma tabela normal, reconstrua o campo de partição em um índice agrupado sem associar o esquema de partição.

7. Para criar uma tabela de partição, a restrição de unicidade deve incluir uma coluna de partição

8. Ao criar um esquema de partição, é necessário garantir que o número de grupos de arquivos corresponda ao segmento de intervalo de partições da função de partição, e não importa se o nome do grupo de arquivos é duplicado.

9. Funções de particionamento e esquemas de particionamento estão em um banco de dados, não enfrentando toda a instância

10. A tabela de partições é grande demais para ocupar muito espaço em disco, e o tamanho ainda não muda após deletar alguns campos

Passos para criar uma tabela de partições

1.1. Exemplo de criação de um grupo de arquivos

alterar banco de dados teste1 adicionar grupo de arquivos parte 1;

Alter banco de dados test1 adicionar grupo de arquivos parte 1000;

alterar banco de dados test1 adicionar filegroup part2000;

alterar banco de dados test1 adicionar grupo de arquivos parte 3000;

alterar banco de dados test1 adicionar grupo de arquivos parte 4000;

1.2. Estabelecer um exemplo de arquivo e associar um grupo de arquivos

ALTERAR BANCO de DADOS test1 ADICIONAR ARQUIVO(NOME = test1part1,NOME do ARQUIVO = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) ao filegroup parte 1;

ALTER DATABASE test1 ADD FILE(NAME = test1part1000,FILENAME = 'G:\test1000.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) ao filegroup part1000;

ALTER BANCO de DADOS test1 ADICIONAR FILE(NAME = test1part2000,FILENAME = 'G:\test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) ao filegroup part2000;

ALTER BANCO de DADOS test1 ADICIONAR FILE(NAME = test1part3000,FILENAME = 'G:\test1part3000.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) ao filegroup PART3000;

ALTER BANCO de DADOS test1 ADICIONAR FILE(NAME = test1part4000,FILENAME = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) ao filegroup part4000;

2. Um exemplo de estabelecimento de uma função de partição, a função de partição é chamada partfun1

CRIAR FUNÇÃO DE PARTIÇÃO partfun1 (int)

QUANTO AO ALCANCE RESTANTE PARA VALORES ('1000', '2000', '3000', '4000')

--VALORES ('1000', '2000', '3000', '4000') indicam que a tabela será dividida em 5 zonas, que são divididas de acordo com o tamanho dos valores nos campos da tabela, sendo as cinco menores - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-maiores

3. Um exemplo de estabelecer um esquema, associar a função de partição partfun1 e associar o grupo de arquivos

CRIAR ESQUEMA DE PARTIÇÃO partschema1

COMO PARTIÇÃO PARTE 1

TO (parte 1, parte 1000, parte2000, parte3000, parte 4000);

--Construído sobre vários grupos de arquivos de parte 1, parte 1000, parte 2000, parte 3000, parte 4000

CRIAR ESQUEMA DE PARTIÇÃO partschema2

COMO PARTIÇÃO PARTE 1

TO (PARTE1,[PRIMÁRIO],[PRIMÁRIO],[PRIMÁRIO]);

--Construído sobre a parte 1, grupo de arquivos [PRIMARY], não há problema em substituir a parte 1 por [PRIMARY], para que seja semelhante a construir no grupo de arquivos [PRIMARY]

CRIAR ESQUEMA DE PARTIÇÃO partschema3

COMO PARTIÇÃO PARTE 1

ALL TO (parte 1);

--todos construídos no grupo de arquivos parte 1

CRIAR ESQUEMA DE PARTIÇÃO partschema4

COMO PARTIÇÃO PARTE 1

TODOS PARA ([PRIMÁRIO]);

--todos construídos sobre o grupo de arquivos [PRIMARY]

4. Exemplo de estabelecimento de uma tabela de partição

CRIAR TABELA PARTTABLE1(

[ID] [int] NÃO NULO,

[IDText] [nvarchar] (max) ZERO

[Data] [data-hora] NULL)

ON [partschema1](ID);

inserir em parttable1 valores (1,'1', getdate()-4);

inserir em parttable1 valores (1001, '1001', getdate()-3);

inserir em valores do parttable1 (2001, '2001', getdate()-2);

inserir em parttable1 valores (3001,'3001', getdate()-1);

inserir em parttable1 valores (4001,'4001', getdate());

5. Verificar os dados da tabela de partições

SELECT * FROM parttable1;

--Retorna todas as linhas da tabela de partição

SELECT distinto $PARTITION. [partfun1] (4) DO quadro da parte1;

--Retorna que particiona a linha com o valor do campo ID 4 a que pertence

SELECT * FROM parttable1 onde $PARTITION. [partfun1] (ID)=2

--Retorna todas as linhas da segunda partição, o ID é o ID do campo da partição

Nota: Você não pode usar SELECT * FROM parttable1 onde $PARTITION. [partfun1] (ID)=2 tem um resultado, significa que é uma tabela particionada, e o último teste 7 deste artigo é uma tabela não particionada, mas SELECT * FROM parttable1 onde $PARTITION. [partfun1] (ID)=2 ainda tem resultado

Novas partições

1. Especifique um grupo de arquivos que possa ser usado para o esquema de partições (adicione um grupo de arquivos para o esquema de partições).

2. Modificar a função de partição (adicionar o intervalo de dados da função de partição)

ALTERAR PARTITION SCHEME partschema1 PRÓXIMO USO [PRIMÁRIO]

ALTERAR FUNÇÃO DE PARTIÇÃO partfun1() FAIXA DIVIDIDA ('4500')

selecione p.partition_number,p.rows de sys.indexes i inner join sys.partitions p on p.object_id = i.object_id e i.object_id = object_id('parttable2') ordenem por 1

--A primeira instrução, se o esquema de partição usa ALL TO ([PRIMÁRIO]), essa instrução não precisa ser executada

--Uma nova partição é adicionada à segunda instrução, o intervalo é de 4000-4500

--A terceira afirmação verifica se a nova partição existe e se há linhas

Excluir\Mesclar partição

ALTERAR FUNÇÃO DE PARTIÇÃO partfun1() FAIXA DE FUSÃO ('2000')

A partição de 1000-2000 foi deletada e incorporada a 1000-3000

--Incapaz de executar ALTER TABLE NAME, DROP PARTITION PARTITIONNAME como o Oracle;

Exclua a tabela de partições e o grupo de arquivos correspondente

A ordem de exclusão é: excluir a tabela de partições, excluir o esquema de partições, excluir a função de partição e, finalmente, excluir o grupo de arquivos, e o arquivo correspondente é excluído após o grupo de arquivos ser excluído

Exemplo de converter uma tabela particionada em uma tabela normal e uma tabela normal em uma tabela particionada

TABELA DE DESCARTE 1;

CRIAR TABELA PARTTABLE1(

[Id] [int] IDENTIDADE(1,1) NÃO NULA,

[Nome] [varchar] (16) NÃO NULO,

[ID2] [int] NÃO NULO

) ON partschema1(Id2);

inserir em parttable1 valores ('1',1);

inserir valores em parttable1 ('1001', 1001);

inserir valores em parttable1 ('2001', 2001);

inserir valores no parttable1 ('3001', 3001);

inserir valores no parttable1 ('4001', 4001);

1. A restrição única criada na tabela de partição deve conter uma coluna de partição.

ALTER TABLE PARTTABLE1 ADICIONAR RESTRIÇÃO PK_prattable1_id CLUSTER DE CHAVE PRIMÁRIA ([ID] ASC)

A coluna de erro 'Id2' é a coluna de particionamento do índice 'PK_prattable1_id'. Colunas de partição para um índice único devem ser um subconjunto da chave de índice.

2. Crie um novo índice agrupado na coluna de partição id2, e o parttable1 ainda será uma tabela particionada

criar CI_prattable1_id2 de índice agrupado no parttable1(id2);

3. A coluna de partição id2 cria um índice não agrupado, e o parttable1 ainda é uma tabela particionada

índice de queda CI_prattable1_id2 no parttable1;

criar NCI_prattable1_id2 de índice não agrupado no parttable1(id2);

4. A coluna de id de coluna não particionada cria um índice clusterizado, parttable1 ou uma tabela particionada, indicando que a coluna não particionada pode ser uma coluna de índice de cluster

criar CI_prattable1_id de índice clusterizado no parttable1(id);

5. A coluna de partição ID2 é reconstruída em um índice não agrupado e não utiliza um esquema de particionamento, o parttable1 ainda é uma tabela de partições

criar índice não agrupado NCI_prattable1_id2 no parttable1(id2) COM (DROP_EXISTING = LIGADO) EM [PRIMÁRIO];

6. A coluna de partição id2 é reconstruída em um índice clusterizado sem a condição ON, e parttable1 ainda é uma tabela de partições

drop index CI_prattable1_id no parttable1;

drop index NCI_prattable1_id2 no parttable1;

criar CI_prattable1_id2 de índice agrupado no parttable1(id2);

criar índice clusterizado CI_prattable1_id2 em parttable1(id2) COM (DROP_EXISTING = ON);

7. A coluna particionada id2 é reconstruída em um índice clusterizado com condição ON, mas sem usar o esquema de particionamento, e o parttable1 se torna uma tabela não particionada

Crie CI_prattable1_id2 de índice agrupado em parttable1(id2) COM (DROP_EXISTING = LIGADO) EM [PRIMÁRIO];

8. A coluna de partição id2 é reconstruída em um índice agrupado e, usando o esquema de particionamento, o parttable1 torna-se uma tabela particionada

criar CI_prattable1_id2 índice clusterizado no parttable1(id2) COM (DROP_EXISTING = ON) no partschema1(Id2);

9. Após excluir o índice agrupado no 8 acima, o parttable1 ainda é uma tabela particionada

índice de queda CI_prattable1_id2 no parttable1;

10. A coluna de partição ID2 é criada como um índice agrupado e não usa o esquema de particionamento, e o parttable1 se torna uma tabela não particionada

criar CI_prattable1_id2 de índice clusterizado no parttable1(id2) EM [PRIMÁRIO];

11. Após excluir o índice agrupado dos 10 acima de mim, o parttable1 ainda é uma tabela não particionada

índice de queda CI_prattable1_id2 no parttable1;

12. A coluna de partição id2 foi criada recentemente como um índice não agrupado, embora o esquema de particionamento seja usado, ainda é uma tabela não particionada

criar NCI_prattable1_id2 índice não agrupado no parttable1(id2) no partschema1(Id2);

Se a tabela particionada for convertida em uma tabela normal, a restrição da chave primária é excluída, e o índice do cluster é reconstruído ou reconstruído na chave primária dos campos da chave primária original, mas isso não está relacionado ao esquema de particionamento

ALTERAR TABELA Table_name DROP RESTRIÇÃO PK_NAME COM (>

CRIAR PK_NAME DE ÍNDICE AGRUPADO EM Table_name(coluna) COM (EM [PRIMÁRIO];

ou

ALTERAR TABELA Table_name ADICIONAR RESTRIÇÃO PK_NAME CHAVE PRIMÁRIA CLUSTERED(coluna) COM (EM [PRIMÁRIA];

Se a tabela normal for convertida em uma tabela particionada, se a chave primária original for mantida, a restrição da chave primária é eliminada, e então a chave primária é criada sem defini-la como um índice agregado, e então um novo índice agregado é criado, e o esquema de particionamento é usado no índice agregado

ALTERAR TABELA Table_name DROP RESTRIÇÃO PK_NAME COM (>

ALTERAR TABELA Table_name ADICIONAR RESTRIÇÃO PK_NAME CHAVE PRIMÁRIA NONCLUSTERED(coluna) COM (EM [PRIMÁRIA];

--Criar uma chave primária, mas não definir como um índice agrupado

CRIAR index_name DE ÍNDICE AGRUPADO EM Table_name(coluna) NO esquema de partição (campo de partição)

--Criar um novo índice clusterizado no qual um esquema de particionamento é usado

Consulte o número total de linhas e o tamanho de uma tabela particionada, por exemplo, a tabela é CRM. E-mailLog

Executivo sp_spaceused 'CRM. E-mailLog';

Consulte as informações de uma tabela particionada, quantas linhas cada partição tem, por exemplo, a tabela é CRM. E-mailLog

selecione converter(varchar(50), ps.name

) como partition_scheme,

p.partition_number,

converter(varchar(10), ds2.name

) como grupo de arquivos,

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

str(p.rows, 9) como linhas

De sys.indexes i

junte-se sys.partition_schemes ps em i.data_space_id = ps.data_space_id

Junte-se sys.destination_data_spaces DDS

em ps.data_space_id = dds.partition_scheme_id

Junte-se sys.data_spaces DS2 em dds.data_space_id = ds2.data_space_id

Junte-se às partições sys.partitions P em dds.destination_id = p.partition_number

e p.object_id = i.object_id e p.index_id = i.index_id

Junte-se sys.partition_functions PF em ps.function_id = pf.function_id

JOINA SYS. Partition_Range_values v em pf.function_id = v.function_id

e v.boundary_id = p.partition_number - pf.boundary_value_on_right

ONDE i.object_id = object_id('crm. E-mailLog')

e i.index_id em (0, 1)

Ordem por p.partition_number

Funções de partição de consulta

selecione * de sys.partition_functions

Veja a arquitetura da partição

selecione * de sys.partition_schemes