Subscript Indexing in Oracle

Question

I just found that our current database design is a little inefficient according to the SELECT queries that we execute the most. IBANs are positional coordinates in accordance with national formats.

Since we mainly execute JOIN and WHERE on the exact substring of IBAN columns in some tables, my question is about assigning the index to the substring (s) of the column

Are we forced to add redundant and indexed columns to the table? I.e. add the columns NATION_CODE , IBAN_CIN , IT_CIN , IT_ABI , IT_CAB , IT_ACCOUNT (where IT_ fields are only considered for accounts starting in ITXX), each of which has a corresponding secondary indexing or is there any special type of secondary index that can only be applied under a substring of a column?

The first solution can make the database more complicated, since IBAN accounts are used throughout the DBMS (and, obviously, I do not have full control over the design).

thanks

[Edit] Typical request

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

Retrieves all payment delegations where the target account belongs to any of the banks that match CONDITION. Must 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 contains the bank code in numbers [6-11]

Answer 1

You are looking for a functional index :

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

Answer 2

If you are using Oracle 11g, you can look at the virtual columns and then index them. This is pretty much equivalent to Rene's answer, but can lead to more formality around data usage, so only the correct characters will be used.

Virtual columns will not take up extra space in the database, although any index you create in this virtual column will be