oracle, MULTI_COLUMN_DATASTORE, doesn't use index for consecutive columns

Question

i have a table with 2 columns, Name and UniqueName. I created Multi_column_datastore:

SQL> begin
  2  ctx_ddl.create_preference('my_multi', 'MULTI_COLUMN_DATASTORE');
  3   ctx_ddl.set_attribute('my_multi', 'columns', 'Name,UniqueName');
  4  end;
  5  /

PL/SQL procedure successfully completed.

I created the text index like this:

SQL> create index text_index on Supplier(Name) indextype is ctxsys.context parameters('datastore my_multi section group CTXSYS.AUTO_SECTION_GROUP SYNC (ON COMMIT)');

Index created.

Now, if i query on UniqueName:

SELECT count(1) FROM Supplier WHERE CONTAINS (UniqueName , '10') > 0)

I get ORA-20000 (Oracle Text error: DRG-10599: column is not indexed)

oracle picks up the text_index only for Name column. It doesn't pickup the index when i query UniqueName.

I also tried creating two separate text indexes one on Name and one on UniqueName.

When i query on UniqueName, though i dont' get error but i don't get any result.

My requirement is to either query on Name or UniqueName (not both). For both, i want to have text index.

Please let me know how to achieve?

thank you

score 0 · Answer 1 · answered Jul 29 '16 at 16:37

you can create index like following :

create index text_index on Supplier(Name) indextype is ctxsys.context parameters('datastore my_multi SYNC (ON COMMIT)');

following query will work in your case as you created index using column "Name":

SELECT count(1) FROM Supplier WHERE CONTAINS (Name, '10') > 0;

oracle, MULTI_COLUMN_DATASTORE, doesn't use index for consecutive columns

1 Answers1