Pro Oracle Database 11g Administration (63 page)

MVIEW_NAME LAST_REF LAST_REFR

------------------------------ -------- ---------

SALES_MV FAST 01-AUG-10

Figure 15–2 illustrates the architectural components involved with a fast refresh. The numbers in the boxes represent the sequential flow of the fast-refresh process. If you’re new to MVs, pause a few minutes here and make sure you understand all the components.

372

CHAPTER 15 ■ MATERIALIZED VIEWS

• Logging

• Parallelism

The next section in this chapter shows examples of how to create MVs with various features.

Going Beyond the Basics

Numerous MV features are available. Many of them are related to attributes that you can apply to any table, such as storage, indexing, compression, encryption, and so on. Other features are related to the type of MV created and how it’s refreshed. These aspects are described in the next several subsections.

Creating MVs and Specifying Tablespace for MVs and Indexes

Every MV has an underlying tables associated with it. Additionally, depending on the type of MV, an index may be automatically created. When you create an MV, you can specify the tablespace and storage characteristics for both the underlying table and index. The next example shows how to specify the tablespace to be used for the MV table and a separate tablespace for the index: create materialized view inv_mv

tablespace mv_data

using index tablespace mv_index

as

select

inv_id

,inv_desc

from inv;

You can also specify storage characteristics. For example, if you know you’re loading data into MVs that will rarely be updated, it’s appropriate to set PCTUSED to a high value, such as 95. For example: create materialized view inv_mv

pctused 95

pctfree 5

tablespace mv_data

using index tablespace mv_index

as

select

inv_id

,inv_desc

from inv;

Creating Indexes on MVs

An MV stores its data in a regular database table. Therefore, you can create indexes on the underlying table (just as you can for any other table). In general, follow the same guidelines for creating an index on an MV table as you would a regular table. (See Chapter 8 for more details on creating indexes.) Keep in mind that although indexes can significantly improve query performance, overhead is associated with maintaining the index for any inserts, updates, and deletes. Indexes also consume disk space.

Listed next is an example of creating an index based on a column in an MV. The syntax is the same as for creating an index on a regular table:

374

CHAPTER 15 ■ MATERIALIZED VIEWS

SQL> create index inv_mv_idx1 on inv_mv(region_id) tablespace mv_index; You can display the indexes created for an MV by querying the USER_INDEXES view: select

a.table_name

,a.index_name

from user_indexes a

,user_mviews b

where a.table_name = b.mview_name;

■
Note
If you create a simple MV with the WITH PRIMARY KEY clause that selects from a base table that has a primary key, Oracle automatically creates an index on the corresponding primary-key columns in the MV. If you create a simple MV using the WITH ROWID clause that selects from a base table that has a primary key, Oracle automatically creates an index on a hidden column named M_ROW$$.

Partitioning Materialized Views

You can partition an MV table like any other regular table in the database. If you work with large MVs, you may want to consider partitioning to better manage and maintain a large table. Use the PARTITION

clause when you create the MV. This example builds an MV that is partitioned by range on the DATE_ID

column:

create materialized view inv_mv

partition by range (date_id)

(partition p1

values less than (20100101)

,partition p2

values less than (20110101)

,partition p3

values less than (20120101))

refresh on demand complete with rowid

as

select

inv_id

,inv_desc

,date_id

from inv;

The result set from the query is stored in a partitioned table. You can view the partition details for this table in USER_TAB_PARTITIONS and USER_PART_TABLES (just like any other partitioned table in your database). See chapter 12 for more details on partitioning strategies and maintenance.

375

CHAPTER 15 ■ MATERIALIZED VIEWS

Compressing a Materialized View

As mentioned earlier, when you create an MV, an underlying table is created to store the data. Because this table is a regular database table, you can implement features such as compression. For example: create materialized view inv_mv

compress

as

select

inv_id

,inv_desc

from inv;

You can confirm the compression details with the following query:

select

table_name

,compression

,compress_for

from user_tables where table_name='INV_MV';

Here’s the output:

TABLE_NAME COMPRESS COMPRESS_FOR

------------------------------ -------- ------------

INV_MV ENABLED BASIC

■
Note
Basic table compression doesn’t require an extra license from Oracle. Online transaction processing (OLTP) compression requires the Advanced Compression option, which requires an extra license from Oracle. See the Oracle Database Licensing Information documentation (available on OTN) for details.

Encrypting Materialized View Columns

As mentioned earlier, when you create an MV, an underlying table is created to store the data. Because this table is a regular database table, you can implement features such as encryption of columns. For example:

create materialized view inv_mv

(inv_id encrypt no salt

,inv_desc encrypt)

as

select

inv_id inv_id

,inv_desc inv_desc

from inv;

For the previous statement to work, you must create and open a security wallet for your database.

This feature requires the Advanced Security option from Oracle (which requires an additional license).

You can verify that encryption is in place by describing the MV:

376

CHAPTER 15 ■ MATERIALIZED VIEWS

SQL> desc inv_mv

Name Null? Type

----------------------------------------- -------- --------------------

INV_ID NOT NULL NUMBER ENCRYPT

INV_DESC VARCHAR2(30) ENCRYPT

