Oracle Specifics: OLAP Performance

• BITMAP Indexes

• • Ensures one pass through dimension tables (small, < 100k rows) and only ONE scan of FACT table (usually large, millions of rows)

• Parallel Query with Partitioned Fact tables

• • Allows for the “ONE scan” of the FACT table to be split across CPUs (nodes in RAC?) and I/O channels. I/O is MORE important than CPUs. Data Warehouse queries are almost ALWAYS waiting on disks.

• Materialized Views

• • Watch your reporting tool (Discoverer, Mondrian) and determine what SQL your “canned” reports are generating. Building a corresponding MView and refreshing after load will make these LIGHTNING quick!

• Oracle Tuning

• • Few Users, Lots of Sort Operations (group by)
  • Dedicated Connections