Set up Oracle Text to index values of multiple columns in Oracle tables

oracle indexing oracle-text
JoshL picture JoshL · Feb 21, 2009 · Viewed 7.2k times · Source

I have a set of Oracle tables that describe information about property owners. Owner names and other text values are stored in multiple fields in multiple related tables, for each owner. I would like to index the contents of these fields. My goal is to provide a single field where a user can enter keywords to locate owners.

How do I set up Oracle Text to accomplish this?

Answer

Nick picture Nick · Feb 22, 2009

You'll just need to create a multiple datastore preference and pass it in as a parameter to the index.

begin
ctx_ddl.create_preference('my_multi', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('my_multi', 'columns', 'column1, column2, column3');
end;


create index myindex on mytable(docs) 
  indextype is ctxsys.context 
  parameters ('DATASTORE my_multi');

http://download.oracle.com/docs/cd/B19306_01/text.102/b14217/ind.htm#sthref281

Related questions

Oracle: Full text search with condition

I've created an Oracle Text index like the following: create index my_idx on my_table (text) indextype is ctxsys.context; And I can then do the following: select * from my_table where contains(text, '%blah%') > 0; But …

Read More
Force index use in Oracle

I encountered this question in an interview and had no clue how to answer: There is a table which has a index on a column, and you query: select * from table_name where column_having_index="some value"; The query …

Read More
Oracle - Select where field has lowercase characters

I have a table, users, in an Oracle 9.2.0.6 database. Two of the fields are varchar - last_name and first_name. When rows are inserted into this table, the first name and last name fields are supposed to be in …

Read More