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.