_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › ColumnstoreIndexes

Contents

1 蟲覯
2 2014..
3 2016


1 蟲覯 #

sample db [http] <--- れ企 .
USE AdventureWorksDWDenali;
GO

CREATE PARTITION FUNCTION [ByOrderDateMonthPF](int) AS RANGE RIGHT 
FOR VALUES (
    20050701, 20050801, 20050901, 20051001, 20051101, 20051201, 
    20060101, 20060201, 20060301, 20060401, 20060501, 20060601, 
    20060701, 20060801, 20060901, 20061001, 20061101, 20061201, 
    20070101, 20070201, 20070301, 20070401, 20070501, 20070601, 
    20070701, 20070801, 20070901, 20071001, 20071101, 20071201, 
    20080101, 20080201, 20080301, 20080401, 20080501, 20080601, 
    20080701, 20080801, 20080901, 20081001, 20081101, 20081201
) 
GO

CREATE PARTITION SCHEME [ByOrderDateMonthRange] 
AS PARTITION [ByOrderDateMonthPF] 
ALL TO ([PRIMARY]) 
GO

-- Create a partitioned version of the FactResellerSales table
CREATE TABLE [dbo].[FactResellerSalesPtnd]( 
    [ProductKey] [int] NOT NULL, 
    [OrderDateKey] [int] NOT NULL, 
    [DueDateKey] [int] NOT NULL, 
    [ShipDateKey] [int] NOT NULL, 
    [ResellerKey] [int] NOT NULL, 
    [EmployeeKey] [int] NOT NULL, 
    [PromotionKey] [int] NOT NULL, 
    [CurrencyKey] [int] NOT NULL, 
    [SalesTerritoryKey] [int] NOT NULL, 
    [SalesOrderNumber] [nvarchar](20) NOT NULL, 
    [SalesOrderLineNumber] [tinyint] NOT NULL, 
    [RevisionNumber] [tinyint] NULL, 
    [OrderQuantity] [smallint] NULL, 
    [UnitPrice] [money] NULL, 
    [ExtendedAmount] [money] NULL, 
    [UnitPriceDiscountPct] [float] NULL, 
    [DiscountAmount] [float] NULL, 
    [ProductStandardCost] [money] NULL, 
    [TotalProductCost] [money] NULL, 
    [SalesAmount] [money] NULL, 
    [TaxAmt] [money] NULL, 
    [Freight] [money] NULL, 
    [CarrierTrackingNumber] [nvarchar](25) NULL, 
    [CustomerPONumber] [nvarchar](25) NULL
) ON ByOrderDateMonthRange(OrderDateKey);
GO

-- Using simple or bulk logged recovery mode, and then the TABLOCK 
-- hint on the target table of the INSERTSELECT is a best practice
-- because it causes minimal logging and is therefore much faster.
ALTER DATABASE AdventureWorksDWDenali SET RECOVERY SIMPLE;
GO

-- Copy the data from the FactResellerSales into the new table
-- 殊 襷 蠍郁 覈視 .  螳 伎殊伎 . 
INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)
SELECT 
	ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, EmployeeKey, 
	PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, 
	RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
	DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, 
	Freight, CarrierTrackingNumber, CustomerPONumber
FROM dbo.FactResellerSales;
GO

coloumn store index襯 蟆曙
-- Create the columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX [csindx_FactResellerSalesPtnd]
ON [FactResellerSalesPtnd]
( 
    [ProductKey], 
    [OrderDateKey], 
    [DueDateKey], 
    [ShipDateKey], 
    [CustomerKey], 
    [EmployeeKey], 
    [PromotionKey], 
    [CurrencyKey], 
    [SalesTerritoryKey], 
    [SalesOrderNumber], 
    [SalesOrderLineNumber], 
    [RevisionNumber], 
    [OrderQuantity], 
    [UnitPrice], 
    [ExtendedAmount], 
    [UnitPriceDiscountPct], 
    [DiscountAmount], 
    [ProductStandardCost], 
    [TotalProductCost], 
    [SalesAmount], 
    [TaxAmt], 
    [Freight], 
    [CarrierTrackingNumber], 
    [CustomerPONumber], 
    [OrderDate],
    [DueDate],
    [ShipDate]
);

SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory
FROM FactResellerSalesPtnd
GROUP BY SalesTerritoryKey;
--企 'Worktable'. 蟆  0, 朱Μ 所鍵  0, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0, LOB 朱Μ 所鍵  0, LOB 覓朱Μ 所鍵  0, LOB 覩碁Μ 所鍵  0.
--企 'FactResellerSalesPtnd'. 蟆  1, 朱Μ 所鍵  49, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0, LOB 朱Μ 所鍵  0, LOB 覓朱Μ 所鍵  0, LOB 覩碁Μ 所鍵  0.

/*
SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory
FROM dbo.FactResellerSales
GROUP BY SalesTerritoryKey;

--企 'Worktable'. 蟆  0, 朱Μ 所鍵  0, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0, LOB 朱Μ 所鍵  0, LOB 覓朱Μ 所鍵  0, LOB 覩碁Μ 所鍵  0.
--企 'FactResellerSales'. 蟆  1, 朱Μ 所鍵  2982, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0, LOB 朱Μ 所鍵  0, LOB 覓朱Μ 所鍵  0, LOB 覩碁Μ 所鍵  0.
*/
cs01.png


れ 一危磯ゼ ロ企慨覃.. 螳 覃讌螳 .
INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)
SELECT 
	ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, EmployeeKey, 
	PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, 
	RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
	DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, 
	Freight, CarrierTrackingNumber, CustomerPONumber
FROM dbo.FactResellerSales;
GO


覃讌 35330, 譴 15, 1, 譴 1
レ 碁煙り 企 一危磯ゼ 一危誤 朱襦 INSERT 覓語 ろ給. INSERT 覓語 ろ蠍 レ 碁煙るゼ 觜燕螻 INSERT(襯) 襭 レ 碁煙るゼ れ 燕 覲伎.

螳 伎 .
alter index csindx_FactResellerSalesPtnd 
on dbo.FactResellerSalesPtnd disable;

INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)
SELECT 
	ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, EmployeeKey, 
	PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, 
	RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
	DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, 
	Freight, CarrierTrackingNumber, CustomerPONumber
FROM dbo.FactResellerSales;
GO

alter index csindx_FactResellerSalesPtnd 
on dbo.FactResellerSalesPtnd rebuild;


coloumn store index 伎 螻
SELECT SUM(on_disk_size_MB) AS TotalSizeInMB
FROM
(
   (SELECT SUM(css.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_segments AS css
        ON css.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
  UNION ALL
   (SELECT SUM(csd.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_dictionaries AS csd
        ON csd.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
) AS SegmentsPlusDictionary

colum store index 覲企 れ viewれ 谿瑚覃 .

  • sys.column_store_index_stats (Transact-SQL)
  • sys.column_store_dictionaries (Transact-SQL)
  • sys.column_store_segments (Transact-SQL)

蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:53

覲旧 磯Μ 襷 蟇一. 磯Μ螳 襷 覓語 願 蠍 螳襷 螳讌る 磯Μ蟆 覓朱 譯殊伎 蟇一. 企Π 蟆 覦一磯 苦 讌 豕覲