I have a table whcih has 3 billion records and 8 columns in it(One of them is datetime).This table is being loaded from 4 different tables. Since this table has 3 billion records, we are having hard time to read data from the table. What is the best way to design this table? Since one of the field is DATETIME, i am thinking that it is not a good idea to create a partition on DATETIME field. Any ideas are appreciated.
If your queries are always by date or date range then a clustered index on the datetime column may solve your performance issues. Partitioning is an option but you first should identify all the common access points (queries) used on the table. Most likely a good clustering choice is all you need.
If you post the DDL including keys and indexes plus an example of the most common queries then I'm sure you'll get some good suggestions.
I have a table whcih has 3 billion records and 8 columns in it(One of them is datetime).This table is being loaded from 4 different tables. Since this table has 3 billion records, we are having hard time to read data from the table. What is the best way to design this table? Since one of the field is DATETIME, i am thinking that it is not a good idea to create a partition on DATETIME field. Any ideas are appreciated.
Thanks.
Partitioning doesn't help performance for the most part. Having the correct indexes will. Post the CREATE TABLE statement along with any CREATE INDEX statements for the table so someone can make a recommedation.,
Design 3 billion records table
Posted - 11/09/2012 : 11:54:58
