Databases Reference
In-Depth Information
-----
- star transformation used for this statement
Keep in mind that the optimizer may choose to ignore star transformation if it determines a cheaper
cost alternative to execute your query. If you think you should be achieving star transformation and
you're not, try adding a STAR_TRANSFORMATION hint, and if needed, also a FACT hint, and see if the
query then uses star transformation. Sometimes both hints are needed for the query to achieve star
transformation. Then you can compare the performance between the results using star transformation
to the one that is not to see which of the two is the better performing query. See the following examples
of how to use the hints related to star transformation:
SELECT /*+ star_transformation */ pr.prod_category, c.country_id, ...
SELECT /*+ fact(s) */ pr.prod_category, c.country_id, ...
SELECT /*+ star_transformation fact(s) */ pr.prod_category, c.country_id, ...
If you executed this query without star transformation enabled, then you can see from the following
explain plan output that the optimizer bypassed the use of star transformation in the execution plan and
the bitmap indexes were not utilized:
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | |
| 5 | NESTED LOOPS | |
| 6 | PARTITION RANGE ALL | |
| 7 | TABLE ACCESS FULL | SALES |
| 8 | TABLE ACCESS BY INDEX ROWID| PRODUCTS |
| 9 | INDEX UNIQUE SCAN | PRODUCTS_PK |
| 10 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS |
| 11 | INDEX UNIQUE SCAN | CUSTOMERS_PK |
| 12 | INDEX UNIQUE SCAN | TIMES_PK |
| 13 | TABLE ACCESS BY INDEX ROWID | TIMES |
---------------------------------------------------------
Performance Implications Loading Data with Bitmap Indexes
The biggest payoff for using bitmap indexes is that they help the speed of queries. The bitmap indexes
can be created very quickly in comparison to B-tree indexes, and they consume much less space than a
B-tree index. The trade-off for gains with these distinct advantages is the impact bitmap indexes have on
DML operations on tables containing bitmap indexes. Bitmap indexes can be created quickly and can be
scanned quickly to help query performance, but they are not designed to support DML operations very
well.
