Showing posts with label Oracle function based index. Show all posts
Showing posts with label Oracle function based index. Show all posts

Monday, December 17, 2018

Oracle Function Based Index is Handy

In table t_my_table, column name is in lower case. However, I want to join this table with another table where names are in upper cases. I create a function based index.
create index t_my_tablei on t_my_table(upper(name));
That way, I don't to create another column or table that contains upper(name) and create index on it. When I join the two tables based on upper(a.name) = b.name, function based index upper(a.name) is used and it is fast.
select a.*, b.* from t_my_table a, my_another_table b where upper(a.name) = b.name;

Wednesday, April 09, 2014

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.