OracleSource

• A materialized view stores both the definition of a view plus the rows resulting from the execution of the view. Like a view, it uses a query as the basis, but the query is executed at the time the view is created, and the results are stored in a table.

• Because accessing a materialized view may be significantly faster than accessing the underlying base tables, the optimizer rewrites a query to access the view when the query allows it. The query rewrite activity is transparent to applications.

• Dimensions are data dictionary structures that define hierarchies based on columns in existing database tables. Dimensions describe business entities such as products, departments, and time in a hierarchical, categorized manner. A dimension may consist of one or more hierarchies. Each hierarchy comprises multiple levels.

• The Oracle9i Server onwards enables you to create temporary tables. Temporary tables can improve performance significantly by holding temporary data for reuse within your transaction or session.

• Like a view, it uses a query as the basis, but the query is executed at the time the view is created, and the results are stored in a table.
• You can define the table with the same storage parameters as any other table and place it in the tablespace of your choice.
• You can also index the materialized view table like other tables to improve the performance of queries executed against them.
• When a query can be satisfied with data in a materialized view, the Oracle9i server transforms the query to reference the view rather than the base tables.
• By using a materialized view, expensive operations such as joins and aggregations do not need to be re-executed.

Note :
Materialized views (MVs) are replacing read-only snapshots. However, MVs provide greater functionality, such as the ability to allow query : rewrites directly or through dimensions, as covered in this lesson.
There are several issues/restrictions with MVs and query rewrites in 8.1.5. For example, as soon as you have a non-join predicate in the MV definition, only exact matches will result in a query rewrite. Make sure to test any MV demo, and don’t improvise…

Create Materialized Views

If a query involving summaries, large or multiple joins, or even both, is likely to be used multiple times, it can be more efficient to create a materialized view of the query results. This requires a single execution of the query and the storage space to preserve the results.
If the queries are likely to be reused over time, you may also need a mechanism to update the materialized view as the base tables change. The performance and storage costs of maintaining the materialized view must be compared to the costs of re-executing the original query whenever it is needed.

The process of modifying a query to use the view rather than the base table is called a query rewrite.

Note: This is just an example of the CREATE MATERIALIZED VIEW command; more details about optional command clauses will be discussed later during this lesson.

SQL> CREATE MATERIALIZED VIEW cls_summ AS
2 select cl.class_id, co.short_name 3 , cl.start_date, l.city
4 , count(r.stud_id) as tot_reg
5 from classes cl
6 , courses co
7 , locations l
8 , registrations r
9 where cl.loc_id = l.loc_id
10 and cl.crs_id = co.crs_id
11 and cl.class_id = r.class_id
13 group by cl.class_id, co.short_name
14 , cl.start_date, l.city;


Refresh Materialized Views :-
Refresh types:
• COMPLETE
• FAST
• FORCE
• NEVER

Create materialized view logs for FAST refreshes

SQL> create materialized view log on …

Depending on the activity on the base tables and the accuracy of the information required, refreshing of materialized views may be done accordingly. The refresh mechanism is similar to that used by snapshots: COMPLETE, FAST, FORCE, and NEVER.

• A COMPLETE refresh involves truncating existing data and reinserting all the data by reexecuting the materialized view query definition.
• FAST refreshes only apply the changes made since the last refresh. Two types are available:
• Using materialized view logs: In this case, all changes to the base tables are captured in a log and then applied to the materialized view.
• Using ROWID ranges: A materialized view can be refreshed fast after direct path loads, based on the ROWIDs of the new rows. Direct loader logs are required for this refresh type.
• Not all materialized views can use a FAST refresh.
• A materialized view defined with a refresh type of FORCE refreshes with the fast mechanism if possible, or else uses a complete refresh. Force is the default refresh type.
• The NEVER option suppresses all refreshes of the materialized view.

Note: Materialized view logs are created using the CREATE MATERIALIZED VIEW LOG command.

As well as choosing the refresh type, you can decide what mode to use to keep materialized views synchronized with their base tables.
• Manual Refresh :
• Manual refreshes are performed using the DBMS_MVIEW package.
• The DBMS_MVIEW package provides a number of procedures and functions to manage materialized views, including the REFRESH, REFRESH_DEPENDENT, and REFRESH_ALL_MVIEWS procedures.
• Automatic Refresh : Automatic refresh can be performed:
• ON COMMIT : When this option is specified for a materialized view, it is updated whenever changes to one of the base tables are committed. Note: The update to the materialized view occurs asynchronously to the user commit and does not degrade the user’s perceived performance.
• At a specified time : Refresh on a materialized view can be scheduled to occur at a specified time. For example, it can be refreshed every Monday at 9:00 a.m. by using the START WITH and NEXT clauses. In order for such refreshes to occur, the instance must initiate job processes with the JOB_QUEUE_PROCESSES parameter.
• As with refresh types, refresh modes are not all available to every materialized view.

Refresh Modes
Manual refresh : By using the DBMS_MVIEW package

Automatic refresh
• Synchronous : Upon commit of changes made to the underlying tables—but independent of the committing transaction
• Asynchronous : Define a refresh interval for the materialized view

Materialized Views : Manual Refresh

Specific materialized views
DBMS_MVIEW.REFRESH ('SF_CLASSES', PARALLELISM => 10);

Materialized views based on one or more tables
DBMS_MVIEW.REFRESH_DEPENDENT ('CLASSES');

All materialized views due for refresh
DBMS_MVIEW.REFRESH_ALL_MVIEWS;

Manual Refresh
The following is a list of possible refresh scenarios for materialized views:
• Refresh specific materialized views using REFRESH procedure
• Refresh all materialized views that depend on a given set of base tables using REFRESH_DEPENDENT procedure
• Refresh all materialized views that have not been refreshed since the last bulk load to one or more detail tables using REFRESH_ALL_MVIEWS procedure

The procedures in the package use a number of parameters to specify the following:
• Refresh method
• Whether to proceed if an error is encountered
• Whether to use a single transaction (consistent refresh)
• The rollback segment to use

You need server job queues to run the refresh job. Therefore the appropriate initialization parameters, JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL, must be set to enable job queue refresh processes.

Query Rewrites

 To use a materialized view instead of the base tables, a query must be rewritten.
 Query rewrites are transparent to applications.
 Query rewrites do not require special privileges on the materialized view.
 A materialized view can be enabled or disabled for query rewrites.

Because accessing a materialized view may be significantly faster than accessing the underlying base tables, the optimizer rewrites a query to access the view when the query allows it. The query rewrite activity is transparent to applications. In this respect, their use is similar to the use of an index.
Users do not need explicit privileges on materialized views to use them. Queries executed by any user with privileges on the underlying tables can be rewritten to access the materialized view.
A materialized view can be enabled or disabled. A materialized view that is enabled is available for query rewrites.

The optimizer rewrites queries in order to utilize materialized views:
 The QUERY REWRITE privilege allows users to enable materialized views.

 The DBMS_OLAP package has options to use materialized views.

To enable or disable individual materialized views for query rewrites, you must have the GLOBAL QUERY REWRITE or the QUERY REWRITE system privilege. Both versions of the privilege allow you to enable materialized views in your own schema. The GLOBAL version allows you to enable any materialized views you own, whereas the simple QUERY REWRITE privilege requires that the base tables as well as the views be in your own schema. The DBMS_OLAP supplied package provides a collection of materialized view analysis and advisory functions.

The best method to detect if query rewrites occur is to use the EXPLAIN PLAN command or the AUTOTRACE setting in SQL*Plus. You should also notice improved response time if a materialized view is used by the optimizer.
Note: There are several system privileges that control whether you are allowed to create materialized views and modify them, and whether query rewrites are enabled for you.

There are also many data dictionary views that contain information about materialized views.
Also note that there are some bugs with SQL*Plus AUTOTRACE not showing the query rewrite, because it uses an implicit second database session that does not inherit all settings from your main session. Using EXPLAIN is safer.

 Use EXPLAIN PLAN or AUTOTRACE to verify that query rewrites occur.

 Check the query response:
• Fewer blocks are accessed
• Response time should be significantly better

Create Materialized Views: Syntax Options

The CREATE MATERIALIZED VIEW syntax is similar to the CREATE SNAPSHOT command, which it replaces. There are some additional options:
• The BUILD IMMEDIATE option causes the materialized view to be populated when the CREATE command is executed. This is the default behavior.
• You can choose the BUILD DEFERRED option, which creates the structure but does not populate it until the first refresh occurs.
• Instead of the BUILD option, you can also specify ON PREBUILT TABLE when you want an existing summary table to be the source of a materialized view.
• The ENABLE/DISABLE QUERY REWRITE clause determines whether query rewrites are automatically enabled for the materialized view.

Note: You can use ON PREBUILT TABLE to convert an existing table to a preinitialized materialized view. There is an additional clause for this option, WITH/WITHOUT REDUCED PRECISION. This clause, which defaults to WITHOUT REDUCED PRECISION, determines whether or not the columns in the subquery have to have the same precision as the corresponding columns in the table.

CREATE MATERIALIZED VIEW mview_name
[TABLESPACE ts_name]
[PARALLEL (DEGREE n)]
[BUILD {IMMEDIATE|DEFERRED}]
[REFRESH {FAST|COMPLETE|FORCE
|NEVER|ON COMMIT}]
[{ENABLE|DISABLE} QUERY REWRITE]
AS SELECT … FROM …

Enabling and Controlling Query Rewrites
• OPTIMIZER_MODE: Query rewrites are only available under cost-based optimization.
• QUERY_REWRITE_ENABLED: This parameter can be set to FALSE to suppress query rewrites. This is a dynamic instance/session parameter.
• QUERY_REWRITE_INTEGRITY: This is also a dynamic instance/session parameter. It accepts the following values:
• ENFORCED (the default) enables query rewrites only if the optimizer can guarantee consistency. Only updated materialized views and enabled validated constraints are used for query rewrites.
• TRUSTED allows query rewrites based on declared (not necessarily enforced) relationships. All updated materialized views and dimensions (discussed later in this lesson) and constraints with the RELY flag are used for query rewrites.
• STALE_TOLERATED allows query rewrites to use materialized views that have not been refreshed since the last DML operation and relationships that have been declared.
• Note: There are no object privileges associated with query rewrites. Users with access to the base tables implicitly benefit from query rewrites.
• You can use the hint REWRITE to restrict the materialized views that are considered for query rewrites.
• The NOREWRITE hint is available to suppress query rewrites.


 Query rewrites are only available with cost-based optimization.
QUERY_REWRITE_ENABLED = {true|false}
QUERY_REWRITE_INTEGRITY = {enforced|trusted|stale_tolerated}

 Optimizer hints to influence query rewrites are REWRITE and NOREWRITE.

Query Rewrite Example

The execution plan shows that the materialized view is accessed instead of joining all four base tables to produce the result.
A REWRITE/NOREWRITE hint overrides a materialized view’s definition, set in the CREATE or ALTER MATERIALIZED VIEW command with the ENABLE QUERY REWRITE clause.
This example shows a transparent query rewrite where the query exactly matches the materialized view definition.
The next page shows an example of a query that does not match the materialized view definition.


SQL> EXPLAIN PLAN FOR
2 select cl.class_id, co.short_name …
3 from classes cl
4 , courses co
5 , locations l …
6 where cl.loc_id = l.loc_id
7 and cl.crs_id = co.crs_id …
8 group by … ;

OPERATION NAME
----------------------------- --------------
SELECT STATEMENT
TABLE ACCESS FULL CLS_SUMM

SQL> select cl.class_id, co.short_name, cl.start_date
2 , l.city, count(r.stud_id) as tot_reg
3 from classes cl, courses co
4 , locations l, registrations r
5 where cl.loc_id = l.loc_id
6 and cl.crs_id = co.crs_id
7 and cl.class_id = r.class_id
8 and l.city = 'San Francisco'
9 group by cl.class_id,co.short_name
10 , cl.start_date, l.city
11 having count(r.stud_id) < 10;


SQL> select class_id, short_name
2 , start_date, city, tot_reg
3 from cls_summ
4 where city = 'San Francisco'
5 and tot_reg < 10;

The optimizer can use the materialized view created earlier to satisfy the query.
Note: This query does not exactly match the materialized view definition. You have added a nonjoin predicate on line 8 and a HAVING clause on the last line. The nonjoin predicate is merged into the rewritten query against the materialized view and the HAVING clause is translated into a second component of the WHERE clause.

Dimensions

• Dimensions are data dictionary structures that define hierarchies based on columns in existing database tables.
• Although they are optional, they are recommended because they:
• Enable additional rewrite possibilities without the use of constraints (Implementation of constraints may not be desirable in a data warehouse for performance reasons).
• Help document dimensions and hierarchies explicitly.
• Can be used by OLAP tools.
• Dimensions enable additional query rewrite capabilities, specifically targeted at data warehouse environments.


Dimensions and Hierarchies

• Dimensions describe business entities such as products, departments, and time in a hierarchical, categorized manner.
• A dimension may consist of one or more hierarchies. In the example shown, the time dimension consists of three hierarchies.
• Each hierarchy comprises multiple levels.
• Each value at a lower level in the hierarchy is the child of one and only one parent at the next higher level.
• A hierarchy consists of 1-to-n relationships between levels, with the parent level representing a level of aggregation of the child level.
• In the example, the CALENDAR_ROLLUP hierarchy consists of sales date, month, quarter, and year.
• A level in the hierarchy has a level key that can identify one or more dimensional attributes. In the example, month is a level where the level key MONTH identifies the attribute MONTH_NAME.
• You can use level keys and their related attribute names interchangeably in a query.

Dimensions : Example Table

SQL> select * from time;

SDATE MONTH MONTH_NAME QUARTER YEAR
-------------- ----------- --------------------- --------------- --------
01-JAN-98 1 January 1 1998
02-JAN-98 1 January 1 1998
03-JAN-98 1 January 1 1998
04-JAN-98 1 January 1 1998
...

30-DEC-98 12 December 4 1998
31-DEC-98 12 December 4 1998

You have a table TIME containing time information in several separate columns (date, month, month name, quarter, year.)
On the next two pages you will create a dimension TIME_DIM on this table.


Dimensions can be based on columns in a single table, as shown in this first example. You can also base dimensions on columns from multiple tables.
When creating dimensions, you must define and name hierarchy levels based on the TIME table column names.
In this case, the dimension TIME_DIM has four levels:
• The highest level in the hierarchy consists of the column YEAR.
• The next level is derived from the column QUARTER.
• The third level has the MONTH column as the key and MONTH_NAME as an attribute. Note that each MONTH value maps to a single MONTH_NAME value and vice versa.
• The lowest level is based on the column SDATE.

Dimensions Based on Multiple Tables

A dimension can use columns from several tables. For example, a GEOGRAPHY dimension can use the following tables and columns:
CITY table containing CITY_NAME , OFFICE_ADDRESS, POPULATION, and STATE_CODE
STATE table consisting of STATE_CODE, STATE_NAME, and REGION_ID
REGION table comprising REGION_ID, REGION_NAME, and COUNTRY

In this case, foreign key constraints should be defined on a child table to reference the primary key of the parent table.
An example may be CITY.STATE_CODE referencing STATE.STATE_CODE. This helps maintain dimension validity.
Note that all columns for a given level are stored in the same table.