Initialization Parameters

The most common reason I find people having problems with star transformation explain plans is that they don't set the proper INIT.ORA parameters. In short, if you don't have the proper initialization parameters set, you cannot obtain star transformation explain plans, even if you specify the STAR_TRANSFORMATION hint! Please reread that last sentence again, possibly even twice, because 20% of the problem sites I visit have the simple problem of either not setting the right values or not setting them high enough.

For Oracle 8i, the following parameters must be set:

  • ALWAYS_ANTI_JOIN = HASH

  • ALWAYS_SEMI_JOIN = HASH

  • BITMAP_MERGE_AREA_SIZE = 16MB or larger

  • COMPATIBLE = 8.1.7

  • CREATE_BITMAP_AREA_SIZE = 16MB or larger

  • HASH_AREA_SIZE ...

Get Oracle® DBA Guide to Data Warehousing and Star Schemas now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial