Case Study: Do-Not-Call List

On a private forum, a discussion came up as to the optimal way to store the U.S. National "Do-Not-Call" list in and RDBMS.

[ For those out of the US and who
don't pay attention to U.S. news, this is a list of 40 million nine-digit telephone numbers. Number are divided into three segments: a three-digit area code that is indicative of their geographic areas (mostly an area has one area code, but some have two or three overlapping ones) a prefix that indicates their local exchange, although those aren't well-defined in many cases, and then four addition digits which have no meaning other than a unique identifier. Not all area codes are used and many have special meaning. Area codes are not geographically distributed, i.e. 5-- area codes are not all in the midwest. AFAIK, area codes don't cross state boundaries.]

This list must be checked against by telemarketers before calling the number in question.

I imagine this list would be used in one of three ways:
+checked for a specific number prior to a phone call
+anti-joined against call list to remove numbers permanently from call list
+anti-joined against call list for the specific block of numbers on a
 telemarketer's daily list

I imagine most telemarketers do numbers near to each other during blocks of calling time, although I don't know the business that well.

List are updated quarterly. As far as I know, no numbers are automatically eliminated from the list, although I'm not sure if that's been established.  

So, given such a simple table...what would you all recommend doing with it? The choices that could make some sense are:

+One number column standard heap table with PK on number
+Two number column standard heap table with area code and rest of number
+One number IOT
+Partitioned table by area code (~200 partitions)
+Partitioned table by state (List partitioned)

My gut reaction was to go with the first form, and possibly put it on a read-only tablespace.

What do you think? I'm not supposed to get the list legally because I'm not a telemarketer...

Jer Received on Sun Oct 26 2003 - 13:42:10 CST