Problems with partitioned views and pruning

[SM]

/*
problem: Trying to get partitioned views to "prune" unneeded
partitions from
select statements against the partitioned view. There are 5
partitioned
tables. Each with a check constraint based on a range of formula_id
column.

Test: Run this script to create the 5 partitioned tables and the
partitioned view. Then
run the explain plans on the select statements at the end of the
script and see that we
can only prune if we give a seemingly superfluous is not null
criteria in addition to
the formula_id.

Ideal: We want to only have to use the formula_id in the select
statement to prune.
*/

/*note: you may get errors on the drops first time run*/
drop table dbo.cs_working_e2
go
CREATE TABLE dbo.cs_working_e2 (
formula_id int NOT NULL
CONSTRAINT formula_id_e14
CHECK (formula_id between 1
and 1000),
submission_id int NOT NULL,
node_id int NOT NULL,
reference_year smallint NOT NULL,
observation_period datetime NOT NULL,
authority_flag tinyint NOT NULL
CONSTRAINT ONE_DEFAULT3436
DEFAULT 1
CONSTRAINT Binary_flag_rule667
CHECK (authority_flag IN
(0, 1)),
interpolated_flag tinyint NOT NULL
CONSTRAINT ZERO_DEFAULT6926
DEFAULT 0
CONSTRAINT Binary_flag_rule668
CHECK (interpolated_flag IN
(0, 1)),
observation_value decimal_datatype NOT NULL,
time_created smalldatetime NOT NULL
CONSTRAINT
CURRENT_DATE_DEFAULT1807
DEFAULT getdate(),
CONSTRAINT XPKcs_working_e2
PRIMARY KEY NONCLUSTERED (formula_id, submission_id,
node_id, reference_year, observation_period)
--ON "INDEXES"
)
--ON "WORKING"
go
CREATE UNIQUE CLUSTERED INDEX XAK1cs_working_e2 ON dbo.cs_working_e2
(
submission_id ASC,
formula_id ASC,
node_id ASC,
authority_flag ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
go

CREATE INDEX XIE1cs_working_e2 ON dbo.cs_working_e2
(
node_id ASC,
authority_flag ASC,
formula_id ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
--ON "INDEXES"
go
drop table dbo.cs_working_indexes2
go
CREATE TABLE dbo.cs_working_indexes2 (
formula_id int NOT NULL
CONSTRAINT formula_id_indexes14
CHECK (formula_id between
7001 and 9000),
submission_id int NOT NULL,
node_id int NOT NULL,
reference_year smallint NOT NULL,
observation_period datetime NOT NULL,
authority_flag tinyint NOT NULL
CONSTRAINT ONE_DEFAULT3437
DEFAULT 1
CONSTRAINT Binary_flag_rule669
CHECK (authority_flag IN
(0, 1)),
observation_value decimal_datatype NOT NULL,
interpolated_flag tinyint NOT NULL
CONSTRAINT ZERO_DEFAULT6927
DEFAULT 0
CONSTRAINT Binary_flag_rule670
CHECK (interpolated_flag IN
(0, 1)),
time_created smalldatetime NOT NULL
CONSTRAINT
CURRENT_DATE_DEFAULT1808
DEFAULT getdate(),
CONSTRAINT XPKcs_working_indexes2
PRIMARY KEY NONCLUSTERED (formula_id, submission_id,
node_id, reference_year, observation_period)
--ON "INDEXES"
)
--ON "WORKING"
go

CREATE UNIQUE CLUSTERED INDEX XAK1cs_working_indexes2 ON
dbo.cs_working_indexes2
(
submission_id ASC,
formula_id ASC,
node_id ASC,
authority_flag ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
go

CREATE INDEX XIE1cs_working_indexes2 ON dbo.cs_working_indexes2
(
node_id ASC,
authority_flag ASC,
formula_id ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
--ON "INDEXES"
go

drop table dbo.cs_working_other2
go
CREATE TABLE dbo.cs_working_other2 (
formula_id int NOT NULL
CONSTRAINT formula_id_other14
CHECK (formula_id >= 9001),
submission_id int NOT NULL,
node_id int NOT NULL,
reference_year smallint NOT NULL,
observation_period datetime NOT NULL,
authority_flag tinyint NOT NULL
CONSTRAINT ONE_DEFAULT3438
DEFAULT 1
CONSTRAINT Binary_flag_rule671
CHECK (authority_flag IN
(0, 1)),
observation_value decimal_datatype NOT NULL,
interpolated_flag tinyint NOT NULL
CONSTRAINT ZERO_DEFAULT6928
DEFAULT 0
CONSTRAINT Binary_flag_rule672
CHECK (interpolated_flag IN
(0, 1)),
time_created smalldatetime NOT NULL
CONSTRAINT
CURRENT_DATE_DEFAULT1809
DEFAULT getdate(),
CONSTRAINT XPKcs_working_other2
PRIMARY KEY NONCLUSTERED (formula_id, submission_id,
node_id, reference_year, observation_period)
--ON "INDEXES"
)
--ON "WORKING"
go

CREATE UNIQUE CLUSTERED INDEX XAK1cs_working_other2 ON
dbo.cs_working_other2
(
submission_id ASC,
formula_id ASC,
node_id ASC,
authority_flag ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
go

CREATE INDEX XIE1cs_working_other2 ON dbo.cs_working_other2
(
node_id ASC,
authority_flag ASC,
formula_id ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
--ON "INDEXES"
go

drop table dbo.cs_working_p1q12
go
CREATE TABLE dbo.cs_working_p1q12 (
formula_id int NOT NULL
CONSTRAINT formula_id_p1q114
CHECK (formula_id between
3001 and 7000),
submission_id int NOT NULL,
node_id int NOT NULL,
reference_year smallint NOT NULL,
observation_period datetime NOT NULL,
authority_flag tinyint NOT NULL
CONSTRAINT ONE_DEFAULT3439
DEFAULT 1
CONSTRAINT Binary_flag_rule673
CHECK (authority_flag IN
(0, 1)),
interpolated_flag tinyint NOT NULL
CONSTRAINT ZERO_DEFAULT6929
DEFAULT 0
CONSTRAINT Binary_flag_rule674
CHECK (interpolated_flag IN
(0, 1)),
observation_value decimal_datatype NOT NULL,
time_created smalldatetime NOT NULL
CONSTRAINT
CURRENT_DATE_DEFAULT1810
DEFAULT getdate(),
CONSTRAINT XPKcs_working_p1q12
PRIMARY KEY NONCLUSTERED (formula_id, submission_id,
node_id, reference_year, observation_period)
--ON "INDEXES"
)
--ON "WORKING"
go

CREATE UNIQUE CLUSTERED INDEX XAK1cs_working_p1q12 ON
dbo.cs_working_p1q12
(
submission_id ASC,
formula_id ASC,
node_id ASC,
authority_flag ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
go

CREATE INDEX XIE1cs_working_p1q12 ON dbo.cs_working_p1q12
(
node_id ASC,
authority_flag ASC,
formula_id ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
--ON "INDEXES"
go

drop table dbo.cs_working_pq2
go
CREATE TABLE dbo.cs_working_pq2 (
formula_id int NOT NULL
CONSTRAINT formula_id_pq14
CHECK (formula_id between
1001 and 3000),
submission_id int NOT NULL,
node_id int NOT NULL,
reference_year smallint NOT NULL,
observation_period datetime NOT NULL,
authority_flag tinyint NOT NULL
CONSTRAINT ONE_DEFAULT3440
DEFAULT 1
CONSTRAINT Binary_flag_rule675
CHECK (authority_flag IN
(0, 1)),
interpolated_flag tinyint NOT NULL
CONSTRAINT ZERO_DEFAULT6930
DEFAULT 0
CONSTRAINT Binary_flag_rule676
CHECK (interpolated_flag IN
(0, 1)),
observation_value decimal_datatype NOT NULL,
time_created smalldatetime NOT NULL
CONSTRAINT
CURRENT_DATE_DEFAULT1811
DEFAULT getdate(),
CONSTRAINT XPKcs_working_pq2
PRIMARY KEY NONCLUSTERED (formula_id, submission_id,
node_id, reference_year, observation_period)
--ON "INDEXES"
)
--ON "WORKING"
go

CREATE UNIQUE CLUSTERED INDEX XAK1cs_working_pq2 ON
dbo.cs_working_pq2
(
submission_id ASC,
formula_id ASC,
node_id ASC,
authority_flag ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
go

CREATE INDEX XIE1cs_working_pq2 ON dbo.cs_working_pq2
(
node_id ASC,
authority_flag ASC,
formula_id ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
--ON "INDEXES"
go
----- create view ---------
drop view cs_working2
go
CREATE VIEW cs_working2 (submission_id, node_id, reference_year,
observation_period, formula_id, observation_value, interpolated_flag,
time_created, authority_flag) AS
SELECT we.submission_id, we.node_id, we.reference_year,
we.observation_period, we.formula_id, we.observation_value,
we.interpolated_flag, we.time_created, we.authority_flag
FROM cs_working_e2 we
union all
SELECT wo.submission_id, wo.node_id, wo.reference_year,
wo.observation_period, wo.formula_id, wo.observation_value,
wo.interpolated_flag, wo.time_created, wo.authority_flag
FROM cs_working_other2 wo
union all
SELECT wpq.submission_id, wpq.node_id, wpq.reference_year,
wpq.observation_period, wpq.formula_id, wpq.observation_value,
wpq.interpolated_flag, wpq.time_created, wpq.authority_flag
FROM cs_working_pq2 wpq
union all
SELECT wp1q1.submission_id, wp1q1.node_id, wp1q1.reference_year,
wp1q1.observation_period, wp1q1.formula_id, wp1q1.observation_value,
wp1q1.interpolated_flag, wp1q1.time_created, wp1q1.authority_flag
FROM cs_working_p1q12 wp1q1
union all
SELECT wi.submission_id, wi.node_id, wi.reference_year,
wi.observation_period, wi.formula_id, wi.observation_value,
wi.interpolated_flag, wi.time_created, wi.authority_flag
FROM cs_working_indexes2 wi
go

--- sample selects against partitioned view -----
/*
--run explain plan here and see all 5 partitions being pulled
select * from cs_working

--run explain plan here and see just the 1 partition
select * from cs_working_e2

--run explain plan and see this is not pruning to the needed partition
select * from cs_working
where formula_id = 1

--run explain plan and see it is now pruning to the needed partition
select * from cs_working
where formula_id = 1
and submission_id is not null

--run explain plan and see it is now pruning to the needed partition,
too
select * from cs_working
where formula_id = 1
and observation_value is not null
*/

[Erland Sommarskog]

[posted and mailed, please reply in news]

SM (mu************@hotmail.com) writes:

problem: Trying to get partitioned views to "prune" unneeded partitions
from select statements against the partitioned view. There are 5
partitioned tables. Each with a check constraint based on a range of
formula_id column.

Test: Run this script to create the 5 partitioned tables and the
partitioned view. Then run the explain plans on the select statements at
the end of the script and see that we can only prune if we give a
seemingly superfluous is not null criteria in addition to the
formula_id.

I looked this, and indeed the behaviour is surprising. However, it is
benign. If you look closer at the plans, you see that there is a filter
which includes a STARTUP EXPR. What this is all about is that before
doing anyhing else, SQL Server filters on the partitoning column.

If you populate the table with some data, and run a query with
SET STATISTICS IO ON, you will something like this:

Table 'c'. Scan count 1, logical reads 2, physical reads 0, read-ahead
reads 0.
Table 'b'. Scan count 0, logical reads 0, physical reads 0, read-ahead
reads 0.
Table 'a'. Scan count 0, logical reads 0, physical reads 0, read-ahead
reads 0.

Thus, only one of the table is accessed.

So while it may not seem so at first glance, you do actually achieve
your goal of partitioning the column.

It seems that this happens because there more than column in the primary
key of the tables.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp