Bitmap join indexes

Here’s another of my “draft” notes that needs some expansion and, most importantly, proof.

I have a fact table with a status id column that shows a massive skew. But I also have a dimension table that holds the “status code” so (in theory, at least) I have to do a join from the statuses table to the facts table to find rows of a given status. Unfortunately the join hides the skew:

select  f.*
from    facts f, statuses s
where   s.code = 'C'
and     f.status_id = s.status_id
;

The optimizer knows that the status_id column on the facts table has a highly skewed distribution and will create a histogram on it, but it can’t know which status code corresponds to which status_id so the histogram doesn’t help in calculating the join cardinality.

Will a bitmap join index help? Answer: NO.

A bitmap join index will produce a virtual column on the facts table, but you can’t collect stats on the virtual column. In fact, the optimizer won’t use the stats even if you program them into existence with calls to dbms_stats.set_column_stats(); the only thing the optimizer will use is the number of distinct keys in the index.

For the optimizer to collect the stats the Oracle developers would have to come up with a new way to collect column stats by reading an index on that column – but it would be useful.

Possible workaround – don’t do this join, rewrite the query with an IN subquery with a /*+ precompute_subquery */ hint to make the optimizer generate an IN-list of literals at optimisation time.