title: Substring-indexing in Oracle
body: <p>I just found that our current database design is a little inefficient according to the <code>SELECT</code> queries we perform the most. IBANs are positional coordinates, according to nation-specific formats.</p>
  
  <p>Since we mostly perform <code>JOIN</code>s and <code>WHERE</code>s on a precise substring of IBAN columns in some tables, my question is about assigning an index to the substring(s) of a column</p>
  
  <p>Are we forced to add redundant and indexed columns to the table? Ie. add columns <code>NATION_CODE</code>, <code>IBAN_CIN</code>, <code>IT_CIN</code>, <code>IT_ABI</code>, <code>IT_CAB</code>, <code>IT_ACCOUNT</code> (where the IT_ fields are considered only for accounts starting in ITXX) each one with appropriate secondary indexing <strong>or</strong> is there any special kind of secondary index that can be applied only on a substring of a column?</p>
  
  <p>The first solution could make the DB more complex since IBAN accounts are used all along the DBMS (and, obviously, I have no full control over design).</p>
  
  <p>Thank you</p>
  
  <p>[Edit] Typical query</p>
  
  <pre><code>SELECT * FROM DELEGATIONS WHERE SUBSTR(IBAN, 6, 5) IN (SELECT ABI FROM BANKS WHERE ANY_CONDITION)
  </code></pre>
  
  <p>Extracts all payment delegations where the target account belongs to any of the banks that match CONDITION. Should be changed to</p>
  
  <pre><code>SELECT * FROM DELEGATIONS WHERE SUBSTR(IBAN, 1, 2) = 'IT' AND SUBSTR(IBAN, 6, 5) IN (SELECT ABI FROM BANKS WHERE ANY_CONDITION)
  </code></pre>
  
  <p>to make sure that BBAN really holds the bank code in digits [6-11]</p>