Building a Materialized View on a Prebuilt Table

In data-warehouse environments, sometimes you need to create a table, populate it with large quantities of data, and then transform it into an MV. Or you may be replicating a large table and find that it’s more efficient to initially populate the remote MV by prebuilding the table with data using Data Pump. Listed next are the steps for building an MV on a prebuilt table:

1. Create a table.

2. Populate it with data.

3. Create an MV on the table created in step 1.

Here’s a simple example to illustrate the process. First, you create a table: create table inv_mv

(inv_id number

,inv_desc varchar2(30)

);

Now, populate the table with data. For example, in a data-warehouse environment, this can be a table loaded using Data Pump, SQL*Loader, or external tables.

Finally, run the CREATE MATERIALIZED VIEW...ON PREBUILT TABLE statement to turn the table into an MV. The MV name and the table name must be identical. Additionally, each column in the query must correspond to a column in the table. For example:

create materialized view inv_mv

on prebuilt table

using index tablespace mv_index

as

select

inv_id

,inv_desc

from inv;

Now the INV_MV object is an MV. If you attempt to drop the INV_MV table, the following error is thrown, indicating that INV_MV is now a materialized view:

SQL> drop table inv_mv;

ORA-12083: must use DROP MATERIALIZED VIEW to drop "MV"."INV_MV"

The prebuilt-table feature is useful in data-warehouse environments where typically there are long periods when a base table isn’t being actively updated. This gives you time to load a prebuilt table and ensure that its contents are identical to the base table. After you create the MV on the prebuilt table, you can fast-refresh the MV and keep it in synch with the base table.

If your base table (specified in the SELECT clause of the MV) is continuously being updated, then creating an MV on a prebuilt table may not be a viable option. This is because there is no way to ensure that the prebuilt table stays in synch with the base table.

377

CHAPTER 15 ■ MATERIALIZED VIEWS

■
Note
For MVs created on prebuilt tables, if you subsequently issue a DROP MATERIALIZED VIEW statement, the underlying table isn’t dropped. This has some interesting implications when you need to modify a base table (like adding a column). For details, see the section later in this chapter on Modifying Base-Table DDL and Propagating to Materialized Views.

Creating an Unpopulated Materialized View

When you create an MV, you have the option of instructing Oracle whether or not to initially populate the MV with data. For example, if it takes several hours to initially build an MV, you may want to first define the MV and then populate it as a separate job.

This example uses the BUILD DEFERRED clause to instruct Oracle not to initially populate the MV with the results of the query:

create materialized view inv_mv

tablespace mv_data

build deferred

refresh complete on demand

as

select

inv_id

,inv_desc

from inv;

At this point, querying the MV results in zero rows returned. At some later point, you can initiate a complete refresh to populate the MV with data.

Creating a Materialized View Refreshed on Commit

You may have a requirement when data is modified in the master table to have it immediately copied to an MV. In this scenario, use the ON COMMIT clause when you create the MV. The master table must have an MV log created on it for this technique to work. Here’s a simple example that creates a table with a primary key, creates an MV log, and then creates an MV refreshed on commit: create table inv(inv_id number primary key,

inv_desc varchar2(30));

--

create materialized view log on inv with primary key;

--

create materialized view inv_mv

refresh

on commit

as

select inv_id, inv_desc from inv;

As data is inserted and committed in the master table, any changes are also available in the MV that would be selected by the MV query.

378

CHAPTER 15 ■ MATERIALIZED VIEWS

The ON COMMIT refreshable MV has a few restrictions you need to be aware of:

• The master table and MV must be in the same database.

• You can’t execute distributed transaction on the base table.

• This approach isn’t supported with MVs that contain object types or Oracle-supplied types.

Also consider the overhead associated with committing data simultaneously in two places; this can impact the performance of a high-transaction OLTP system. Additionally, if there is any problem with updating the MV, then the base table can’t commit a transaction. For example, if the tablespace in which the MV is created becomes full (and can’t allocate another extent), you see an error such as this when trying to insert into the base table:

ORA-12008: error in materialized view refresh path

ORA-01653: unable to extend table MV.INV_MV by 16 in tablespace TBSP2

For these reasons, you should use this feature only when you’re sure it won’t affect performance or availability.

■
Note
You can’t specify an MV to be refreshed with both ON COMMIT and ON DEMAND. In addition, ON COMMIT isn’t compatible with the START WITH and NEXT clauses of the CREATE MATERIALIZED VIEW statement.

Creating a Never-Refreshable Materialized View

You may never want an MV to be refreshed. For example, you may want to guarantee that you have a snapshot of table at a point in time for auditing purposes. Specify the NEVER REFRESH clause when you create the MV to achieve this:

create materialized view inv_mv

tablespace mv_data

using index tablespace mv_index

never refresh

as

select

inv_id

,inv_desc

from inv;

If you attempt to refresh a nonrefreshable MV, you receive this error: ORA-23538: cannot explicitly refresh a NEVER REFRESH materialized view You can alter a never-refreshable view to being refreshable. Use the ALTER MATERIALIZED VIEW

statement to do this:

SQL> alter materialized view inv_mv refresh on demand complete;