26 billion rows in one
A client has a table with 5 billion rows in it and
is currently growing to 9 billion rows. This will be seven years of week-ending history of all movement
of inventory into, within and out of all of their warehouses. And their legal department is probably
going to ask to keep it for 20 years, at least. Prevent one major lawsuit and it pays for itself many times
over. 20 years of history will be 26 billion rows in the largest table.
How do you put 26 billion rows into one
This data is history, so it never gets changed.
Once they reach their maximum size, old weeks need to be purged every week just before the new week is added.
And this table is one of 30+ tables that are either weekly, monthly or quarterly totals. And, most
importantly, most users work within one week's information when using this. Occasionally some
people will work with all weeks in a month or compare a week to a week in a prior month or year, but
most access is within one week.
We partitioned all the weekly tables on fiscal
year, then week. And the monthly tables on fiscal year, then month and so on. This largest table has
25,000,000 rows per week, so we partitioned it on ranges of its next index column within year and week. All
indexes have the partitioning columns as their first columns and are data partitioned secondary
indexes (DPSI). Each week, they add new partitions, right now. When they need to start purging old data,
they will rotate the oldest partitions to the newest ones every week. The same process will be done at
month-end and quarter-end for the monthly and quarterly tables.
These tables are all accessed via a Business
Objects universe. We also have tables that define the days in each week, weeks in each month, months in
each quarter and quarters in each year.