andyrobins
Technical User
I have come across a rather odd problem with partitioned views and was hoping that someone could shed some light on it.
If you have, for example, 4 base tables for each quarter's sales data and these are partitioned using a datetime field that is part of the primary key and using the constraint >='2005-01-01' and < '2005-04-01' then some weird things happen with the execution plans.
If you execute
select * from accounts
where act_timestamp >= '2005-01-02 00:00:00.000' and act_timestamp <= '2005-01-03 12:31:00.000'
you get access to all tables in the partition. (this is proved by the actual execution plan and the statistics IO/TIME options)
But, if you execute
select * from accounts
where act_timestamp IN ('2005-01-02 00:00:00.000' , '2005-01-03 12:31:00.000')
then it will work out which subtable it needs and provide a much more sensible plan.
Is there some limit to using ranges or logical operators within a partitioned query, or am I just being a bit of a fool and doing something wrong? (I suspect the latter!!
)
Thanks in advance
Andy
If you have, for example, 4 base tables for each quarter's sales data and these are partitioned using a datetime field that is part of the primary key and using the constraint >='2005-01-01' and < '2005-04-01' then some weird things happen with the execution plans.
If you execute
select * from accounts
where act_timestamp >= '2005-01-02 00:00:00.000' and act_timestamp <= '2005-01-03 12:31:00.000'
you get access to all tables in the partition. (this is proved by the actual execution plan and the statistics IO/TIME options)
But, if you execute
select * from accounts
where act_timestamp IN ('2005-01-02 00:00:00.000' , '2005-01-03 12:31:00.000')
then it will work out which subtable it needs and provide a much more sensible plan.
Is there some limit to using ranges or logical operators within a partitioned query, or am I just being a bit of a fool and doing something wrong? (I suspect the latter!!
)Thanks in advance
Andy