- How is the b-tree index stored internally?
- If a query is submitted, how is the query result loaded in memory? Is it block-oriented or column-oriented?
Oracle also has bitmap join indexes:
In general, you will want a bitmap index when:
- Table column is low cardinality -- As a rough guide, consider a bitmap for any index with less than 100 distinct values.
select region, count(*) from sales group by region;
- The table has low DML -- You must have low insert./update/delete activity. Updating bitmapped indexes takes a lot of resources, and bitmapped indexes are best for largely read-only tables and tables that are batch updated nightly.
- Multiple columns -- Your SQL queries reference multiple, low-cardinality values in their where clause. Oracle cost-based SQL optimizer (CBO) will scream when you have bitmap indexes on.
This was first published in November 2005