Início / dba / Perguntas / 295307
Accepted
sebeid
Asked: 2021-07-07 06:47:27 +0800 CST

Tabela existente particionada

  • 772

Eu tenho uma tabela com 800 GB e preciso particioná-la por ano

Esta é uma tabela atual

CREATE TABLE [dbo].[MessageInbox]
(
    [Id] [INT] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
    [Subject] [VARCHAR](250) NULL,
    [MessageFrom] [VARCHAR](50) NULL,
    [MessageText] [VARCHAR](MAX) NULL,
    [DateReceived] [DATETIME] NOT NULL,
    [DateCreated] [DATETIME] NOT NULL,
    [ProfileId] [INT] NOT NULL,
    [IsRead] [BIT] NULL,
    [InstanceId] [INT] NULL,
    [msgType] [VARCHAR](25) NULL,
    [Tags] [VARCHAR](100) NULL,
    [excerpt] [VARCHAR](500) NULL,
    CONSTRAINT [PK_Inbox]
        PRIMARY KEY CLUSTERED ([Id] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
              ALLOW_PAGE_LOCKS = ON
             ) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO

Foi assim que eu pensei em particionar

CREATE PARTITION FUNCTION [PartitioningByYear] (DATETIME)
AS RANGE RIGHT FOR VALUES
(   '20150101',
    '20160101',
    '20170101',
    '20180101',
    '20190101',
    '20200101',
    '20210101',
    '20220101',
    '20230101',
    '20240101'
);


---DROP PARTITION FUNCTION [PartitioningByYear];
CREATE PARTITION SCHEME PartitionByYear
AS PARTITION PartitioningByYear
TO
(
    Year2014,
    Year2015,
    Year2016,
    Year2017,
    Year2018,
    Year2019,
    Year2020,
    Year2021,
    Year2022,
    Year2023,
    Year2024
);


CREATE TABLE [dbo].[MessageInbox]
(
    [Id] [INT] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
    [Subject] [VARCHAR](250) NULL,
    [MessageFrom] [VARCHAR](50) NULL,
    [MessageText] [VARCHAR](MAX) NULL,
    [DateReceived] [DATETIME] NOT NULL,
    [DateCreated] [DATETIME] NOT NULL,
    [ProfileId] [INT] NOT NULL,
    [IsRead] [BIT] NULL,
    [InstanceId] [INT] NULL,
    [msgType] [VARCHAR](25) NULL,
    [Tags] [VARCHAR](100) NULL,
    [excerpt] [VARCHAR](500) NULL
) ON PartitionByYear (DateCreated);

CREATE CLUSTERED INDEX CI_MessageInbox1
ON dbo.MessageInbox (DateCreated);
GO
CREATE NONCLUSTERED INDEX IX_messageinbox_id ON dbo.MessageInbox (Id);

Minhas perguntas são

1- Esta é a maneira ideal de criar a tabela de partição?

2- Estou confuso em não ter o ID como chave primária, tem como adicionar na chave primária?

3- Tendo a data criada como chave no índice clusterizado. isso não degradará o desempenho e adicionará pesquisas extras de chave para cada consulta?

Quero particionar a tabela, pois acho que será mais fácil se precisar arquivar / descartar anos mais antigos. E eu posso criar índice/manter em partições em vez de ter um índice em toda a tabela.

sql-server

1 respostas

  1. Best Answer

    A chave primária não precisa ser igual à chave de cluster. O padrão ao criar uma chave primária é torná-la também o índice clusterizado, mas isso não é necessário .

    Tente isso para a CREATE TABLEdeclaração:

    CREATE TABLE [dbo].[MessageInbox]
(
    [Id] [INT] NOT NULL
        IDENTITY(1, 1)
        NOT FOR REPLICATION
        PRIMARY KEY NONCLUSTERED,
    [Subject] [VARCHAR](250) NULL,
    [MessageFrom] [VARCHAR](50) NULL,
    [MessageText] [VARCHAR](MAX) NULL,
    [DateReceived] [DATETIME] NOT NULL,
    [DateCreated] [DATETIME] NOT NULL
        INDEX MessageInbox_cx CLUSTERED,
    [ProfileId] [INT] NOT NULL,
    [IsRead] [BIT] NULL,
    [InstanceId] [INT] NULL,
    [msgType] [VARCHAR](25) NULL,
    [Tags] [VARCHAR](100) NULL,
    [excerpt] [VARCHAR](500) NULL
) ON PartitionByYear (DateCreated);

    Isso tornará a [id]coluna a chave primária e a [DateCreated]coluna será o índice clusterizado.

    Sem saber muito sobre seus dados, eu diria que provavelmente seria útil adicionar compactação de dados à sua tabela e índices não clusterizados. Você pode fazer isso adicionando o seguinte à CREATE TABLEinstrução:

    CREATE TABLE [dbo].[MessageInbox]
(
    [Id] [INT] NOT NULL
        IDENTITY(1, 1)
        NOT FOR REPLICATION
        PRIMARY KEY NONCLUSTERED
        WITH (DATA_COMPRESSION = PAGE),
    [Subject] [VARCHAR](250) NULL,
    [MessageFrom] [VARCHAR](50) NULL,
    [MessageText] [VARCHAR](MAX) NULL,
    [DateReceived] [DATETIME] NOT NULL,
    [DateCreated] [DATETIME] NOT NULL
        INDEX MessageInbox_cx CLUSTERED
        WITH (DATA_COMPRESSION = PAGE),
    [ProfileId] [INT] NOT NULL,
    [IsRead] [BIT] NULL,
    [InstanceId] [INT] NULL,
    [msgType] [VARCHAR](25) NULL,
    [Tags] [VARCHAR](100) NULL,
    [excerpt] [VARCHAR](500) NULL
) ON PartitionByYear (DateCreated);
    • 7

relate perguntas