Oracle Materialized Views Containing Joins Only
介紹
只包含表連線而沒有聚合的物化檢視。與聚合類似,表連線也是相當消耗資源的操作, 將其結果預先計算並儲存於物化檢視中,可以提高SQL執行效率。
每一個基表(包括inline view)的Rowid必須出現在物化檢視的select部分。這也是該物化檢視可以快速重新整理的必要條件之一。其他條件包括:
- From 語句後的所有表必須建立MV log,且包含rowid
- 不可包含group by或其他聚合函式
- Select語句中不能包含object型別的列
下面是一個例子:
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;
CREATE MATERIALIZED VIEW detail_sales_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT s.ROWID sales_rid, t.ROWID times_rid, s.time_id, t.day_name, s.amount_sold, s.quantity_sold
FROM times t , sales s WHERE t.time_id = s.time_id;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/638844/viewspace-1063190/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Overview of Materialized Views (184)ViewZed
- Refresh Materialized Views (189)ZedView
- [DW]An article about Materialized Views(zz)ZedView
- Extents in Materialized Views and Their Logs (18)ZedView
- Materialized Views in data warehouse environment (185)ZedView
- Validating Tables, Indexes, Clusters, and Materialized ViewsIndexZedView
- Materialized Views in data distributed environment (186)ZedView
- Materialized Views in mobile computing environment (187)ZedView
- Oracle JoinsOracle
- Oracle:ORA-01219:database not open:queries allowed on fixed tables/views onlyOracleDatabaseView
- How To Use DBMS_ADVISOR.TUNE_MVIEW Tuning Materialized ViewsViewZed
- [20121101]物化檢視與表(Materialized Views and Tables).txtZedView
- ORA-02030: can only select from fixed tables/viewsView
- Oracle's V$ Views(轉)OracleView
- Oracle vs PostgreSQL Develop(20) - Materialized ViewOracleSQLdevZedView
- Oracle 10G V$ViewsOracle 10gView
- Lerning Entity Framework 6 ------ Joins and Left outer JoinsFramework
- note of Beginning Oracle SQL-Oracle Data Dictionary ViewsOracleSQLView
- Materialized ViewZedView
- oracle10g materialized view物化檢視示例OracleZedView
- Partition-wise Joins
- 熟悉又陌生的containing blockAIBloC
- joins型別名詞型別
- DML ViewsView
- Oracle vs PostgreSQL Develop(31) - Index Only ScanOracleSQLdevIndex
- oracle 表空間和表 read only遷移後不再read onlyOracle
- drop materialized view hung !!!ZedView
- oracle 資料泵 content=data_onlyOracle
- Oracle Isolation Levels : Read-only (317)Oracle
- oracle10g nested materialized view巢狀物化檢視示例OracleZedView巢狀
- 8.1.1 V$ ViewsView
- 8.1.2 GV$ ViewsView
- Parallel query & viewsParallelView
- External Views (33)View
- Overview of Views (174)View
- materialized view 的總結ZedView
- about materialized view and long(turn)ZedView
- materialized view (物化檢視)ZedView