Create Function Based Index on Oracle Table

Function based index is a very useful tool. I took advantage of it when I was involved in a project to deploy predictive models into a production Oracle database. The are two requirements:

1. I needed to write about two million records of two columns, account_num (varchar2(19)) and a code (varchar2(4)) into a table.
2. In production, we need to quickly find the code for each account number.
There is one restriction.
1. There is only one column col_xyz( varchar2(32)) in a table tbl_abc that we can write to.

What I did was to concatenate the account_num and code columns and store them in the column col_xyz. I then create function based index on the first 19 characters of col_xyz, the account_num. The following are SQL scripts involved.
My original table looks like the following.

SQL> select * from MY_TABLE where rownum <5;

ACCOUNT_NUM                            CODE
-------------------------------------- ----
AAAAAAAA00000000984                    3045
AAAAAAAA00000001421                    3045
AAAAAAAA00000002644                    3045
AAAAAAAA00000004569                    3045

I concatenate account_num and code and put them into tbl_abc as column col_xyz.

SQL> insert into tbl_abc (col_xyz) select account_num||code from MY_TABLE;

SQL> select * from tbl_abc where rownum <5;

COL_XYZ
--------------------------------
AAAAAAAA000000009843045
AAAAAAAA000000014213045
AAAAAAAA000000026443045
AAAAAAAA000000045693045

I create function based index on the first 19 characters of col_zyx, the account_num.

SQL> create index tbl_abc_idx on tbl_abc(substr(col_xyz,1,19));

Index created.

Once the function based index is created on substr(col_xyz,1,19), the following query that finds the code for account_num is very fast.

SQL> select col_xyz, substr(col_xyz,1,19) account_num, substr(col_xyz, 20,4) code from tbl_abc where substr(col_xyz,1,19)='AAAAAAAA00000000984';

COL_XYZ                          ACCOUNT_NUM         CODE
-------------------------------- ------------------- ----
AAAAAAAA000000009843045          AAAAAAAA00000000984 3045

Please also see Table Joining Using Function Based Index.