The post Create Function Based Index on Oracle Table shows that indexes can be created based on functions. Here is an example of the actual use of function based index in table joining. In a project, I try to join the following two tables based on a unique sequence numbers: 1. Checks deposited, tbl_checks_deposited. 2. Checks that are returned, tbl_checks_returned. Both tables contain column sequence_ids that are unique for each record and are supposed to link these two tables. I used the following query to join them.
select a.*, b.return_code from tbl_checks_deposited a, tbl_checks_returned b where a.sequence_id=b.sequence_id;However, I did not get many successful matches. After some investigation, I have found that the sequence_id in the second table tbl_checks_returned has extra leading and trailing blanks. This could happen when the data is loaded from text files into an Oracle database. I used trim() function to remove the leading and trailing blanks and redo the join using the following query. To make the join faster, I first create a function based index on table tbl_checks_returned.
create index tbl_checks_returned_idx on tbl_checks_returned(trim(sequence_id)); select a.*, b.return_code from tbl_checks_deposited a, tbl_checks_returned b where a.sequence_id=trim(b.sequence_id);Now the join returns the correct number of records.