Recreate index on column store indexed table with 35 billion rows

I have a big table that I need to rebuild the index. The table is configured with Clustered Column Store Index (CCI) and we realized we need to sort the data according to specific use case.

User performs date range and equality query but because the data was not sorted in the way they would like to get it back, the query is not optimal. SQL Advisory Team recommended that data are organized in right row group so query can benefit from row group elimination.

Table Description:

Partition by Timestamp1, monthly PF

Total Rows: 31 billion

Est row size: 60 bytes

Est table size: 600 GB

Table Definition:

CREATE TABLE [dbo].[Table1](
    [PkId] [int] NOT NULL,
    [FKId1] [smallint] NOT NULL,
    [FKId2] [int] NOT NULL,
    [FKId3] [int] NOT NULL,
    [FKId4] [int] NOT NULL,
    [Timestamp1] [datetime2](0) NOT NULL,
    [Measurement1] [real] NULL,
    [Measurement2] [real] NULL,
    [Measurement3] [real] NULL,
    [Measurement4] [real] NULL,
    [Measurement5] [real] NULL,
    [Timestamp2] [datetime2](3) NULL,
    [TimeZoneOffset] [tinyint] NULL
)

CREATE CLUSTERED COLUMNSTORE INDEX [Table1_ColumnStoreIndex] ON [dbo].[Table1] WITH (DROP_EXISTING = OFF)
GO

Environment:

SQL Server 2014 Enterprise Ed.

8 Cores, 32 GB RAM

VMWare High Performance Platform

My strategy is:

Drop the existing CCI

Create ordinary Clustered Row Index with the right columns, this will sort the data

Recreate CCI with DROP EXISTING = OFF. This will convert the existing CRI into CCI.

My questions are:

Does it make sense to rebuild the index or just reload the data? Reloading may take a month to complete where as rebuilding the index may take as much time either, maybe...

If I drop the existing CCI, the table will expand as it may not be compressed anymore?

链接地址: http://www.djcxy.com/p/95686.html

上一篇: SQL Server的隐藏功能

下一篇: 在列存储索引表上重新创建索引，包含350亿行