Skip to main content
Database Administrators

Return to Question

added 78 characters in body
Source Link
Fastidious
Fastidious
  • 496
  • 1
  • 7
  • 15

I have a two part question really for anyone working with data warehousing where they have large fact tables.

Question 1

Lets say you have a table that has 500 million or more records in it that is clustered on time. You are only posting incremental records for the last 24 hours on a daily basis to this table.

How do you handle inserting delayed records that are a month or older to that table? Would you do nothing unless the insert caused a lot of fragmentation or would you attempt to drop the indexes, insert and rebuild?

I do not have enterprise edition available to me for table partitioning.

Question 2

If you have a fact table that is growing large like the above example, would it be wise to split the fact table up into multiple tables or would it be better to look towards adding additional files to the filegroup of said table if table partitioning is not an option?

Thanks in advance. I'm dealing with some large growth and trying to approach it the right way.

I have a two part question really for anyone working with data warehousing where they have large fact tables.

Question 1

Lets say you have a table that has 500 million or more records in it that is clustered on time. You are only posting incremental records for the last 24 hours on a daily basis to this table.

How do you handle inserting delayed records that are a month or older to that table? Would you do nothing unless the insert caused a lot of fragmentation or would you attempt to drop the indexes, insert and rebuild?

Question 2

If you have a fact table that is growing large like the above example, would it be wise to split the fact table up into multiple tables or would it be better to look towards adding additional files to the filegroup of said table if table partitioning is not an option?

Thanks in advance. I'm dealing with some large growth and trying to approach it the right way.

I have a two part question really for anyone working with data warehousing where they have large fact tables.

Question 1

Lets say you have a table that has 500 million or more records in it that is clustered on time. You are only posting incremental records for the last 24 hours on a daily basis to this table.

How do you handle inserting delayed records that are a month or older to that table? Would you do nothing unless the insert caused a lot of fragmentation or would you attempt to drop the indexes, insert and rebuild?

I do not have enterprise edition available to me for table partitioning.

Question 2

If you have a fact table that is growing large like the above example, would it be wise to split the fact table up into multiple tables or would it be better to look towards adding additional files to the filegroup of said table if table partitioning is not an option?

Thanks in advance. I'm dealing with some large growth and trying to approach it the right way.

Source Link
Fastidious
Fastidious
  • 496
  • 1
  • 7
  • 15

How can I handle delayed records a month or older on a table clustered on time?

I have a two part question really for anyone working with data warehousing where they have large fact tables.

Question 1

Lets say you have a table that has 500 million or more records in it that is clustered on time. You are only posting incremental records for the last 24 hours on a daily basis to this table.

How do you handle inserting delayed records that are a month or older to that table? Would you do nothing unless the insert caused a lot of fragmentation or would you attempt to drop the indexes, insert and rebuild?

Question 2

If you have a fact table that is growing large like the above example, would it be wise to split the fact table up into multiple tables or would it be better to look towards adding additional files to the filegroup of said table if table partitioning is not an option?

Thanks in advance. I'm dealing with some large growth and trying to approach it the right way.