Re: Phone Numbers

Ed Stevens <nospam_at_noway.nohow> wrote in message news:<1qa9sv8s8m03q7hq07tfeq38iv6mudrbvu_at_4ax.com>...
> On Wed, 26 Nov 2003 00:21:07 -0500, v5034 <v5034_at_vm.vm.co> wrote:
>
> >Richard wrote:
> >
> >> Do you need to search on the whole phone number, e.g. to identify an
> >> individual subscriber or do you just need to identify the 'location' of the
> >> phone number, e.g. to identify which exchange or charge group it is
> >> associated with?
> >
> >I need to search the whole number just to see if it exists
> >(do-not-call-list). I would prefer to keep area code in a separate
> >column though - unless you think this is a bad idea. I also do not have
> >Oracle EE, just SE - so I don't have access to bitmapped indexes. Also
> >there are no updates and very rare deletes. So mostly (98%) reads.
> >
> >
> >>
> >> Regards,
> >>
> >> Richard
> >>
> >> "v5034" <v5034_at_vm.vm.co> wrote in message
> >> news:vs80klekuf1pb3_at_news.supernews.com...
> >>
> >>>What is the best way to store, index and search N. American phone
> >>>numbers for maximum search efficiency? Should they be broken up and
> >>>stored as area code columns and number columns? How should they be
> >>>indexed? I have a standard Oracle database - not EE.
> >>>How should they be searched for efficiency?
> >>>
> >>>Thanks
> >>>
> >>
> >>
> >>
> I see nothing gained by separating the area code. Is there any other
> data associated with the telephone number or is it truly a simple
> (though large) list of numbers - a single column table?
>
> Looks to me like a perfect case for an Index Oraganized Table.

I agree 100% with Ed. A single column Index-organized table is the way to go. This is what we did to create our Do Not Call list for recently enacted rules. We have the national DNC list and the DNC from several states where we do business. We use UNIX tools to merge and sort the input files. Then we use SQL*Loader with the direct path option to load the table. We created a tablespace specifically for this table and made everything involved 'nologging'. We truncate and load the table on a periodic basis. The entire load process takes around 5 minutes to load 60,000 rows. Theer is some time spent preparing the load files, but the amount of time the information in the database is unavailable is minimal and this process easily fits into our maintenance window.

HTH,
Patrick

PS. We keep a seperate table for those customers who request that we do not call them, but who are not on one of the other DNC Lists. Received on Wed Nov 26 2003 - 13:26:36 CST