oracle杂项收款,我的Oracle学习旅程---杂项---物化视图

创建实体视图语法：

CREATE MATERIALIZED VIEW   [schema.] materializede_view

[ OF [schema .] object_type ]| [(scoped_table_ref_constraint)] |

ORGANIZATION_INDEX index_org_table_clause | [

[

[ [segment_attribute_cluase|column_properties ] | [CACHE|NOCACHE ] ] |

[ CLUSTER cluster (column,) ]

] |

[partitioning_clause|parllel_cluse|build_clause] |

[

ON PREBUILT TABLE [ [WITH|WITHOUT] | REDUCED PRECISION ]

] |

[

[ USING INDEX [physical_attribute_clause |TABLESPACE tablespace] ] |

[ USING NO INDEX ]

] refresh_cluse

[ [ FOR UPDATE ] | [ DISABLE | ENABLE ] QUERY REWRITE ] ]

AS subquery;

其中：

scoped_table_ref_constraint为：

SCOPE FOR ( [ref_column|ref_attribute] ) IS [schema.] scpe_table_name

Index_org_table_clause为：

[ (mapping_table_clause) | PCTTHRESHOLD integer | [COMPRESS integer|NOCOMPRESS] ]

[ INCLUDING column_name ] OVERFLOW [ segment_attribute_clause]

refresh_clause 为：

[ NEVER REFRESH |

[ REFRESH | [ USING [ DEFAULT [LOCAL | MASTER] ROLLBACK SEGMENT ] |

[LOCAL | MASTER] ROLLBACK SEGMENT ] rollback_segment ]

| WITH [ PRIMARY KEY | ROWID ]

| NEXT [ START WITH ] date

| ON [ DEMAND | COMMIT ]

| [ FAST | COMPLETE|FORCE ]

]

修改实体视图语法：

ALTER MATERIALIZED VIEW   [schema.] materializede_view

[

[ physical_attributes_clause|

LOB_storage_clause[,...] |

Modify_LOB_storage_clause [,...]|

Partition_clause |

Parallel_clause |

[LOGGING|NOLOGGING] |

allocate_extent_clause |

[CACHE|NOCACHE ]

]|

[

alter_iot_cluse |

USING INDEX physical_attribute_clause |

MODIFY scoped_table_ref_constraint |

REBUILD |

Refresh_cluse

]|

[

[ DISABLE | ENABLE ] QUERY REWRITE |

COMPILE |

CONSIDER FRESH

]

创建实体视图日志语法：

CREATE MATERIALIZED VIEW LOG ON [schema .] table

[{ physical_attributes_clause

| TABLESPACE tablespace

| logging_clause

| { CACHE | NOCACHE }

}

[ physical_attributes_clause

| TABLESPACE tablespace

| logging_clause

| { CACHE | NOCACHE }

]...

]

[parallel_clause] [table_partitioning_clauses]

[WITH

{ OBJECT ID | PRIMARY KEY | ROWID | SEQUENCE | ( column [, column]... ) }

[, { OBJECT ID | PRIMARY KEY | ROWID | SEQUENCE | ( column [, column]... ) }]...

[ "new_values_clause" ]

]

修改实体视图日志语法：

ALTER MATERIALIZED VIEW LOG ON [ schema. ]table

[ physical_attributes_clause

| alter_table_partitioning

| parallel_clause

| logging_clause

| allocate_extent_clause

| { CACHE | NOCACHE }

]

[ ADD

{ { OBJECT ID | PRIMARY KEY | ROWID }

[ ( column [, column ]...) ]

| ( column [, column]... )

}

[, { { OBJECT ID | PRIMARY KEY | ROWID }

[ ( column [, column ]...) ]

| ( column [, column]... )

}

]...

[ new_values_clause ]

]

创建前提：

要有授权创建实体视图的权限(CREATE MATERIALIZED VIEW 或CREATE SNAPSHOT)；

必须有访问各个主表的权限，即有SELECT ANY TABLE 的系统权限。

如果在另外的用户模式下创建实体视图，则：

需要有CREATE ANY MATERIALIZED VIEW或CREATE ANY SNAPSHOT、SELECT ANY TABLE 权限；

必须有CREATE TABLE、SELECT ANY TABLE系统权限。

如果带查询重写有效来创建实体视图，则：

主表的主人必须有QUERY REWRITE系统权限；

如果你不是主表主人，则必须有GLOBAL QUERY REWRITE系统权限；

如果模式主人没有主表，则该模式主人必须有GLOBAL QUERY REWRITE权限

如果你拥有主表，则可以建立实体视图日志。

如果你为其他人建立实体视图日志，则必须有CREATE ANY TABLE和COMMENT ANY TABLE权限。

初始化参数设置：

job_queue_processes = 2

job_queue_interval = 5