HORIZONTAL PARTIONING - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

HORIZONTAL PARTIONING

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-01-18 : 11:45:36

I'm trying to partion a table with 3 years worth of information into 3 tables. My problem is that the query plans are not showing the usage of just one table if I defined a select with criteria on the field with the constraint. Any ideas comments would be appreciated.

I took the original table definition and modified the constraint names so that year partion looks as follows. Note the day_dt constraint with the between clause. The other two partions look the same with the 2001 replaced with appropriate year and different between clause for the day_dt constraint.

CREATE TABLE [dbo].[T_RAS_1000_F_WM_2001] (
[DAY_DT] [datetime] NOT NULL ,
[ITEM_ID] [int] NOT NULL ,
[STORE_ID] [int] NOT NULL ,
[TRT_ID] [smallint] NOT NULL ,
[INS_BATCH_ID] [smallint] NOT NULL ,
[UPD_BATCH_ID] [smallint] NOT NULL ,
[ERPDCR_ID] [smallint] NOT NULL ,
[TRAITED_ID] [smallint] NOT NULL ,
[VALID_ID] [smallint] NOT NULL ,
[SCAN_ID] [smallint] NOT NULL ,
[CURRENCY_ID] [smallint] NOT NULL ,
[CUR_TO_US_AMT] [numeric](18, 9) NOT NULL ,
[PEG_CUR_TO_US_AMT] [numeric](18, 9) NOT NULL ,
[UNIT_COST_AMT] [numeric](15, 2) NOT NULL ,
[UNIT_SALES_AMT] [numeric](15, 2) NOT NULL ,
[CASEPACK_QTY] [smallint] NOT NULL ,
[DCR_CASEPACK_QTY] [smallint] NOT NULL ,
[EXT_SALES_QTY] [int] NOT NULL ,
[EXT_SALES_AMT] [numeric](15, 2) NOT NULL ,
[NET_SHIP_QTY] [int] NOT NULL ,
[GROSS_SHIP_QTY] [int] NOT NULL ,
[SI_MUMD_AMT] [numeric](15, 2) NOT NULL ,
[SI_MUMD_QTY] [int] NOT NULL ,
[INSTOCK_ID] [smallint] NULL ,
[PLANO_ID] [int] NOT NULL
) ON [FACT]
GO

ALTER TABLE [dbo].[T_RAS_1000_F_WM_2001] WITH NOCHECK ADD
CONSTRAINT [PK_T_RAS_1000_F_WM_2001] PRIMARY KEY CLUSTERED
(
[DAY_DT],
[ITEM_ID],
[STORE_ID],
[TRT_ID]
) WITH FILLFACTOR = 70 ON [FACT]
GO

ALTER TABLE [dbo].[T_RAS_1000_F_WM_2001] ADD
CONSTRAINT [DF__T_RAS_100__ITEM_WM_2001___0F6D37F0] DEFAULT ((-2)) FOR [ITEM_ID],
CONSTRAINT [DF__T_RAS_100__STORE_WM_2001__10615C29] DEFAULT ((-2)) FOR [STORE_ID],
CONSTRAINT [DF__T_RAS_100__TRT_I_WM_2001__11558062] DEFAULT ((-2)) FOR [TRT_ID],
CONSTRAINT [DF__T_RAS_100__ERPDC_WM_2001__1249A49B] DEFAULT ((-2)) FOR [ERPDCR_ID],
CONSTRAINT [DF__T_RAS_100__CURRE_WM_2001__133DC8D4] DEFAULT ((-2)) FOR [CURRENCY_ID],
CONSTRAINT [DF__T_RAS_1000__PLANO_WM_2001__133DC8D4] DEFAULT ((-2)) FOR [PLANO_ID],
CONSTRAINT [C_T_RAS_DAY_DT_WM_2001] CHECK ([DAY_DT] >= '20010127' and [DAY_DT] <= '20020125')
GO

