August 29, 2009 at 10:17 am
I have read that using partitioned views can significantly improve performance in place of extremely large tables, so I gave it a go.
I created the member tables and table check constraints I set are like this
TABLE 1 : substring(CLIENT_ID,4,7) < 200000
TABLE 2 : substring(CLIENT_ID,4,7) >= 200000 and substring(CLIENT_ID,4,7) < 400000
TABLE 3 : substring(CLIENT_ID,4,7) >= 400000 and substring(CLIENT_ID,4,7) < 600000
etc....
(n.b. CLIENT_ID is of the form XYZ123567)
I have created the view with UNION ALL for each table.
Here is a sample query on the new view:
SELECT * FROM PARTITIONED_VIEW WHERE CLIENT ID = 'CAV0012345'
I had expected to see data only being pulled from one table, but it actually looks through them all.
I have tried another test where the check contraint column is an int (CLIENT_ID < 200000 etc), and it works fine, so I guess the problem in my original attempt must be to do with the substring function? Can any explain the rules that govern what will and won't work in this situation.
August 29, 2009 at 4:08 pm
I think I've found the answer in another forum. It appears that the problem is a result of using a function in the check constraints.
A partitioning column exists on each member table and, through CHECK constraints, identifies the data available in that specific table. Partitioning columns must adhere to these rules:
* Each base table has a partitioning column whose key values are enforced by CHECK constraints. The key ranges of the CHECK constraints in each table do not overlap with the ranges of any other table. Any given value of the partitioning column must map to only one table. The CHECK constraints can only use these operators: BETWEEN, AND, OR, <, , >=, =.
Because your check constraint uses the expression datepart(mm,Col2), it is not a check on Col2 using only BETWEEN, AND, OR, <, , >=, and =.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply