Oracle Data Warehouse Tuning for 10g

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

4.11.