Fact table partioining and indexing

View previous topic View next topic Go down

Fact table partioining and indexing

Post  jtigger on Wed Jun 13, 2012 6:51 am

Hi,
I am looking for some help and advise on my fact table.
I have a large fact table on sql2012. I have partioned this by a date. I also have indexes on this table aswell.
Currently my nightly extract inserts my data into staging tables and then drops the indexes and inserts my data into my fact table and then recreates the indexes.
I am new to table partioining and was wanting some advise on my nightly extract and inserting data into a partioned table?.There are different types of data scenarios which also add to the complexity.
The first is we receive fresh data i.e. data for the current year. Would this require a change to my script to ensure data is inserted into the correct partion?, if so how can this be done?.
The second scenario is data that is older than the current year may get updated. How would i update this type of data in a partioned table?.
I am also finding that although i have indexed my table. When i create and drop the indexes it takes hours to rebuild. I was looking to find out what is the best way to deal with indexes when inserting data into a fact table?.
Thank You
JTigger

jtigger

Posts : 2
Join date : 2012-04-27

View user profile

Back to top Go down

Re: Fact table partioining and indexing

Post  BoxesAndLines on Wed Jun 13, 2012 9:00 am

Your indexes should be partitioned as well. You should only build indexes on the new partition. The old ones do not need to change. Also look at the partition swap functionality to manage your partitions.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum