애플리케이션 수준 분할
이 샘플은 날짜 지정 전 또는 후에 내려진 명령에 따라 메모리 최적화 테이블 또는 디스크 기반 테이블에 데이터가 저장되는 애플리케이션 수준의 파티션을 보여줍니다. hotDate 이후의 모든 명령은 메모리 최적화 테이블에 저장되며, hotDate 보다 앞선 날짜의 모든 명령은 디스크 기반 테이블에 저장됩니다. 동시 트랜잭션이 많이 있는 극단적인 OLTP 작업을 가정합니다. 여러 개의 동시 트랜잭션이 hotDate를 변경하려고 하는 경우에도 이 비즈니스 규칙(메모리 최적화 테이블에 있는 최근 주문)을 적용해야 합니다.
이 예제에서는 디스크 기반 테이블에 분할된 테이블 을 사용하지 않지만 제 3의 테이블을 사용하여 두 테이블 간의 명시적 분할 지점을 추적합니다. 분할 지점을 사용하여 새로 삽입된 데이터가 날짜에 따라 적절한 테이블에 항상 삽입되도록 할 수 있으며 데이터를 찾을 위치를 결정할 수도 있습니다. 늦게 도착하는 데이터가 여전히 적절한 테이블로 이동합니다.
분할된 테이블을 사용하는 관련 샘플은 Application Pattern for Partitioning Memory-Optimized Tables을(를) 참조하십시오.
코드 목록
USE MASTER
GO
IF DB_ID (N'partitionsample2') IS NOT NULL
DROP DATABASE partitionsample2;
GO
CREATE DATABASE partitionsample2
-- Enable the database for In-Memory OLTP.
ALTER DATABASE partitionsample2 ADD FILEGROUP partitionsample2_mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE partitionsample2 ADD FILE( NAME = 'partitionsample2_mod' , FILENAME = 'c:\data\partitionsample2_mod') TO FILEGROUP partitionsample2_mod;
GO
USE partitionsample2
GO
-- Create a memory-optimized table for current (hot) orders.
IF OBJECT_ID(N'SalesOrders_hot',N'U') IS NOT NULL
DROP TABLE [dbo].[SalesOrders_hot]
CREATE TABLE dbo.SalesOrders_hot (
so_id INT NOT NULL PRIMARY KEY NONCLUSTERED,
cust_id INT NOT NULL,
so_date DATETIME2 NOT NULL INDEX ix_date NONCLUSTERED,
so_total MONEY NOT NULL,
INDEX ix_date_total NONCLUSTERED (so_date DESC, so_total DESC)
) WITH (MEMORY_OPTIMIZED=ON)
GO
-- Create a disk-based table for archiving older (cold) orders.
IF OBJECT_ID(N'SalesOrders_cold',N'U') IS NOT NULL
DROP TABLE [dbo].[SalesOrders_cold]
CREATE TABLE dbo.SalesOrders_cold (
so_id INT NOT NULL PRIMARY KEY NONCLUSTERED,
cust_id INT NOT NULL,
so_date DATETIME2 NOT NULL INDEX ix_date NONCLUSTERED,
so_total MONEY NOT NULL,
INDEX ix_date_total NONCLUSTERED (so_date DESC, so_total DESC)
)
GO
-- The date that splits old and new orders (hotDate)
-- is stored in this memory-optimized table.
IF OBJECT_ID(N'SalesOrders_hotDate') IS NOT NULL
DROP TABLE [dbo].[SalesOrders_hotDate]
CREATE TABLE dbo.SalesOrders_hotDate (
hotDate DATETIME2 not null PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1)
) WITH (MEMORY_OPTIMIZED=ON)
GO
-- STORED PROCEDURES
-- Set the hotDate with SNAPSHOT ISOLATION so if other transactions
-- try to update the hotDate, they will fail immediately due to a
-- write/write conflict.
IF OBJECT_ID(N'usp_SetHotDate') IS NOT NULL
DROP PROCEDURE [dbo].[usp_SetHotDate]
GO
CREATE PROCEDURE dbo.usp_SetHotDate (@newDate DATETIME2)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'english'
)
DELETE FROM [dbo].[SalesOrders_hotDate]
INSERT INTO [dbo].[SalesOrders_hotDate] (hotDate) VALUES (@newDate)
END
GO
-- Get the orders up to the hotDate
-- (Must be serializable, to prevent deleting rows that are not returned.)
IF OBJECT_ID(N'usp_getHotData') IS NOT NULL
DROP PROCEDURE [dbo].[usp_GetHotData]
GO
CREATE PROCEDURE dbo.usp_GetHotData (@hotDate DATETIME2)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SERIALIZABLE,
LANGUAGE = N'english'
)
SELECT so_id, cust_id, so_date, so_total FROM dbo.SalesOrders_hot WHERE so_date < @hotDate
DELETE FROM dbo.SalesOrders_hot WHERE so_date < @hotDate
END
GO
-- Inserts an order into the proper table depending on the current hotDate.
-- It is important that the SP for retrieving the hotDate is REPEATABLEREAD, in order to ensure that
-- the hotDate is not changed before the decision is made where to insert the order.
-- Note that insert operations [in both disk-based and memory-optimized tables] are always fully isolated, so the transaction
-- isolation level has no impact on the insert operations; this whole transaction is effectively REPEATABLEREAD.
IF OBJECT_ID(N'usp_InsertOrder') IS NOT NULL
DROP PROCEDURE [dbo].[usp_InsertOrder]
GO
CREATE PROCEDURE dbo.usp_InsertOrder (@id int, @custID nvarchar(10), @orderDate DATETIME2, @orderTotal MONEY)
AS BEGIN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
-- get hot date under repeatableread isolation; this is to guarantee it does not change before the insert is executed
DECLARE @hotDate DATETIME2
SET @hotDate = (SELECT hotDate FROM [dbo].[SalesOrders_hotDate] WITH (REPEATABLEREAD))
IF (@orderDate >= @hotDate) BEGIN
INSERT INTO [dbo].[SalesOrders_hot] (so_id, cust_id, so_date, so_total) VALUES (@id, @custID, @orderDate, @orderTotal)
END
ELSE BEGIN
INSERT INTO [dbo].[SalesOrders_cold] (so_id, cust_id, so_date, so_total) VALUES (@id, @custID, @orderDate, @orderTotal)
END
COMMIT TRAN
END
GO
-- Changes the hotDate and moves the rows between the tables as appropriate.
-- The hotDate is updated in this transaction; this means that if the hotDate is changed by another transaction
-- the update will fail due to a write/write conflict and the transaction is rolled back
-- therefore, the initial (SNAPSHOT) access of the hotDate is effectively REPEATABLEREAD.
IF OBJECT_ID(N'usp_ChangeHotDate') IS NOT NULL
DROP PROCEDURE [dbo].[usp_ChangeHotDate]
GO
CREATE PROCEDURE [dbo].[usp_ChangeHotDate](@newHotDate DATETIME2)
AS BEGIN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
DECLARE @oldHotDate DATETIME2
SET @oldHotDate = (SELECT hotDate FROM [dbo].[SalesOrders_hotDate] WITH (SNAPSHOT))
-- get hot date under repeatableread isolation; this is to guarantee it does not change before the insert is executed
IF (@oldHotDate < @newHotDate) BEGIN
INSERT INTO [dbo].[SalesOrders_cold] EXEC usp_GetHotData @newHotDate
END
ELSE BEGIN
INSERT INTO [dbo].[SalesOrders_hot] SELECT * FROM SalesOrders_cold WITH (SERIALIZABLE) WHERE so_date >= @newHotDate
DELETE FROM [dbo].[SalesOrders_cold] WITH (SERIALIZABLE) WHERE so_date >= @newHotDate
END
EXEC [dbo].[usp_SetHotDate] @newHotDate
COMMIT TRAN
END
GO
-- DEMO
DELETE FROM [dbo].[SalesOrders_cold]
GO
-- Initialize the order split date.
EXEC [dbo].[usp_SetHotDate] '2014-1-1'
GO
-- List the hotDate.
SELECT * FROM [dbo].[SalesOrders_hotDate]
GO
EXEC [dbo].[usp_InsertOrder] 1, 1001, '2013-11-14', 150
EXEC [dbo].[usp_InsertOrder] 2, 1001, '2014-3-4', 100
EXEC [dbo].[usp_InsertOrder] 3, 1001, '2013-1-23', 250
EXEC [dbo].[usp_InsertOrder] 4, 1001, '2013-8-6', 200
EXEC [dbo].[usp_InsertOrder] 5, 1001, '2012-11-1', 150
EXEC [dbo].[usp_InsertOrder] 6, 1001, '2014-1-9', 150
EXEC [dbo].[usp_InsertOrder] 7, 1001, '2014-2-14', 95
EXEC [dbo].[usp_InsertOrder] 8, 1001, '2012-1-17', 125
EXEC [dbo].[usp_InsertOrder] 9, 1001, '2014-3-8', 100
EXEC [dbo].[usp_InsertOrder] 10, 1001, '2013-9-24', 100
GO
-- List contents of the tables.
-- Query new orders.
SELECT * FROM [dbo].[SalesOrders_hot] ORDER BY so_date DESC
-- Query old orders.
SELECT * FROM [dbo].[SalesOrders_cold] ORDER BY so_date DESC
-- Move the hot date to March 1, 2014.
EXEC [dbo].[usp_ChangeHotDate] '2014-03-01'
-- List the new hotDate
SELECT * FROM [dbo].[SalesOrders_hotDate]
GO
-- Verify that all orders before March 1, 2014 were moved
-- to the older order table and list the data.
SELECT * FROM [dbo].[SalesOrders_hot] ORDER BY so_date DESC
SELECT * FROM [dbo].[SalesOrders_cold] ORDER BY so_date DESC