Substring-indexing in Oracle

Question

I just found that our current database design is a little inefficient according to the SELECT queries we perform the most. IBANs are positional coordinates, according to nation-specific formats.

Since we mostly perform JOINs and WHEREs on a precise substring of IBAN columns in some tables, my question is about assigning an index to the substring(s) of a column

Are we forced to add redundant and indexed columns to the table? Ie. add columns NATION_CODE, IBAN_CIN, IT_CIN, IT_ABI, IT_CAB, IT_ACCOUNT (where the IT_ fields are considered only for accounts starting in ITXX) each one with appropriate secondary indexing or is there any special kind of secondary index that can be applied only on a substring of a column?

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).

Thank you

[Edit] Typical query

SELECT * FROM DELEGATIONS WHERE SUBSTR(IBAN, 6, 5) IN (SELECT ABI FROM BANKS WHERE ANY_CONDITION)

Extracts all payment delegations where the target account belongs to any of the banks that match CONDITION. Should be changed to

SELECT * FROM DELEGATIONS WHERE SUBSTR(IBAN, 1, 2) = 'IT' AND SUBSTR(IBAN, 6, 5) IN (SELECT ABI FROM BANKS WHERE ANY_CONDITION)

to make sure that BBAN really holds the bank code in digits [6-11]

Answer 1

You're looking for a function based index:

create index ix_substring on TABLE (substr(COLUMN, 4, 9))