Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Modify materialized view query

Tags:

sql

oracle

I need to modify Materialized view query . Is is possible to do the same without droping and recreating it.

like image 301
code990 Avatar asked Nov 25 '10 07:11

code990


People also ask

How do I change the query of a materialized view?

No, you cannot alter the query of a materialized view without dropping it. The CREATE MATERIALIZED VIEW syntax does not support that feature. The ALTER MATERIALIZED VIEW is used to modify an existing materialized view in one or more of the following ways: To change its storage characteristics.

Can we alter materialized view?

The privileges required to alter a materialized view should be granted directly, as follows: The materialized view must be in your own schema, or you must have the ALTER ANY MATERIALIZED VIEW system privilege.

Can you query a materialized view?

You can use a materialized view in any SQL query by referencing the materialized view name as the data source, like a table or standard view. When a query accesses a materialized view, it sees only the data that is stored in the materialized view as of its most recent refresh.

How do I alter a materialized view in postgresql?

Description. ALTER MATERIALIZED VIEW changes various auxiliary properties of an existing materialized view. You must own the materialized view to use ALTER MATERIALIZED VIEW . To change a materialized view's schema, you must also have CREATE privilege on the new schema.


1 Answers

No, you cannot alter the query of a materialized view without dropping it.

The CREATE MATERIALIZED VIEW syntax does not support that feature.

The ALTER MATERIALIZED VIEW is used to modify an existing materialized view in one or more of the following ways:

  • To change its storage characteristics
  • To change its refresh method, mode, or time
  • To alter its structure so that it is a different type of materialized view
  • To enable or disable query rewrite

See Oracle 12c Release 1 Manual for:

  • CREATE MATERIALIZED VIEW syntax: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6002.htm#i2145767

  • ALTER MATERIALIZED VIEW syntax: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_2002.htm#SQLRF00808

like image 144
mcalmeida Avatar answered Oct 04 '22 02:10

mcalmeida