Welcome to

www.BillHulsizer.com !!!

 

26 billion rows in one table

 

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 table?

Very carefully...

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. 

~~~~~

 

 

Some of my more recent thoughts:

~~~~~

Save billions per year in highway costs?

Here is my way...

~~~~~

Motivate kids to do better in school?

Here is my way...

~~~~~

 End the violence in the Mideast?

Here is my way...

 ~~~~~

 Health Insurance?

Here is my way...

~~~~~

 Thoughts of your own?

Email Bill