Re: Word search in a database

In article <78ptnb$98q$1_at_nnrp1.dejanews.com>,   mmem_at_usa.net wrote:
> Hi,
>
> We wish to search for names inside a db. There is a single field in the
> database, which constains strings like "Dr. Davis, Bill and Irene". We want
 to
> match either "Dr. Davis", "Irese Weiss", "Weiss Irene", etc.
>
> The problem is that it is a hugh database (ten million records); Searching
> without some indexes is inpractical.
>
> Any suggestions ?

I haven't tried the ORACLE CONTEXT features. That might help. Otherwise you may have to search the DB at least once to build some word tables. If you know your searches are always for words (not parts of words), then you could build word index tables.

something like:
Create table my_names_index (
  word varchar2 (200),
  table varchar2 (100),
  loc char(18)
);

search each field/table you want. For example,

SELECT street, rowid into my_street, my_rowid  from address;

Split the field into words. then put them in the index table:

insert into my_names_index values (
  street_word,
  'ADDRESS',
  my_rowid );

then create the indices on my_names_index.

This is a really brief handwaving design. But this looks like a good approach to me. Contact me for more assistance.

Ed Prochak
Magic Interface, Ltd.
ORACLE services for LINUX and other platforms 440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Jan 28 1999 - 00:00:00 CET