2 Replies Latest reply: Mar 27, 2013 11:10 AM by rp0428 RSS

    Star transformation question

    rahulras
      Hi All,

      I am on v11.2.0.3.

      I am in process of creating a star schema for our transaction data model. According to the documentation, we create "bitmap" indexes on foreign key columns in the fact table and enable star transformation operation in init.ora. In the start query transformation and execution, bitmap merge looks like the key in whole operation.
      I want to clear my technical understanding around star transformation.

      My questions are
      1) If I create btree index on the foreign keys in fact table ( instead of bitmap ), will the star transformation not happen?
      2) I might have to create the fact table as Index Organized table (why? very long answer) I guess, start transformation will not work in that case as well. Am I correct?
      3) Are there any special partitioning facilities/concepts which might be helpful in star schema/transformation ?

      Thanks in advance
        • 1. Re: Star transformation question
          riedelme
          rahulras wrote:
          Hi All,

          I am on v11.2.0.3.
          My questions are

          1) If I create btree index on the foreign keys in fact table ( instead of bitmap ), will the star transformation not happen?
          I've (rarely) seen what appeared to be star plans without bitmap indexes but they performed poorly. My understanding agrees with yours - bitmap indexes help the process along
          2) I might have to create the fact table as Index Organized table (why? very long answer) I guess, start transformation will not work in that case as well. Am I correct?
          I am not aware of any requirement to use IOTs for star transfomations. What is the source of this idea?
          3) Are there any special partitioning facilities/concepts which might be helpful in star schema/transformation ?
          Not that I am aware of
          • 2. Re: Star transformation question
            rp0428
            >
            I am on v11.2.0.3.

            I am in process of creating a star schema for our transaction data model. According to the documentation, we create "bitmap" indexes on foreign key columns in the fact table and enable star transformation operation in init.ora. In the start query transformation and execution, bitmap merge looks like the key in whole operation.
            I want to clear my technical understanding around star transformation.

            My questions are
            1) If I create btree index on the foreign keys in fact table ( instead of bitmap ), will the star transformation not happen?
            2) I might have to create the fact table as Index Organized table (why? very long answer) I guess, start transformation will not work in that case as well. Am I correct?
            3) Are there any special partitioning facilities/concepts which might be helpful in star schema/transformation ?
            >
            Have you reviewed the Data Warehouse Guide sections that discuss Star Schemas? They will answer many of your questions.
            http://docs.oracle.com/cd/B28359_01/server.111/b28313/schemas.htm#CIHFGCEJ
            >
            Tuning Star Queries
            To get the best possible performance for star queries, it is important to follow some basic guidelines:

            A bitmap index should be built on each of the foreign key columns of the fact table or tables.

            The initialization parameter STAR_TRANSFORMATION_ENABLED should be set to TRUE. This enables an important optimizer feature for star-queries. It is set to FALSE by default for backward-compatibility.

            When a data warehouse satisfies these conditions, the majority of the star queries running in the data warehouse will use a query execution strategy known as the star transformation. The star transformation provides very efficient query performance for star queries.
            . . .
            Star Transformation with a Bitmap Index
            A prerequisite of the star transformation is that there be a single-column bitmap index on every join column of the fact table. These join columns include all foreign key columns.