Clean your data

Whenever I am helping people create tables, I always try to get the data as clean as possible before it is inserted. There all several different degrees of cleaning, but what I look for is putting things in a usable format. For example if you find yourself always having to do a TRIM or an UPPER function on field in your where statement then that data probably should have been cleaned before it got inserted. A simple function on their indexed column makes that index useless.

Something like this would never use a standard index.

Select * from participant
where upper(name) like 'SMITH, JOHN%'

If I can't track down all the code doing the data modificiations on the table then I will use table triggers. This will trim or same case the data for the table. It is much better to not use triggers if you can get away with it. While triggers will get the job done, people often forget about them and they become the magic behind the scenes. This can lead to unintended conconsequences too. Like slowing down any bulk operations you do against that table.

However, sometimes you don't have the ability to change the table data. If this is the case then I will consider building a functioned based index. Since Oracle 8i, Oracle has had the ability to create an index based on a function.

I'll create a simple test
create table
foobar
as select *
from all_objects;

create index foobar_idx1 on foobar (object_name);

BEGIN
dbms_stats.gather_table_stats(ownname=>'XXXXXX',
tabname=>'FOOBAR',estimate_percent=>100,cascade=>TRUE);
END;

Here is my normal query
select * from foobar where object_name like 'BLAH%'

0 SELECT STATEMENT 2 188
1 TABLE ACCESS BY INDEX ROWID FOOBAR 2 188 *
2 INDEX RANGE SCAN FOOBAR_IDX1 2

But lets say, I have to upper object_name because it has mixed case in it

select * from foobar where upper(object_name) like 'BLAH%'

Now I am back to a full table scan. Ouch.

0 SELECT STATEMENT 2589 237K 166 (3) *
1 TABLE ACCESS FULL FOOBAR 2589 237K 166 (3)

Now I will create a function based index.

create index foobar_idx2 on foobar (upper(object_name));

BEGIN
dbms_stats.gather_table_stats(ownname=>'XXXXXX',
tabname=>'FOOBAR',estimate_percent=>100,cascade=>TRUE);
END

0 SELECT STATEMENT 2 196
1 TABLE ACCESS BY INDEX ROWID FOOBAR 2 196 *
2 INDEX RANGE SCAN FOOBAR_IDX2 2

Now its using an index. Things should be much faster now.