January 6, 2003 at 9:24 am
I have a partitioned view that when run using static values searches only the appropriate member table it should. However, when the static values are changed to variables and those variables assigned values, it (the query optimizer) searches all the member tables in the view, even though the sql itself is exactly the same. This is very easy to reproduce. The following script will create 2 small tables with the correct partitioning criteria, a view over the tables and then insert a few rows into each member table. Lastly, run the sql at the bottom and then check the plan produced by the query. You can see that all member tables are checked when variables are used instead of static values.
What's the deal?
Many thanks for any help with this.
/***** Script start ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tst1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tst1]
GO
CREATE TABLE [dbo].[tst1] (
[checkDate] [int] NOT NULL ,
[state] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[city] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tst1] WITH CHECK ADD
CONSTRAINT [PK_tst1] PRIMARY KEY CLUSTERED
(
[checkDate],
[state],
[city]
) ON [PRIMARY],
CONSTRAINT [CK_tst1] CHECK ([CheckDate] = 20021)
GO
/*************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tst2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tst2]
GO
CREATE TABLE [dbo].[tst2] (
[checkDate] [int] NOT NULL ,
[state] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[city] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tst2] WITH CHECK ADD
CONSTRAINT [PK_tst2] PRIMARY KEY CLUSTERED
(
[checkDate],
[state],
[city]
) ON [PRIMARY],
CONSTRAINT [CK_tst2] CHECK ([CheckDate] = 20022)
GO
/**************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vuTst]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vuTst]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE
View vuTst
AS
SELECT * FROM tst1
UNION ALL
SELECT * FROM tst2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/***************/
insert into tst2 values(20022,'colorado','denver','88277')
insert into tst2 values(20022,'colorado','durango','88277')
insert into tst2 values(20022,'colorado','leadville','88277')
insert into tst2 values(20022,'colorado','vail','88277')
insert into tst2 values(20022,'colorado','aspen','88277')
insert into tst2 values(20022,'colorado','vista','88277')
insert into tst2 values(20022,'colorado','col sprngs','88277')
insert into tst2 values(20022,'texas','dallas','75207')
insert into tst2 values(20022,'texas','ft. worth','76204')
insert into tst2 values(20022,'texas','abilene','78277')
insert into tst2 values(20022,'texas','amarillo','74907')
insert into tst2 values(20022,'texas','houston','88307')
/****************************/
insert into tst1 values(20021,'colorado','denver','88277')
insert into tst1 values(20021,'colorado','durango','88277')
insert into tst1 values(20021,'colorado','leadville','88277')
insert into tst1 values(20021,'colorado','vail','88277')
insert into tst1 values(20021,'colorado','aspen','88277')
insert into tst1 values(20021,'colorado','vista','88277')
insert into tst1 values(20021,'colorado','col sprngs','88277')
insert into tst1 values(20021,'texas','dallas','75207')
insert into tst1 values(20021,'texas','ft. worth','76204')
insert into tst1 values(20021,'texas','abilene','78277')
insert into tst1 values(20021,'texas','amarillo','74907')
insert into tst1 values(20021,'texas','houston','88307')
insert into tst1 values(20021,'texas','austin','88307')
insert into tst1 values(20021,'texas','el paso','88307')
insert into tst1 values(20021,'texas','san anton','88307')
/***** Script end ******/
declare @state varchar(10)
declare @checkdate int
declare @city varchar(10)
set @city = 'dallas'
set @checkdate = 20021
set @state = 'texas'
select * from vuTst
where checkDate = 20021 --@checkdate
and state = 'texas' --@state
and city = 'austin' --@city
/* this sql checks for untrusted partitioning columns */
SELECT name, status FROM sysobjects WHERE xtype = 'C' AND status &0x800 = 0x800
January 9, 2003 at 8:00 am
This was removed by the editor as SPAM
February 6, 2003 at 12:34 pm
This is kind of by design. Without a literal value, the optimizer cannot use your CHECK constraint to locate the member, because it ignores the the content of the variable.
This allows the plan to be reused for other (identical) queries with different content of the variables.
Kind of the same as when you use a placeholder (parameterized query), then the plan will be for average selectivity, not selectivity for the actual value.
In theory you should be able to replace all criterias, but your 'partition key'
with variables.
So:
declare @state varchar(10)
declare @checkdate int
declare @city varchar(10)
set @city = 'dallas'
set @checkdate = 20021
set @state = 'texas'
select * from vuTst
where checkDate = 20021 --@checkdate
and state = @state
and city = @city
should only go to the table with data for checkDate = 20021.
You still get good plan-reuse, because now you get one plan for member 1, another for member 2 etc. But still reuse per member.
regards
jensk
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply