I had a hard time creating a couple of indexes for some oracle tables. Things I thought for sure would work in SQL Server just would not work in oracle. Consider the following query:
select
slh_label_batch_id,
trunc(min(expected_ship_date)) as Expected_Ship_Date,
trunc(min(actual_ship_date)) as First_Ship_Date,
nullif(max(nvl(trunc(actual_ship_date), to_date('1-dec-9999'))),to_date('1-dec-9999')) as Last_Ship_Date,
count(actual_ship_date) as Completed_Ship_Count,
count(slh_label_batch_id) as Expected_Ship_Count
from ship_label
group by slh_label_batch_id
having min(trunc(expected_ship_date)) between trunc( to_date('1-mar-2009','dd-mon-yyyy')) and trunc(to_date('31-mar-2009','dd-mon-yyyy'))
order by min(expected_ship_date) desc
I thought i'd speed up the above with the following index:
create index ix_ship_label_batch on ship_label(slh_label_batch_id, expected_ship_date, actual_ship_date);
To my surprise, explain plan still said it was doing a full table scan. This didn't make any sense to me - the index a "covering" index for this query, which means it shouldn't need the table at all.
After some time i descovered that oracle will exclude index rows that are all nulls. Even still, I'm not looking for nulls, right? Wrong, no nulls would make the group by return one less row. The fix was to add one not null column to the index. Unfortunately, there wasn't a single not null column on the table (ugghh). The simple fix was to use Oracle's function-based indexing feature and index the result of a non-null function. My new index was:
create index ix_ship_label_batch on epi_ship_label, label_batch_id, expected_ship_date, actual_ship_date, nvl(confirm_flag,'N'));