 Multi-column index setup question [message #234447] |
Tue, 01 May 2007 11:32  |
gerrywhite
Messages: 3
Registered: April 2007
Location: Maryland
|
Junior Member |
 
|
|
First of all, this forum has been a great source of answers for my dive into Oracle Text. Thanks, all!
Here is my noob question:
First, I'm trying to create an index for a multi-column search.
I've created the MULTI_COLUMN_DATASTORE using the standard CTX_DDL code as shown below:
begin
ctx_ddl.create_preference('test_multi',MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('test_multi','columns','col1,col2');
ctx_ddl.set_attribute('test_multi','filter','N,Y');
end;
NOTE: col1 is VARCHAR2 and col2 is BLOB containing docs
And I've run code for my BASIC_LEXER and BASIC_STORAGE requirements.
Here is the CREATE INDEX statement I tried to use:
CREATE INDEX test_idx ON legacy_objects(col1,col2)
INDEXTYPE IS ctxsys.context
PARAMETERS('
DATASTORE test_multi
LEXER test_lexer
STORAGE test_storage
');
I got an ORA-29851 error, which I expected because of this line:
CREATE INDEX test_idx ON legacy_objects(col1,col2)
I'm sure I should only have one column in parentheses, but what?
Should I put the first of the multiple cols there? I can't find in the docs where it tells what to put in there when dealing with multiple-column indices for context searches.
Thanks in advance for any help.
- Gerry
|
|
|
|
| Re: Multi-column index setup question [message #234494 is a reply to message #234447] |
Tue, 01 May 2007 15:46  |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
|
You can create your index on any one column, like col1 or col2 or any other column in the table, whether it is in your multi_column_datastore or not. It will not affect what columns are indexed or searchable, because that is determined by the columns in the multi_column_datastore. What it does affect is what rows are synchronized. Only those rows where the indexed column has been updated will be synchronized. It is therefore a common practice to have a column like dummy varchar2(1) and add something to any update procedure that updates that column, even if all it does is set the new value to the same old value. You also need to tell it when to synchronize. If using Oracle 10g, you can add sync(on commit) to your parameters when creating your index to automatically synchronize the changed rows upon commit. If using Oracle 9i, you can create an after insert or update or delete statement level trigger that uses dbms_job.submit to run ctx_ddl.sync_index upon commit. You can also synchronize periodically or manually or alter and rebuild or drop and recreate. If you do not synchronize or alter and rebuild or drop and recreate, any changes are not indexed or searchable. If you do not update the indexed column, the change to those rows are not searchable even after you synchronize, unless you alter and rebuild or drop and recreate.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
