October 3, 2003 at 2:38 pm
Has anyone used the partitioned views in production? I have a 3 billion record, 190 GB data warehouse type table as
a single large table, that contains certain statistics over a period of time (7 years, so it has lots of historic data).
As you can imagine, this is hard to manage in terms of backups, reindexing, dbccc etc.
We arelooking into partitioning the table into several smaller tables each for i year woth of data.
I see that this can be done using the indexed views and instead of triggers. The daily load in to the table
consists of about a million records. Once I get the table partitioned, I would like to keep the inactive sections of
the table in a new database so we backup the inactive section just once every year when the tables in that DB change.
I would like to get opinions of people that used either the partitioned views or the instead of triggers for partitioning
a table. How is the performance. Do the insert fare OK? How are the data retrievals?
October 4, 2003 at 4:36 am
Informative article in BOL 2000 search on 'Using Partitioned Views'.
quote:
However, if the CHECK constraints have not been defined, the query optimizer must search all the tables instead of only those that cover the search condition on the partitioning column.
October 5, 2003 at 7:52 pm
Partitioned views are cool. you could put the data in 7 different tables or (7x12) different tables partitioned by Year and Month. Our partitioned view was not so huge as yours, so the performance was good. you have to test in your Dev environment for performance. CHECK constraints MUST be defined for partitioned views or else it doesn't make sense.
October 6, 2003 at 12:15 am
What do you guys mean with check constraints ? Hur du you have to set up the tables ?
February 14, 2005 at 2:24 am
Hi,
Partitioning view is really a good option. but while implementing in our scenario we are facing some problems such as we cannot create index on the partitioned views as it gives some error saying that "underlying tables have some constraints" and even we had the triggers on the table which we have decided to split butif create instead of triggers on the view it is creating a prob for the queries given below such as
"update a
set col1 = d.col1
from a , d
where a.col2= d.col2"
here a is a view and d is another table
so plz anyone canhelp me out to create index on the partitioned view and trigerrs nthe view
reagrds,
megha
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply