ALTER ANALYTIC VIEW

Purpose

Use the ALTER ANALYTIC VIEW statement to rename or compile an analytic view. Additionally, you can modify grouping level caches by adding or dropping a new level grouping cache to a specifed analytic view.

For other alterations, use CREATE OR REPLACE ANALYTIC VIEW.

Prerequisites

To alter an analytic view in your own schema, you must have the ALTER ANALYTIC VIEW system privilege. To alter an analytic view in another user's schema, you must have the ALTER ANY ANALYTIC VIEW system privilege or ALTER ANY TABLE granted on the analytic view.

Syntax

alter_analytic_view::=

Description of the illustration alter_analytic_view.eps

alter_add_cache_clause::=

Description of the illustration alter_add_cache_clause.eps

alter_drop_cache_clause::=

Description of the illustration alter_drop_cache_clause.eps

Semantics

IF EXISTS

Specify IF EXISTS to alter an existing table.

Specifying IF NOT EXISTS with ALTER VIEW results in ORA-11544: Incorrect IF EXISTS clause for ALTER/DROP statement.

schema

Specify the schema in which the analytic view exists. If you do not specify a schema, then Oracle Database looks for the analytic view in your own schema.

analytic_view_name

Specify the name of the analytic view.

RENAME TO

Specify RENAME TO to change the name of the analytic view. For new_av_name, specify a new name for the analytic view.

COMPILE

Specify COMPILE to compile the analytic view.

alter_add_cache_clause

Use this clause to add a new level grouping cache to a specified analytic view like the measure group, level clause and the cache type. Before you add a new level grouping cache, you must ensure that it does not match a previously defined cache with the same measures and levels.

alter_drop_cache_clause

Use this clause to drop an existent level grouping cache from an analytic view. You must specify the attributes of the level grouping you are about to drop, like the measure group and the level clause.

Example: Change the Name of an Analytic View

ALTER ANALYTIC VIEW sales_av RENAME TO mysales_av;

Example: Add a New Level Grouping Cache to an Analytic View

ALTER ANALYTIC VIEW TKHCSGL308_UNITS_AVIEW_CACHE ADD CACHE
    MEASURE GROUP (sales, units, cost)
    LEVELS (TIME.FISCAL.FISCAL_QUARTER, WAREHOUSE);