Home » Server Options » Text & interMedia » Multi Column Datastore
Multi Column Datastore [message #110453] Mon, 07 March 2005 15:00 Go to next message
verynewtothis
Messages: 11
Registered: March 2005
Location: Washington DC Metro Area
Junior Member
I already have a CONTEXT index using a direct datastore on one column of data. I want to try creating a CONTEXT index on the same data, but I would like to index that data on more than one column (i.e. using a multi column datastore).

Q1) I know that I need to preceed index creation with the following code, for example:

begin
ctx_ddl.create_preference('my_alias', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('my_alias', 'column', 'column1 "column1", column2 "column2", column3 "column3"');
end;

But when I create the index and specify which column to index the table on, do I use 'my_alias' as the column name during index creation

i.e. CREATE INDEX ex_idx ON table1('my_alias')...

Q2) Then, to utilize the multiple columns that, would hopefully be indexed on, I am wondering if within a CONTAINS clause that I can list the columns that are indexed that I want to query against and the parameter I am searching for

i.e. WHERE CONTAINS(table1.name, 'ralph', table1.age, '25', table1.lastName, 'smith') > 0

IS this the correct syntax, or can you point me to what would be the correct syntax?
Re: Multi Column Datastore [message #110456 is a reply to message #110453] Mon, 07 March 2005 15:31 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
First, you want to create the preference like:

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


Note that 'columns' needs to be plural and you don't need the column aliases. Also, I wouldn't recommend calling the preference 'my_alias' - you are naming a datastore preference here, it has nothing to do with an alias.

When creating the index:

create index index_name
  on table_name(column1)
  indextype is ctxsys.context
  parameters ('datastore ctxsys.my_alias');


Note that you will need to create a trigger to do a fake update to column1 so that changes to column2 and 3 get included in the index.

create or replace trigger t_trg
before update of column2, column3 on t
for each row
begin
  :new.column1 := :new.column1;
end;
/


When querying:

select *
  from t
 where contains(column1, 'search_text') > 0;


This query will return any rows where the search_text appears in any of the three columns.
Re: Multi Column Datastore [message #110464 is a reply to message #110456] Mon, 07 March 2005 16:16 Go to previous messageGo to next message
verynewtothis
Messages: 11
Registered: March 2005
Location: Washington DC Metro Area
Junior Member
What is the trigger for? Is it used for writing data? My data will be static.
Re: Multi Column Datastore [message #110467 is a reply to message #110464] Mon, 07 March 2005 16:29 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
If your table is static after you create the index, then you don't need to include the trigger, but it is a necessary piece for any table with a multi-column datastore and updates to columns in the datastore but not included in the create index statement.
Previous Topic: splitting
Next Topic: iFS Text Search Query
Goto Forum:
  


Current Time: Thu Mar 28 11:14:03 CDT 2024