oracle, MULTI_COLUMN_DATASTORE, doesn't use index for consecutive columns
<p>i have a table with 2 columns, Name and UniqueName. 
I created Multi_column_datastore:</p>

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

<p>PL/SQL procedure successfully completed.</p>

<p>I created the text index like this:</p>

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

<p>Index created.</p>

<p>Now, if i query on UniqueName:</p>

<pre><code>SELECT count(1) FROM Supplier WHERE CONTAINS (UniqueName , '10') &gt; 0) 
</code></pre>

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

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

<p>I also tried creating two separate text indexes one on Name and one on UniqueName.</p>

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

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

<p>Please let me know how to achieve?</p>

<p>thank you</p>