Overview

This chapter begins Part III of this book, expanding on previous chapters to cover more detail on query rewrite and parallel processing. Additionally, Part III includes details of data warehouse loading and general physical architecture, both as applicable to performance tuning. This chapter will cover the specifics of query rewrite in detail, rather than why it is used, and the tools used for verification. We will examine what query rewrite actually is and how its processing speed and possible use can be improved upon. So this chapter is divided into two parts. The first part explains how the optimizer query rewrites in different situations. The second part examines possibilities for improving query rewrite performance.

Chapter 4 covered query rewrite very briefly by showing how it can be implemented (ENABLE QUERY REWRITE) and by demonstrating its occurrence and relative query plan cost improvements. Chapter 4 also covered various utilities and procedures in the DBMS_MVIEW and DBMS_ADVISOR packages.

11.1 What Is Query Rewrite?

A materialized view is a physical copy of underlying data in tables. In their most efficient form materialized views are read-only objects, but they can be updated regularly, even on a real-time basis. Query rewrite occurs only using materialized views. The optimizer decides whether to rewrite a query or not, potentially replacing tables in a query with access to materialized views. Traditionally, materialized views contain aggregations of information in tables, and thus they occupy less physical space than tables. The result is less I/O activity and thus better...