Let’s say you have a classic data warehouse star schema defined something like the one shown here in Toad^® Data Modeler.

It’s not uncommon for the DBA to index the fact table in two radically different ways. During “live-reporting” periods, the fact table would have just bitmap indexes on each individual foreign key column to induce “STAR_TRANSFORMATION” explain plans for all the truly ad-hoc queries. During “data-loading” periods, the fact table would have instead a b-tree index to guarantee the data accuracy (i.e. a unique primary key index).

The point is that prior to Oracle 11g, these two indexing strategies could not co-exist at the same time. The DBA would drop the bitmap indexes during the data loads, and drop the b-tree index during reporting periods so as to avoid it being used in any explain plan.

Now with 11g, the DBA could simply create the primary key index as invisible – that way there would no longer be any need to recreate it before the data load. In the past, the DBA could partition the fact table and thus drop only the b-tree PK index on the currently active partition. But even that step is unnecessary now. If that PK unique index is marked as being invisible, it can remain in existence during reporting periods without chance of forming a SELECT that uses the b-tree index instead of the bitmap indexes. 

So here in Toad we see the ad-hoc query and the resulting explain plan using the b-tree PK index – with a cost of 2,795 and a run time of 4+ seconds.

Now we modify the index to be invisible as follows using Toad for Oracle 9.5.

Thus we now get the explain plan desired (i.e. no PK index usage) – which means we got the “star transformation”, which a higher cost but runs four times as fast at just 1 second!

Wow – Indexing has come a long way baby.