oracle物化檢視系列(二)
oracle物化檢視知識比較繁雜,本文為oracle物化檢視系列(二)
--子查詢的表名不能與物化檢視的名稱相同
Cause: The prebuilt container table of the materialized view was referenced in the definition query.
Action: Change the definition query to reference a non-prebuilt table.
SQL> desc t_mv;
Name Type Nullable Default Comments
---- ------- -------- ------- --------
A INTEGER Y
create materialized view t_mv on prebuilt table with reduced precision
enable query rewrite
as
select a from t_mv
ORA-32349: cannot reference prebuilt table in definition query for materialized view
--經修正後,物化檢視的名稱可與非子查詢的表名一樣,其作用即根據業務邏輯加工幾個源錶轉變為另一個表,
--另一個表與物化檢視相同
SQL> create table t_same(a int);
Table created
SQL> create materialized view t_same on prebuilt table with reduced precision
2 enable query rewrite
3 as
4 select a from t_mv
5 /
Materialized view created
--刪除物化檢視
SQL> drop materialized view t_same;
Materialized view dropped
---如移除on prebuilt table選項則提示物件已存在
create materialized view t_same
enable query rewrite
as
select a from t_mv
ORA-00955: name is already used by an existing object
物理特性子句
physical_properties_clause
物化檢視的物理特性子句和普通表的語義一樣
The components of the physical_properties_clause have the same semantics for materialized views that they have for tables,
with exceptions and additions described in the sections that follow.
此子句不能指定organization external
Restriction on the physical_properties_clause You cannot specify ORGANIZATION EXTERNAL for a materialized view.
segment_attributes_clause
使用segment_attributes子句指定pctfree,pctused,initrans引數的值;即配置物化檢視的儲存屬性,即指定表空間,是否開
啟日誌功能.而在使用using index子句,不能指定pctfree,pctused
Use the segment_attributes_clause to establish values for the PCTFREE, PCTUSED, and INITRANS parameters,
the storage characteristics for the materialized view, to assign a tablespace, and to specify whether
logging is to occur. In the USING INDEX clause, you cannot specify PCTFREE or PCTUSED.
索引組織表子句
index_org_table_clause
此子句建立索引組織物化檢視.基於物化檢視的主鍵對應的索引儲存每條記錄,可以為如下幾種型別的物化檢視指定索引組織方式
The ORGANIZATION INDEX clause lets you create 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. You can specify index
organization for the following types of materialized views:
只讀且可更新的物化檢視,必須確保master table具備主鍵
Read-only and updatable object materialized views. You must ensure that the master table has a primary key.
只讀且可更新並具有主鍵的物化檢視
Read-only and updatable primary key materialized views.
只讀rowid的物化檢視
Read-only rowid materialized views.
索引組織表子句和create table具有相同的語法
The keywords and parameters of the index_org_table_clause have the same semantics as described in CREATE TABLE, with the restrictions that follow.
索引組織物化檢視一些操作限制
Restrictions on Index-Organized Materialized Views Index-organized materialized views are subject to the following restrictions:
不能指定cache,nocache,cluster,on prebuilt table
You cannot specify the following CREATE MATERIALIZED VIEW clauses: CACHE or NOCACHE, CLUSTER, or ON PREBUILT TABLE.
不能指定mapping_table子句
In the index_org_table_clause:
?
You cannot specify the mapping_table_clause.
僅可基於組合主鍵的物化檢視指定compress,但可基於單一或組合主鍵的物化檢視指定nocompress(因單列無須壓縮,而多列可壓縮)
You can specify COMPRESS only for a materialized view based on a composite primary key. You can specify NOCOMPRESS
for a materialized view based on either a simple or composite primary key.
物化檢視特性
materialized_view_props
用此子句定製基於非已存在的表的物化檢視.如為了基於已存在的表建立物化檢視,使用on prebuilt table子句
Use these property clauses to describe a materialized view that is not based on an existing table.
To create a materialized view that is based on an existing table, use the ON PREBUILT TABLE clause.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-754475/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle物化檢視的建立及使用(二)Oracle
- Oracle普通檢視和物化檢視的區別Oracle
- Oracle物化檢視的建立及使用(一)Oracle
- 物化檢視
- 物化檢視(zt)
- calcite物化檢視詳解
- 【MVIEW】Oracle通過物化檢視同步表資料及簡介ViewOracle
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- 資料庫的物化檢視資料庫
- 物化檢視分割槽實驗
- 物化檢視幾個知識點
- ClickHouse 物化檢視學習總結
- ClickHouse 效能優化?試試物化檢視優化
- ClickHouse效能優化?試試物化檢視優化
- 基於ROWID更新的物化檢視測試
- StarRocks 物化檢視重新整理流程及原理
- StarRocks 物化檢視重新整理流程和原理
- 物化檢視如何快速完成資料聚合操作?
- Postgres使用trigger自動重新整理物化檢視
- 物化檢視快速重新整理與ORA-00001
- 使用Materialise物化檢視解耦微服務架構解耦微服務架構
- 火山引擎ByteHouse:如何最佳化ClickHouse物化檢視能力?
- hg_job配置定時重新整理物化檢視
- 物化檢視日誌無法正常清除的解決方法
- 資料泵匯出匯入物化檢視(ORA-39083)
- Oracle OCP(24):檢視Oracle
- 基於圖神經網路的動態物化檢視管理神經網路
- 用exp、imp遷移包含物化檢視日誌的資料
- 【TUNE_ORACLE】Oracle檢查點(二)檢查點效能Oracle
- oracle 檢視錶空間Oracle
- 11、Oracle中的檢視Oracle
- 使用SpringBoot+PostgreSQL物化檢視實現微服務設計模式 - vinsguruSpring BootSQL微服務設計模式
- 檢視oracle臨時表空間佔用率的檢視Oracle
- MySQL入門系列:檢視MySql
- Oracle檢視歷史TOP SQLOracleSQL
- oracle檢視物件DDL語句Oracle物件
- 檢視ORACLE中鎖定物件Oracle物件
- ORACLE 檢視IP,解析機器名Oracle
- Oracle相關資料字典檢視Oracle