Lesson 7	Understanding query rewrites
Objective	Describe what conditions allow for query rewriting.

Materialized View to store Summary Data

Understanding Query Rewrites

Query rewrites are where the real benefit of materialized views kick in. When you have a materialized view that has query rewrites enabled, the Oracle cost-based query optimizer will automatically rewrite a submitted query to take advantage of a materialized view. The substitution of a materialized view for a requested table or view is totally transparent to the user, although a user can directly query a materialized view.

Conditions for Query rewrites

The Oracle cost-based optimizer determines whether to use a materialized view in a query according to a set of conditions:

Query rewrite must be enabled for the database session.
The materialized view must have the query rewrite enabled.,
The SQL query submitted can use the materialized view.

The cost-based optimizer determines if a materialized query can be used according to a series of tests:

If the SQL query submitted exactly matches the SQL used in the materialized view.
If the text of the SQL query, starting from the FROM clause, matches the SQL used in the materialized view.
If one of the general query rewrite methods can be used to substitute the materialized view. These methods do a number of compatibility tests to see if some or all of the data requested by the query can be supplied by a materialized view.

There is also a set of integrity constraints that will allow or disallow the use of a materialized view depending on how recently the data in the view has been refreshed. The following SlideShow illustrates some of the conditions where a materialized view could be selected by the cost-based optimizer:

Materialized View Match

1) If the list of columns, tables to be joined in the query match those in the view (and the GROUP BY clause if aggregates are used), the cost-based optimizer will use the materialized view.

2) If additional tables are joined in the query, buyt the query includes tables in the materialized view — 2) If additional tables are joined in the query, buy the query includes all the joined tables in the materialized view, the cost-based optimizer will use the materialized view.

3) If additional columns are mentioned in the query, but they can either be calculated or easily retrieved through a constraint, the cost-based optimizer will use the materialized view.

4) If the materialized view is GROUPed at a finer level of detail than the other conditions — 4) If the materialized view is GROUPed at a finer level of detail than the query, and the other conditions for use are met, the cost-based optimizer will use the materialized view.

If the list of columns, tables to be joined in the query match those in the view (and the GROUP BY clause if aggregates are used),
If additional tables are joined in the query, buy the query includes all the joined tables in the materialized view, the cost-based optimizer will use the materialized view.
If additional columns are mentioned in the query, but they can either be calculated or easily retrieved through a constraint, the cost-based optimizer will use the materialized view.
If the materialized view is GROUPed at a finer level of detail than the query, and the other conditions for use are met, the cost-based optimizer will use the materialized view.

Materialized View Match
The fourth section of the create materialized view command is the query that the materialized view will use:

[ for update ] [{disable | enable} query rewrite]

as subquery. If you specify for update, the materialized view will be updatable; otherwise, it will be readonly. Most materialized views are read-only replicas of the master data. If you use updatable materialized views, you need to be concerned with issues such as two-way replication of changes and the reconciliation of conflicting data changes. Updatable materialized views are an example of multimaster replication.
Note: The query that forms the basis of the materialized view should not use the User or SysDate pseudo-columns.
The following example creates a read-only materialized view called LOCAL_BOOKSHELF in a local database, based on a remote table named BOOKSHELF that is accessible via the REMOTE_CONNECT database link. The materialized view is placed in the USERS tablespace.

create materialized view LOCAL_BOOKSHELF
tablespace USERS
refresh force
start with SysDate next SysDate+7
with primary key
as
select * from BOOKSHELF@REMOTE_CONNECT;

Oracle responds with

Materialized view created.

The command shown in the preceding example will create a read-only materialized view called LOCAL_BOOKSHELF. Its underlying table will be created in a tablespace named USERS. You can place materialized view logs in tablespaces apart from the materialized views they support. The force refresh option is specified because no materialized view log exists on the base table for the materialized view; Oracle will try to use a fast refresh but will use a complete refresh until the materialized view log is created. The materialized view’s query specifies that the entire BOOKSHELF table, with no modifications, is to be copied to the local database. As soon as the LOCAL_BOOKSHELF materialized view is created, its underlying table will be populated with the BOOKSHELF data. Thereafter, the materialized view will be refreshed every seven days. The storage parameters that are not specified will use the default values for those parameters for the USERS tablespace.

You can use the EXPLAIN PLAN command to see if a particular query will be rewritten to use a materialized view.
Explain Plan Command
In the next lesson, you will learn how to manage materialized views.