title: multiple string search in oracle
body: <p>I have a list of words I need to search for (say a few thousand entries of varchar2 of no more than 30 characters), I need to search for the presence of these words in sentences (say about a hundred million entries of varchar2 of no more than 256 characters). I would like to get the id of the text with a least one word matching and ideally a list of indexes giving the positions of the searched words.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>searched words</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>pluto</td>
  </tr>
  <tr>
  <td>2</td>
  <td>jupiter</td>
  </tr>
  </tbody>
  </table>
  </div><div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>sentences</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>we go back to earth</td>
  </tr>
  <tr>
  <td>2</td>
  <td>we discover pluto and jupiter</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>would give back the results</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>minimum results</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2</td>
  </tr>
  </tbody>
  </table>
  </div><div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ideal results</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2, ( (1, 13), (2, 23))</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>While this is something that can be developed, it feels like it is a common SQL request. Hence I wonder if there are best practices to do it or even better if there is a dedicated function in oracle SQL starting from 19c or PL/SQL that would do such a thing in an efficient way.
  It seems that Oracle Text <code>CONTAINS</code> and <code>ACCUMulate</code> would work but I am not sure I can use Oracle Text in my context and if this would be typically slower or faster than a pure SQL PL/SQL request.</p>