| Multi Column Datastore [message #110453] |
Mon, 07 March 2005 15:00  |
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  |
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 #110467 is a reply to message #110464] |
Mon, 07 March 2005 16:29 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
