Function based index
I simply hate it when I see join conditions like this
WHERE table1.column1= UPPER(table2.column2)
If we build the systems ourselves, why can’t we simply ensure that the two columns have consistent data, especially if we are responsible in building application, from the ETL part, processing part all the way to the REPORTING part.
If table2.column2 is indexed, the indexed will be ignored in the above join.
To get around it, you need to create a function based index on table2
CREATE INDEX TABLE2_IX01 on TABLE2(UPPER(COLUMN2));
There is no such thing as free lunch though, there is an overhead to using function based index. It’s more costly to create than simple b-tree indexes.
While we are on function based indexes, my friend Jimbo has this great trick
Common problem - you’ve got a zillion row table of trades to process, with a status flag set to ‘PENDING’ in 10 cases and other values in the almost-a-zillion. You want the special rows.
Maintaining a convention B-tree index blows as the selectivity is so poor for N. So, how do you do it? I would have always gone for list partitioning with row movement until I devised this trick…
Use a CASE statement and a functional index to create an index in which the vast majority of entries are NULL - and so don’t appear. The index is thus tiny.
CREATE INDEX trade_trade_status_fidx ON trade ( CASE WHEN trade_status = ‘PENDING’ THEN trade_status ELSE NULL END );
Now select based on the condition that you indexed, it’ll use the index and it’ll be very fast.
SELECT * FROM trade
WHERE ( CASE WHEN trade_status = ‘PENDING’ THEN trade_status ELSE NULL END
) = ‘PENDING’;