Re: cross reference index

In article <9062v1$h0c_at_dispatch.concentric.net>,   "Jim Poe" <jpoe_at_fulcrumit.com> wrote:
> We have a legacy database application that is being moved to Oracle.
 In the
> legacy application we had a cross reference type of index. It would
 index
> on each word in a text field. This allowed us to search for records
 using
> operators on individual words. For instance, a field called
 COMPANY_NAME
> may contain the value 'ACME Freight & Trucking'. We could search for
 this
> record by 'SELECT COMPANY_NAME FROM COMPANY WHERE
 COMPANY_NAME.XREF='ACME'
> AND COMPANY_NAME.XREF LIKE 'Truck%'.
>
> This feature was built into the database engine.
>
> Is there any way to emulate this in Oracle?
>
> --
> Jim Poe ( jpoe_at_fulcrumit.com )
>
>

I suppose that your COMPANY_NAME may contain the value 'The ACME Freight & Trucking', you would like to look it up by 'ACME' & 'TRUCK'.

There are two approachs.

One is to write the query as desired, company_name like '%ACME% TRUCK%'. This could be slow if the table is large (100,000+ rows) or your machine is slow. Also, this would not find the transposed version of this name eg 'Freight & Trucking, ACME'.

One could automate this for the user -- parsing out spaces and converting then into '%'. Not hard programming.

I have written code that did lookup based on the initial letters --> looking for 'ACME' as company_name like 'A%C%M%E%'. Again for small tables (~10,000 rows) the database was more than fast enough especially if the company_name was part of an index. Again not hard programming in forms.

Approach two is to generate the company word index table/index. You can create the rows by doing the parsing and inserting the data into the table. You might want to develop triggers -- to add/update/delete rows in the index table when you add/update/delete company rows. Not hard programming -- but a little bit of work.

By the way what database has this cross referencing built-in?

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rosetta.org
              Ususual disclaimers


Sent via Deja.com http://www.deja.com/
Before you buy.