title: Oracle: Full text search with condition
body: <p>I've created an Oracle Text index like the following:</p>
  
  <pre><code>create index my_idx on my_table (text) indextype is ctxsys.context; 
  </code></pre>
  
  <p>And I can then do the following:</p>
  
  <pre><code>select * from my_table where contains(text, '%blah%') &gt; 0;
  </code></pre>
  
  <p>But lets say we have a have another column in this table, say <code>group_id</code>, and I wanted to do the following query instead:</p>
  
  <pre><code>select * from my_table where contains(text, '%blah%') &gt; 0 and group_id = 43;
  </code></pre>
  
  <p>With the above index, Oracle will have to search for all items that contain <code>'blah'</code>, and then check all of their <code>group_id</code>s.</p>
  
  <p>Ideally, I'd prefer to only search the items with <code>group_id = 43</code>, so I'd want an index like this:</p>
  
  <pre><code>create index my_idx on my_table (group_id, text) indextype is ctxsys.context; 
  </code></pre>
  
  <p>Kind of like a normal index, so a separate text search can be done for each <code>group_id</code>.</p>
  
  <p>Is there a way to do something like this in Oracle (I'm using 10g if that is important)?</p>
  
  <p><strong>Edit (clarification)</strong></p>
  
  <p>Consider a table with one million rows and the following two columns among others, <code>A</code> and <code>B</code>, both numeric. Lets say there are 500 different values of <code>A</code> and 2000 different values of <code>B</code>, and each row is unique.</p>
  
  <p>Now lets consider <code>select ... where A = x and B = y</code></p>
  
  <p>An index on <code>A</code> and <code>B</code> separately as far as I can tell do an index search on <code>B</code>, which will return 500 different rows, and then do a join/scan on these rows. In any case, at least 500 rows have to be looked at (aside from the database being lucky and finding the required row early.</p>
  
  <p>Whereas an index on <code>(A,B)</code> is much more effective, it finds the one row in one index search. </p>
  
  <p>Putting separate indexes on <code>group_id</code> and the text I feel only leaves the query generator with two options.</p>
  
  <p>(1) Use the <code>group_id</code> index, and scan all the resulting rows for the text.<br>
  (2) Use the text index, and scan all the resulting rows for the <code>group_id</code>.<br>
  (3) Use both indexes, and do a join.  </p>
  
  <p>Whereas I want:</p>
  
  <p>(4) Use the <code>(group_id, "text")</code> index to find the text index under the particular <code>group_id</code> and scan that text index for the particular row/rows I need. No scanning and checking or joining required, much like when using an index on <code>(A,B)</code>.</p>