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;
Post a Comment