19 Schema Modeling Techniques< /font>

Execution Plan for a Star Transformation with a Bitmap Index

The following typical execu tion plan might result from "Star Transformation with a Bitmap Index":

SELECT S
TATEMENT
 SORT GROUP BY
  HASH JOIN
   TABLE ACCESS FULL                          CHANNELS
   HASH JOIN
    TABLE ACCESS FULL
                 CUSTOMERS
    HASH JOIN
     TABLE ACCESS FULL                        TIMES
     PARTITION RANGE ITERATOR
      TABL
E ACCESS BY LOCAL INDEX ROWID       SALES
       BITMAP CONVERSION TO ROWIDS
        BITMAP AND
         BITMAP MERGE
          BITMA
P KEY ITERATION
           BUFFER SORT
            TABLE ACCESS FULL                 CUSTOMERS
           BITMAP INDEX RANGE SCAN
        SALES_CUST_BIX
         BITMAP MERGE
          BITMAP KEY ITERATION
           BUFFER SORT
            TABLE ACCESS FULL
            CHANNELS
           BITMAP INDEX RANGE SCAN            SALES_CHANNEL_BIX
         BITMAP MERGE
          BITMAP KEY ITERA
TION
           BUFFER SORT
            TABLE ACCESS FULL                 TIMES
           BITMAP INDEX RANGE SCAN            SALES_T
IME_BIX

In this plan, the fact table is accessed through a bitmap access path based on a bitmap AND, of thre e merged bitmaps. The three bitmaps are generated by the BITMAP MERGE row source being fed bitmaps from row source trees underneath it. Each such row source tree consists of a BITMAP KEY ITERATION row source which fetches values from the subquery row source tree, which in this example is a full table access. For each such value, the BITMAP KEY ITERATION row source retrieves the bitmap from the bitmap index. After the relevan t fact table rows have been retrieved using this access path, they are joined with the dimension tables and temporary tables to produ ce the answer to the query.

Star Transformation with a Bitmap Join Index

In addition to bitmap indexes, you can use a bitmap join index during star transformations. Assume you have the following additional index structure:

CREATE BITMAP INDEX sales_c_state_bjix
ON sales(customers.cust_state_province)
FROM sales, customers
WHERE
sales.cust_id = customers.cust_id
LOCAL NOLOGGING COMPUTE STATISTICS;

The processing of the same star query using the bit map join index is similar to the previous example. The only difference is that Oracle will utilize the join index, instead of a singl e-table bitmap index, to access the customer data in the first phase of the star query.

Execution Plan for a Star Transformation with a Bitmap Join Index

The following typical execution pl an might result from "Execution Plan for a Star Transformation with a Bitmap Join Index":

SELECT STATEMENT
 SORT GROUP BY
  HASH JOIN
   TABLE ACCESS FULL                          CHANNELS
   HASH JOIN
    TABLE
ACCESS FULL                         CUSTOMERS
    HASH JOIN
     TABLE ACCESS FULL                        TIMES
     PARTITION RANGE
ALL
      TABLE ACCESS BY LOCAL INDEX ROWID       SALES
       BITMAP CONVERSION TO ROWIDS
        BITMAP AND
         BITMAP INDEX S
INGLE VALUE            SALES_C_STATE_BJIX
         BITMAP MERGE
          BITMAP KEY ITERATION
           BUFFER SORT
            TAB
LE ACCESS FULL                 CHANNELS
           BITMAP INDEX RANGE SCAN            SALES_CHANNEL_BIX
         BITMAP MERGE
   BITMAP KEY ITERATION
           BUFFER SORT
            TABLE ACCESS FULL                 TIMES
           BITMAP INDEX RANGE SCAN
            SALES_TIME_BIX

The difference between this plan as compared to the previous one is that the inner part of the bitmap index scan for the customer dimension has no subselect. This is because the join predicate information on customer.cust_state_province can be satisfied with the bitmap join index sales_c_state_bjix.

How Oracle Chooses to Use Star Transformation

The optimizer generates and saves the best plan it can produce witho ut the transformation. If the transformation is enabled, the optimizer then tries to apply it to the query and, if applicable, genera tes the best plan using the transformed query. Based on a comparison of the cost estimates between the best plans for the two version s of the query, the optimizer will then decide whether to use the best plan for the transformed or untransformed version.

If t he query requires accessing a large percentage of the rows in the fact table, it might be better to use a full table scan and not use the transformations. However, if the constraining predicates on the dimension tables are sufficiently selective that only a small po rtion of the fact table needs to be retrieved, the plan based on the transformation will probably be superior.

Note that the o ptimizer generates a subquery for a dimension table only if it decides that it is reasonable to do so based on a number of criteria. There is no guarantee that subqueries will be generated for all dimension tables. The optimizer may also decide, based on the propert ies of the tables and the query, that the transformation does not merit being applied to a particular query. In this case the best re gular plan will be used.

Star Transformation Restrictions

Star transformation is not supported for tables with any of the following characteristics:

• Queries with a table hint that is incompatible with a bitmap access path

• Queries that contain bind variables

• Tables with too few bitmap indexes. There must be a bitmap index on a fact table column for the optimizer t o generate a subquery for it.

• Remote fact tables. However, remote dimension tables are allowed in the su bqueries that are generated.

• Anti-joined tables

• Tables that are already use d as a dimension table in a subquery

• Tables that are really unmerged views, which are not view partition s

The star transformation may not be chosen by the optimizer for the following cases:

• T ables that have a good single-table access path

• Tables that are too small for the transformation to be w orthwhile

In addition, temporary tables will not be used by star transformation under the following conditions:

• The database is in read-only mode

• The star query is part of a transaction that is in serializable mode