112 4.6 Endnotes
Now we can run the EXPLAIN PLAN command again on the same
query used in Figure 4.11. The result is shown in Figure 4.12. There is little
difference between Figures 4.11 and 4.12, except that the bytes read has
dropped to nearly a fourth of what it was. That is quite a sharp drop. If the
database were much larger, there would ultimately be a cost difference. It is
not necessary to demonstrate further at this stage, but suffice it to say that
in data warehouses, a difference in I/O activity of this magnitude could
result in a substantial cost saving. The byte count has probably changed due
to the introduction of compression using the COMPRESS keyword.
Materialized views are certainly very useful in data warehousing environ-
ments, and their use is a tuning method in itself. Perhaps an important con-
clusive point to note is that, like many other types of database objects, most
restrictions on materialized views all make perfect sense, especially with
respect to fast refresh and on commit.
The next chapter will look at Oracle Dimension objects, which can be
used to alleviate performance issues occurring in the multiple level dimen-
sional entity hierarchies of star and snowflake schemas. A snowflake schema
is a less purely dimensional-fact version of a star schema, such that dimen-
sions are normalized.
4.6 Endnotes
1. Chapter 7 in Oracle Performance Tuning for 9i and 10g
(ISBN: 1555583059)
Figure 4.12
Improving query
rewrite from Figure

Get Oracle Data Warehouse Tuning for 10g 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.