Online Documentation for SQL Manager for Oracle

Creating/editing materialized view


Use the Materialized view tab of Materialized View Editor to create/edit a materialized view and specify its properties.

 

Name

Specify the name of the materialized view to be created, or view the name of the materialized view being edited. Note that the materialized view name must be unique within its schema.

 

Schema

Use the drop-down list to specify the schema in which the materialized view will be created.

 

Materialized View Editor - Editing Materialized View definition

 

CheckBox On prebuilt table

The option lets you register an existing table as a preinitialized materialized view. This option is particularly useful for registering large materialized views in a data warehousing environment.

 

CheckBox Updatable

Select this option to allow a subquery, primary key, object, or rowid materialized view to be updated.

 

CheckBox Reduced precision

Select this option to authorize the loss of precision, if the precision of the table or materialized view columns do not exactly match the precision returned by subquery.

 

CheckBox Partitioning

Select this option to create a partitioned materialized view.

 

Materialized view type

Select the type of index organization for the materialized view:

RadioButton Default

RadioButton Organization index

Specifies an index-organized materialized view. In such a materialized view, data rows are stored in an index defined on the primary key of the materialized view.

 

Compress

Use these options to instruct the database whether to compress data segments to reduce disk and memory use:

RadioButton None

RadioButton Compress

RadioButton Nocompress

 

Rewrite query

This group allows you to specify whether the materialized view is eligible to be used for query rewrite:

RadioButton None

RadioButton Enabled

RadioButton Disabled

 

Logging

Use this group to establish the logging characteristics for the materialized view (the default is the logging characteristic of the tablespace in which the materialized view resides):

RadioButton Logging

RadioButton Nologging

 

Cache

These attributes can be used for data that will be accessed frequently:

RadioButton Cache

Specifies that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables.

RadioButton Nocache

Specifies that the blocks are placed at the least recently used end of the LRU list.

 

Cluster schema / Cluster name / Subquery cluster fields

This group of controls allows you create the materialized view as part of the specified cluster. A cluster materialized view uses the space allocation of the cluster.

 

The Subquery area of the editor window allows you to specify the defining query of the materialized view. When you create the materialized

view, Oracle Database executes this subquery and places the results in the materialized view. This subquery is any valid SQL subquery.

 

For your convenience the code folding, syntax highlight, code completion and a number of other features for efficient SQL editing are implemented. For details see Working with Query data editor and Using the context menu.

 

To compile the object, use the corresponding iconCompile Compile item of the Navigation bar or toolbar.