1. Create a multi-column index that satisfies the query entirely on its own without the need to go to the table to read additional data. This technique solves a large percentage of slow query problems.
2. A bitmap index in Oracle kills concurrency. Do not use bitmap indexes for table with multiple concurrent transactions.
Also consider that if you have a 100M row table and the column has only 4 values, but those values are highly skewed (ie 3 values are 99% of the rows, 4th is 1%), then a function index on the skewed column can be very useful:
create fbi on table(case
when skewed_col == 'small value'
skewed_col
else
null
end)
The null values are never indexed, so now you have a nice small index that is good only for finding you 'needles in the haystack', so long as you put that case statement in your queries.
1. Create a multi-column index that satisfies the query entirely on its own without the need to go to the table to read additional data. This technique solves a large percentage of slow query problems.
2. A bitmap index in Oracle kills concurrency. Do not use bitmap indexes for table with multiple concurrent transactions.