Oracle Text Question

Oracle 10g V2 on Linux.

I have a contacts database with contact, address, phone number, email, contact activity (etc) tables. The users want a global search that will print a list of contacts where a specific keyword (say 'wellstone') occurs in a large list of attributes, say contact first name, last name, address, notes (and about five other contact fields), contact email, phone number or contact activity notes. I tried the SQL with LIKE '%wellstone%' with a bunch of ORs. It worked but it was extremely slow. Then I defined CONTEXT indexes on each column with statements like the following...

CREATE INDEX PIJ.CON_FIRST_NAME
    ON PIJ.CONTACT(CON_FIRST_NAME) INDEXTYPE IS CTXSYS.CONTEXT     PARAMETERS ('');
CREATE INDEX PIJ.CON_LAST_NAME
    ON PIJ.CONTACT(CON_LAST_NAME) INDEXTYPE IS CTXSYS.CONTEXT     PARAMETERS (''); I tried the following syntax and realized that it does not work. Only one CONTAINS is allowed in the where clause.

   AND (

        contains(con_first_name, 'wellstone', 1) > 0)
     OR contains(con_last_name, 'wellstone', 1) > 0)
     OR contains(con_alt_first_name, 'wellstone', 1) > 0)
      )

I got around the problem by changing the query as follows... It works, but is extremely slow.

   AND (
        con_id in (select con_id from contact where contains(con_first_name,
'wellstone', 1) > 0)

     OR con_id in (select con_id from contact where contains(con_last_name,
'wellstone', 1) > 0)

     OR con_id in (select con_id from contact where contains(con_alt_first_name, 'wellstone', 1) > 0)

      )

How can I build an efficient global query, with or without Oracle TEXT?

Thanks!
- Rajesh Received on Wed Apr 12 2006 - 11:23:22 CDT