- 8-7 Materialized View Schema Design. - 8-8 Materialized View Schema Design Guidelines. - 8-10 Overview of Materialized View Management Tasks. - 8-19 Nested Materialized View Usage Guidelines. - Materialized View Restrictions. - 8-31 Materialized View Logs. - 8-37 Analyzing Materialized View Capabilities. - 9-6 Partitioning a Materialized View. - 9-7 Benefits of Partitioning a Materialized View. - 15-21 Checking the Status of a Materialized View. - Materialized View Refresh Enhancements. - A summary in an Oracle database is called a materialized view.. - Analyzing Materialized View Capabilities. - It then transparently rewrites the request to use the materialized view. - A materialized view can be. - partitioned, and you can define a materialized view on a partitioned table. - You can also define one or more indexes on the materialized view.. - Instead, let query rewrite transparently rewrite your query to use the materialized view.. - Materialized View Schema Design. - A materialized view consumes storage space.. - The contents of the materialized view must be updated when the underlying detail tables are modified.. - alternative, subject to some restrictions, is to use outer joins in the materialized view definition (that is, in the CREATE MATERIALIZED VIEW statement).. - Materialized View Schema Design Guidelines. - Overview of Materialized View Management Tasks. - Example 8–1 Example 1: Creating a Materialized View. - CREATE MATERIALIZED VIEW LOG ON products WITH SEQUENCE, ROWID. - CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID. - CREATE MATERIALIZED VIEW product_sales_mv PCTFREE 0 TABLESPACE demo. - Example 8–3 Example 3: Creating a Materialized View. - CREATE MATERIALIZED VIEW sum_sales PARALLEL. - The rowids of all the detail tables must appear in the SELECT list of the materialized view query definition.. - supported for materialized view with remote tables. - CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;. - CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;. - Basic Materialized Views 8-17 CREATE MATERIALIZED VIEW detail_sales_mv. - CREATE MATERIALIZED VIEW detail_sales_mv PARALLEL. - Example 8–5 Nested Materialized View. - CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;. - create materialized view log on join_sales_cust_time. - CREATE MATERIALIZED VIEW LOG ON join_sales_cust_time. - Nested Materialized View Usage Guidelines. - relative to changes in the tables referenced by the materialized view it references.. - Example 8–6 creates the materialized view cust_sales_mv. - Design the materialized view. - If the materialized view contains many rows, then, if. - appropriate, the materialized view should be partitioned (if possible) and should match the partitioning of the largest or most frequently updated detail or fact table (if possible). - The materialized view is as follows:. - CREATE MATERIALIZED VIEW sales_mv ENABLE QUERY REWRITE AS. - MATERIALIZED VIEW , and DROP MATERIALIZED VIEW. - materialized view column alias list is illustrated in the following example:. - CREATE MATERIALIZED VIEW sales_mv (sales_tid, costs_tid) ENABLE QUERY REWRITE AS. - The defining query of the materialized view cannot contain any non-repeatable expressions ( ROWNUM , SYSDATE , non-repeatable PL/SQL functions, and so on).. - Depending on the materialized view you create, some of the options may not be available.. - This can be specified as long as the materialized view is fast refreshable (in other words, not complex). - COMPLETE Refreshes by recalculating the materialized view's defining query.. - The defining query of the materialized view is restricted as follows:. - Contain all columns from the table referenced in the materialized view.. - For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)". - for the materialized view to be fast refreshable.. - Example 8–7 Example of Refreshing a Nested Materialized View. - An ORDER BY clause is allowed in the CREATE MATERIALIZED VIEW statement. - It is used only during the initial creation of the materialized view. - The ORDER BY clause is not considered part of the materialized view definition. - Materialized View Logs. - Materialized view logs are defined using a CREATE. - are not created on the materialized view. - CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID. - ALTER MATERIALIZED VIEW LOG ON sales ADD ROWID. - Using the FORCE Option with Materialized View Logs. - You can add a comment to a materialized view. - SALES_MV sales materialized view. - ORA-12098: cannot comment on the materialized view.. - Basic Materialized Views 8-35 materialized view tables whenever possible. - materialized view with the CREATE MATERIALIZED VIEW. - defining query of the materialized view. - DROP MATERIALIZED VIEW sales_sum_mv;. - This statement drops the materialized view sales_sum_mv. - If a materialized view is fast refreshable. - First, create the materialized view. - CREATE MATERIALIZED VIEW cal_month_sales_mv BUILD IMMEDIATE. - Then, you invoke EXPLAIN_MVIEW with the materialized view to explain. - REFRESH_PCT N PCT is not possible on any of the detail tables in the materialized view. - REFRESH_COMPLETE If this capability is possible, complete refresh of the materialized view is possible.. - materialized view log is possible.. - 2187 materialized view on external table. - At least one of the detail tables referenced by the materialized view must be partitioned.. - corresponding materialized view row. - CREATE MATERIALIZED VIEW LOG ON SALES WITH ROWID. - CREATE MATERIALIZED VIEW LOG ON TIMES WITH ROWID. - CREATE MATERIALIZED VIEW prod_yr_sales_mv BUILD DEFERRED. - Partitioning a Materialized View. - Benefits of Partitioning a Materialized View. - The materialized view is partitioned on the partitioning key column or join dependent expressions of the detail table.. - CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE (amount_sold, time_id). - CREATE MATERIALIZED VIEW LOG ON times WITH ROWID, SEQUENCE. - CREATE MATERIALIZED VIEW my_groupby_mv REFRESH FAST. - CREATE MATERIALIZED VIEW my_model_mv ENABLE QUERY REWRITE AS. - A materialized view is automatically revalidated when it is referenced. - ALTER MATERIALIZED VIEW mview_name COMPILE;. - However, the owner of the materialized view may establish a VPD policy on the new materialized view. - materialized view is accessed using query rewrite. - Six modifications can be made to a materialized view
Xem thử không khả dụng, vui lòng xem tại trang nguồn hoặc xem
Tóm tắt