ALTER TABLE [dbo].[T_RAS_1000_F_WM_2001] ADD
CONSTRAINT [FK_RAS_1000_F_CURRENCY_WM_2001] FOREIGN KEY
(
[CURRENCY_ID]
) REFERENCES [dbo].[T_SAT_CURRENCY_L] (
[CURRENCY_ID]
),
CONSTRAINT [FK_RAS_1000_F_DAY_WM_2001] FOREIGN KEY
(
[DAY_DT]
) REFERENCES [dbo].[T_TME_DAY_L] (
[DAY_DT]
),
CONSTRAINT [FK_RAS_1000_F_ERPDCR_WM_2001] FOREIGN KEY
(
[ERPDCR_ID]
) REFERENCES [dbo].[T_DCR_ERPDCR_L] (
[ERPDCR_ID]
),
CONSTRAINT [FK_RAS_1000_F_ITEM_WM_2001] FOREIGN KEY
(
[ITEM_ID]
) REFERENCES [dbo].[T_PRD_ITEM_L] (
[ITEM_ID]
),
CONSTRAINT [FK_RAS_1000_F_PLANO_WM_2001] FOREIGN KEY
(
[PLANO_ID]
) REFERENCES [dbo].[T_PLA_PLANO_L] (
[PLANO_ID]
),
CONSTRAINT [FK_RAS_1000_F_STORE_WM_2001] FOREIGN KEY
(
[STORE_ID]
) REFERENCES [dbo].[T_GEO_STORE_L] (
[STORE_ID]
),
CONSTRAINT [FK_RAS_1000_F_TRT_WM_2001] FOREIGN KEY
(
[TRT_ID]
) REFERENCES [dbo].[T_TRT_TRT_L] (
[TRT_ID]
)
GO

The view definition is as follows

CREATE VIEW T_RAS_1000_F
AS
SELECT DAY_DT, ITEM_ID, STORE_ID, TRT_ID, INS_BATCH_ID, UPD_BATCH_ID, ERPDCR_ID, TRAITED_ID, VALID_ID, SCAN_ID, CURRENCY_ID, CUR_TO_US_AMT, PEG_CUR_TO_US_AMT, UNIT_COST_AMT, UNIT_SALES_AMT, CASEPACK_QTY, DCR_CASEPACK_QTY, EXT_SALES_QTY, EXT_SALES_AMT, NET_SHIP_QTY, GROSS_SHIP_QTY, SI_MUMD_AMT, SI_MUMD_QTY, INSTOCK_ID, PLANO_ID
FROM T_RAS_1000_F_WM_2001
UNION ALL
SELECT DAY_DT, ITEM_ID, STORE_ID, TRT_ID, INS_BATCH_ID, UPD_BATCH_ID, ERPDCR_ID, TRAITED_ID, VALID_ID, SCAN_ID, CURRENCY_ID, CUR_TO_US_AMT, PEG_CUR_TO_US_AMT, UNIT_COST_AMT, UNIT_SALES_AMT, CASEPACK_QTY, DCR_CASEPACK_QTY, EXT_SALES_QTY, EXT_SALES_AMT, NET_SHIP_QTY, GROSS_SHIP_QTY, SI_MUMD_AMT, SI_MUMD_QTY, INSTOCK_ID, PLANO_ID
FROM T_RAS_1000_F_WM_2002
UNION ALL
SELECT DAY_DT, ITEM_ID, STORE_ID, TRT_ID, INS_BATCH_ID, UPD_BATCH_ID, ERPDCR_ID, TRAITED_ID, VALID_ID, SCAN_ID, CURRENCY_ID, CUR_TO_US_AMT, PEG_CUR_TO_US_AMT, UNIT_COST_AMT, UNIT_SALES_AMT, CASEPACK_QTY, DCR_CASEPACK_QTY, EXT_SALES_QTY, EXT_SALES_AMT, NET_SHIP_QTY, GROSS_SHIP_QTY, SI_MUMD_AMT, SI_MUMD_QTY, INSTOCK_ID, PLANO_ID
FROM T_RAS_1000_F_WM_2003

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-01-18 : 16:50:58

It seems horizontal partioning will only work if DAY_DT is a PK and no other fields are PK. It also does not work if DAY_DT is not a PK.

How do you get around this?

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-01-18 : 17:33:34

Seems the right way to do this is to define dates in ISO integer format.

Here is what microsoft recommends.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/partitionsindw.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources