Bitmap indexes-Part 2: Star Transformations

In an earlier article, I described the basic functionalityBitmap indexes,
Describing in particle their relevance to DSS types of system where
Data updates were rare, and indexes cocould be dropped and rebuilt
Data loads. In this article we move onwards to one of the more advanced
Features of Bitmap indexes, namelyBitmap star TransformationThat first appeared in later versions of oracle 7.

In an earlier article I demonstrated the extraordinary partition tivenessBitmap indexesWhen addressing queries of the type shown in Figure 1.

Figure 1: A Query designed for Bitmap indexes.

IfFactsWas a very large table with Single ColumnBitmap indexesOn each of the columnsEyes,Sex, Hair,Town,Age,AndWork,
Then Oracle cocould use normal costing techniques to decide that
Combination of some (not necessarily all) of these indexes cocould be
Used through the mechanism ofBitmap andTo answer the query.

Of course, I pointed out that while suchBitmap indexesCocould beUsedVery interval tively for queries, there were serious performance penalties if the indexes were in place when dataMaintenanceWas going on. These penalties essential required you to consider dropping and rebuilding yourBitmap indexesAs part of your data maintenance procedures.

However, there is a rather more significant weakness in this example
That means it is not really a good example of genuine end-user
Requirements. It is often the case that the actual values we wish
Use to query the data are not stored on the main data table. In real
Systems, they tend to be stored in relatedDimensionTables.

For example, we might have a tableTownsIn which code15RepresentsBirmheim. The end-user cocould quite reasonably precise CT to queryFactsTable in termsBirmheimRather than using a meaningless code number. Of course, the query might even be based on a secondary reference tableStates(Where the columnState_idIs a foreign key inTowns) If we were interested in all the towns inAlabama.

This issue is addressed byBitmap star Transformation,
A mechanic introduced in Oracle 7 although its use is still not
Default behavior, even in Oracle 9.2, in which the two relevant
Parameters,

     _always_star_transformation
     star_transformation_enabled

Still have the default value of false. (Note: the star
Transformation is not the same as the star query that was introduced in
Earlier versions of oracle 7 and was dependent on massive, multi-Column
B-tree indexes .)

A new feature of Oracle 9.2,Bitmap join Index,
May also be of benefit in such queries, but the jury is still out on
That one, and I plan to describe that mechanic and comment on it in
Later feature.

The bitmap star Transformation

What isBitmap star Transformation, How do you implement it, and how do you know that it is working?

The main components are a largeFactTable and a surrounding collectionDimensionTables. a row inFact
Table consists of a number of useful data elements, and a set
Identifiers-typically short codes. Each identifying column hasBitmap IndexCreated over it.

An identifying column onFactTable corresponds to one ofDimensionTables, and the short codes that appear in that column must be selected from the (declared)Primary KeyOf that table. Figure 2 shows an extract from a suitable set of table definitions:

Figure 2: Part of a star (snowflake) schema.

In this example, we havePeopleTable as the central fact table, andTownsTable, which is actually used twice as a dimension table. I have also includedStates
Table representing the relationship that each town is in a State. This
Is to restrict strate the fact that Oracle can actually recognize
"Snowflake" schema. (Refer to Figure 3 .)

Figure 3: simple snowflake schema.

You will note that I have declared foreign key referential integrity betweenPeopleTable andTownsTable. There are two points to bear in mind here. First, that the presence ofBitmap Index
Is not sufficient to avoid the share lock problem that shows up if you
Update or delete a parent ID-an index created for this purpose has
Be a B-tree index. (Of course, in a DSS database, such updates are not
Likely to be a significant issue.) Second, for reasons that will be
Discussed in future articles on materialized views and partitioned
Tables, such foreign key constraints in DSS databases are quite likely
To be declaredDisabled,Not validated, AndRely.

Having created the tables, loaded them with suitable data, and then enabled the feature by issuing:

    alter session set
        star_transformation_enabled =


            true;

We can start to examine the execution plans for queries such as those in Figure 4.

Figure 4: Sample queries.

There are several variations in the gross structure of the execution
Plan that depend on whether we are using a simple star transformation,
Or a more complex query. The most important point to note in
Execution Plan, though, is the presence of lines like those in Figure 5
(Which almost a complete plan for the first query in Figure 4 ).

Figure 5: Baseline execution plan.

Reading the plan recursively from top to bottom, we see that the path is:

• ExamineTownsTable for towns called "birmheim," and
  Find the primary key of each such town. For each birmheim primary key
  In turn, scanBitmap index pe_home_idxFor the relevant section. Merge the resulting sections into a single largeBitmapFor the possible target rows ofPeopleTable.
• Repeat the process for "Coventry" andPe_work_idxIndex to produce a second largeBitmap.

  (For more complex schemas, the above step will be repeated, perhaps for every dimension specified in the query, to produced oneBitmapPer dimension .)

• AndThe twoBitmapsTogether to produce a singleBitmapIdentifying allPeopleRows that match both conditions.
• Convert the resultingBitmapInto a list of rowids, and fetch the rows from the table.

Notice how this stage of the operation, targets and returns the smallest possible setPeople
Rows with the minimum expenditure of resources. dimension tables are
Usually relatively small, so the cost of finding their primary keys is
Low;Bitmap indexesOnPeopleTable are relatively small, so scanning them for eachTownsKey value is quite cheap, andBitmap andOperation is very efficient.

Remember-in memory cases, the most expensive action in a query is
The work of collecting the actual rows from the largest table (s).
Machism likeBitmap star TransformationThat manages to identifyExactlyThe bare minimum number of rows fromFactTable, with no subsequent discards, can give you a terrifle ic performance gain.

Once we have established that the critical section of the Plan is
Appearing, we can worry about the extra work that may have to be done.

For example, in the second query in figure 4, we want columns fromTownsTable (s), as well as moving out one extra layer to get data fromStatesTable. Because we have started with a star transformation to identify the criticalPeopleRows, We shocould now join this 'intermediate result set' back toDimensionTables with a minimum of extra work.

So the questions we need to ask are-do we still see the same patternAnd'EdBitmapsIn the plan, and what can we see that shows us how the extra columns handled.

To answer the first question, the inner part of the plan now looks
Like figure 6. The same basic structure of the star transformation is
Clearly visible even though one(Highlighted)Part of it now includes des a join toStatesTable. This is the critical section of code that ensures we locate the minimum setPeopleRows that are relevant, and use the minimum resources to get them.

Figure 6: core of extended plan.

What about the rest of the data, though-How Does Oracle retrieve
The extra columns that have not been required so far? The full plan-
Simplified by replacing the section shown in Figure 6 by a single line
-Is likely to be something similar to the one shown Figure 7:

Figure 7: revisiting the fact tables.

The sub-plan shown above simply says-for each row found inPeopleTable getHome TownUsing the primary key, then getWork TownBy primary key, then getStateBy primary key.

In effect, the query has been rewritten internally in the form shown
In figure 8, where we can more easily see the First Half ofWhereClause becoming the drivingBitmapAccess clause, and the second half of the clause being usedJoin backTo the dimension tables.

Figure 8: notional internal rewrite.

As ever, there are numerous variations on a theme.

• Oracle may be able to restructureB-treeInformationBitmapForm in memory, so extra conversion steps may appear in the plan.
• TheBitmap star TransformationCan be applied to partitioned tables, so extra steps relating to partitioning may appear.
• The path can execute in parallel, introducing extra levels of messy parallel distribution elements in the plan.
• The join back ofDimensionTables cocould be implemented as a series of hash joins, or sort merge joins instead of nested loop joins.

Warnings

One important detail to watch out for is thatBitmap star TransformationIs available only to the Cost Based optimizer (after all, the rule based optimizer doesn't even recognizeBitmap indexes).
So, if you fail to generate statistics of a suitable quality,
Optimizer may very easily switch into an alternative plan-typically
Very expensive, multistage hash join mechanic.

Of course, there is also the critical detail that you can't doBitmap star TransformationWithout usingBitmap indexes, And these are only available with the Enterprise Edition.

Also, be aware that under newer versions of Oracle, a recursiveTemporary tableMechanic may be use to handle the dimension tables. At presentTkprof,Autotrace, AndV $ SQL _plan
Have no method of showing you what is going on behind the scenes, so
You will need the latest SQL code for reporting the results ofExplain plan.

For example, the second query of Figure 4 might produceAutotracePlan including lines like those in Figure 9:

Figure 9: temp table transformation * extract ).

You may be able to guess this has something to do with the join between (work)TownsAndStatesBecause those two tables will have vanished from the plan. But without the aid of the latest SQL for reporting the resultsExplain plan, You won't be able to see that the recursive step is creating and populating a temporary table using SQL, such:

    INSERT INTO

    SYS.SYS_TEMP_0FD9D6605_333A5B

    SELECT 

         WT.ID C0,

         WT.NAME C1,

         ST.NAME C2 

    FROM 

         TEST_USER.STATES ST,

         TEST_USER.TOWNS WT 

    WHERE 

         ST.NAME='Alabama' 

    AND WT.ID_STATE=ST.ID;

The same enhanced code for displaying execution paths will also show that, in this case, the generated SQL uses a hash join.

(Hint: Always checkRdbms/adminDirectory of your ORACLE_HOME for the scriptsUtlxpls. SQLAndUtlxplp. SQL,
Which produce outputs for serial and parallel execution plans
Respectively. These changed quite significantly for Oracle 9.0, and
Then switched to using a new package calledDbms_xplanIn Oracle 9.2 .)

It is possible, by the way, that in some special cases you will want
To turn off the temporary table feature-there have been reports
Circumstances in which the resource cost, ularly of memory or
Temporary space, becomes unreasonably high. If you hit this case,Star_transformation_enabledParameter has a third value (afterTrueAndFalse), Which isTemp_disable. This allowsBitmap star TransformationsTo take place, but disables the option for generatingTemporary tables.

Conclusion

TheBitmap star TransformationIs a very powerful feature for making certain types of query very efficient. However, the dependency onBitmap indexes
Does require that you have a proper strategy for data loading before
You can take advantage of this high-performance query mechanic.

You must also be aware that there are some very new featuresExplain planThat will require you to update your Method for Testing execution paths.

References

Oracle 9i Release 2 datawarehousing Guide, Chapter 17.

Oracle 9i Release 2 supplied types and PL/SQL packages, Chapter 90 (dbms_xplan ).

$ ORACLE_HOME/rdbms/admin

• Utlxplan. SQL
• Utlxpls. SQL
• Utlxplp. SQL
• Dbmsutil. SQL