November 26, 2007 at 2:21 pm
I have partitioned a few tables based on date and unioned them in a view.
when i do this query :
SELECT sum(VIEW_FACT_SALESDATA.extendedprice) FROM VIEW_FACT_SALESDATA
LEFT JOIN Z_DIM_TIME ON VIEW_FACT_SALESDATA.InvoiceDate = Z_DIM_TIME.Cal_Date
WHERE Z_DIM_TIME.Cal_Date '1/1/2006'
it uses the partion correctly, but when i use this query
SELECT sum(VIEW_FACT_SALESDATA.extendedprice) FROM VIEW_FACT_SALESDATA
LEFT JOIN Z_DIM_TIME ON VIEW_FACT_SALESDATA.InvoiceDate = Z_DIM_TIME.Cal_Date
WHERE Z_DIM_TIME.Cal_Date between '1/1/2006' and '1/11/2006'
it scans all partitions instead of just the one for year 2006
I used this code to create the partitons
CREATE TABLE [dbo].[Z_FACT_SALESDATA_2010](
CHECK ([InvoiceDate] BETWEEN '01/01/2010' AND '12/31/2010')
What could I be doing wrong??
November 26, 2007 at 2:34 pm
Would it be possible for you to post the DDL statements for the tables and the view?
November 26, 2007 at 2:41 pm
the view is as follows
CREATE VIEW [dbo].[VIEW_FACT_SALESDATA]
AS
SELECT * FROM [dbo].[Z_FACT_SALESDATA_2000]
UNION ALL
SELECT * FROM [dbo].[Z_FACT_SALESDATA_2002]
UNION ALL
SELECT * FROM [dbo].[Z_FACT_SALESDATA_2003]
UNION ALL
SELECT * FROM [dbo].[Z_FACT_SALESDATA_2004]
UNION ALL
SELECT * FROM [dbo].[Z_FACT_SALESDATA_2005]
UNION ALL
SELECT * FROM [dbo].[Z_FACT_SALESDATA_2006]
UNION ALL
SELECT * FROM [dbo].[Z_FACT_SALESDATA_2007]
UNION ALL
SELECT * FROM [dbo].[Z_FACT_SALESDATA_2008]
UNION ALL
SELECT * FROM [dbo].[Z_FACT_SALESDATA_2009]
UNION ALL
SELECT * FROM [dbo].[Z_FACT_SALESDATA_2010]
----------------------------------------------------------------
10 tables partioned on date this is year 2010 table
-----------------------------------------------------------------
USE [EnterpriseDB]
GO
/****** Object: Table [dbo].[Z_FACT_SALESDATA] Script Date: 11/26/2007 11:37:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Z_FACT_SALESDATA_2010](
CHECK ([InvoiceDate] BETWEEN '01/01/2010' AND '12/31/2010'),
[SalesData_ID] [int] IDENTITY(1,1) NOT NULL,
[ORG_ID] [int] NULL,
[CLASS_ID] [int] NULL,
[TIME_ID] [int] NULL,
[BP] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[InvoiceDate] [datetime] NOT NULL,
[ShortItemNumber] [numeric](8, 0) NOT NULL,
[OrderQuantity] [numeric](15, 2) NULL,
[QuantityShipped] [numeric](15, 2) NULL,
[ExtendedPrice] [numeric](15, 2) NULL,
[ExtendedCost] [numeric](15, 2) NULL,
[GLOffset] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrderType] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NextStatus] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastStatus] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LineType] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Z_FACT_SALESDATA_2010] WITH NOCHECK ADD CONSTRAINT [FK_Z_FACT_SALESDATA_2010_Z_DIM_CLASS] FOREIGN KEY([CLASS_ID])
REFERENCES [dbo].[Z_DIM_CLASS] ([CLASS_ID])
GO
ALTER TABLE [dbo].[Z_FACT_SALESDATA_2010] CHECK CONSTRAINT [FK_Z_FACT_SALESDATA_2010_Z_DIM_CLASS]
GO
ALTER TABLE [dbo].[Z_FACT_SALESDATA_2010] WITH NOCHECK ADD CONSTRAINT [FK_Z_FACT_SALESDATA_2010_Z_DIM_ORG] FOREIGN KEY([ORG_ID])
REFERENCES [dbo].[Z_DIM_ORG] ([ORG_ID])
GO
ALTER TABLE [dbo].[Z_FACT_SALESDATA_2010] CHECK CONSTRAINT [FK_Z_FACT_SALESDATA_2010_Z_DIM_ORG]
GO
ALTER TABLE [dbo].[Z_FACT_SALESDATA_2010] WITH NOCHECK ADD CONSTRAINT [FK_Z_FACT_SALESDATA_2010_Z_DIM_TIME] FOREIGN KEY([TIME_ID])
REFERENCES [dbo].[Z_DIM_TIME] ([Time_ID])
GO
ALTER TABLE [dbo].[Z_FACT_SALESDATA_2010] CHECK CONSTRAINT [FK_Z_FACT_SALESDATA_2010_Z_DIM_TIME]
GO
/****** Object: Index [IX_BP] Script Date: 11/26/2007 12:01:45 ******/
CREATE NONCLUSTERED INDEX [IX_BP] ON [dbo].[Z_FACT_SALESDATA_2010]
(
[BP] ASC
) ON [PRIMARY]
GO
/****** Object: Index [IX_ShortItemNumber] Script Date: 11/26/2007 12:02:17 ******/
CREATE NONCLUSTERED INDEX [IX_ShortItemNumber] ON [dbo].[Z_FACT_SALESDATA_2010]
(
[ShortItemNumber] ASC
) ON [PRIMARY]
November 26, 2007 at 2:49 pm
For it to do what you want, InvoiceDate must be part of the primary key on each table that is part of the view.
Since you do not have a primary key defined, it doesn’t work.
You should read about partitioned views in SQL Server Books Online. The requirements are very specific.
November 26, 2007 at 3:04 pm
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply