Матвью oracle что такое
Русские Блоги
Oracle Materialized View
1. Создайте материализованное представление
Следующее передано от: http://www.itpub.net/thread-1614812-1-1.html
Интеллектуальная рекомендация
Упражнения по поводу scipy (неделя 13)
Метод наименьших квадратов находит x и вычисляет остаток (используя функцию leastsq в scipy.optimize). Код: результат: Используйте функцию minim_scalar () в scipy.optimize, чтобы минимизировать (макси.
HTML js очищает значение в select, js контролирует добавление, удаление, изменение, выделение, очистку и определяет, существует ли элемент управления
Очистить выбранные элементы Если вы оставите первую строку [color = red] Оцените, есть ли элемент со значением = «paraValue» [/ color] [color = red] Добавить элемент [/ color] в опцию выбора.
Vuex разминка
— это объект, который может быть доступен все компоненты; Обобщенная
режим управления государством Применимый：1. Несколько компонентов публикации данных; 2. Несколько компонентов меняю.
VS2010 Компилированные результаты HellowOrld
Зеркало
Управляйте заданным двоичным деревом, преобразуйте его в зеркало источника два разветвленного дерева.
CREATE MATERIALIZED VIEW
The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.
Oracle Database Advanced Replication for information on the types of materialized views used to support replication
Oracle Data Warehousing Guide for information on the types of materialized views used to support data warehousing
The privileges required to create a materialized view should be granted directly rather than through a role.
To create a materialized view in your own schema:
You must have been granted the CREATE MATERIALIZED VIEW system privilege an d either the CREATE TABLE or CREATE ANY TABLE system privilege.
You must also have access to any master tables of the materialized view that you do not own, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.
To create a materialized view in another user’s schema:
You must have the CREATE ANY MATERIALIZED VIEW system privilege.
The owner of the materialized view must have the CREATE TABLE system privilege. The owner must also have access to any master tables of the materialized view that the schema owner does not own (for example, if the master tables are on a remote database) and to any materialized view logs defined on those master tables, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.
To create a refresh-on-commit materialized view ( ON COMMIT REFRESH clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege.
If the schema owner does not own the master tables, then the schema owner must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside the schema.
If you are defining the materialized view on a prebuilt container ( ON PREBUILT TABLE clause), then you must have the SELECT privilege WITH GRANT OPTION on the container table.
The user whose schema contains the materialized view must have sufficient quota in the target tablespace to store the master table and index of the materialized view or must have the UNLIMITED TABLESPACE system privilege.
When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.
CREATE TABLE, CREATE VIEW, and CREATE INDEX for information on these privileges
Oracle Database Advanced Replication for information about the prerequisites that apply to creating replication materialized views
Oracle Data Warehousing Guide for information about the prerequisites that apply to creating data warehousing materialized views
Description of the illustration create_materialized_view.gif
Description of the illustration physical_properties.gif
Description of the illustration materialized_view_props.gif
Description of the illustration scoped_table_ref_constraint.gif
Description of the illustration index_org_table_clause.gif
Description of the illustration key_compression.gif
Description of the illustration index_org_overflow_clause.gif
Description of the illustration create_mv_refresh.gif
Description of the illustration segment_attributes_clause.gif
Description of the illustration physical_attributes_clause.gif
Description of the illustration logging_clause.gif
Description of the illustration table_compression.gif
Description of the illustration column_properties.gif
Description of the illustration object_type_col_properties.gif
Description of the illustration substitutable_column_clause.gif
Description of the illustration nested_table_col_properties.gif
Description of the illustration varray_col_properties.gif
Description of the illustration LOB_storage_clause.gif
Description of the illustration LOB_parameters.gif
Description of the illustration LOB_partition_storage.gif
Description of the illustration parallel_clause.gif
Description of the illustration build_clause.gif
Specify the name of the materialized view to be created. Oracle Database generates names for the table and indexes used to maintain the materialized view by adding a prefix or suffix to the materialized view name.
You can specify a column alias for each column of the materialized view. The column alias list explicitly resolves any column name conflict, eliminating the need to specify aliases in the SELECT clause of the materialized view. If you specify any column alias in this clause, you must specify an alias for each data source referenced in the SELECT clause.
ON PREBUILT TABLE Clause
The ON PREBUILT TABLE clause lets you register an existing table as a preinitialized materialized view. This clause is particularly useful for registering large materialized views in a data warehousing environment. The table must have the same name and be in the same schema as the resulting materialized view.
If the materialized view is dropped, then the preexisting table reverts to its identity as a table.
This clause assumes that the table object reflects the materialization of a subquery. Oracle strongly recommends that you ensure that this assumption is true in order to ensure that the materialized view correctly reflects the data in its master tables.
Restrictions on Using Prebuilt Tables Prebuilt tables are subject to the following restrictions:
Each column alias in subquery must correspond to a column in the prebuilt table, and corresponding columns must have matching datatypes.
If you specify this clause, then you cannot specify a NOT NULL constraint for any column that is not referenced in subquery unless you also specify a default value for that column.
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.
Restriction on the physical_properties_clause You cannot specify ORGANIZATION EXTERNAL for a materialized view.
TABLESPACE Clause Specify the tablespace in which the materialized view is to be created. If you omit this clause, then Oracle Database creates the materialized view in the default tablespace of the schema containing the materialized view.
physical_attributes_clause and storage_clause for a complete description of these clauses, including default values
logging_clause Specify LOGGING or NOLOGGING to establish the logging characteristics for the materialized view. The default is the logging characteristic of the tablespace in which the materialized view resides.
logging_clause for a full description of this clause
Use the table_compression clause to instruct the database whether to compress data segments to reduce disk and memory use. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression.
table_compression clause of CREATE TABLE for more information on table compression
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:
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.
Read-only rowid materialized views.
Restrictions on Index-Organized Materialized Views Index-organized materialized views are subject to the following restrictions:
In the index_org_table_clause :
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.
The CLUSTER clause lets you create the materialized view as part of the specified cluster. A cluster materialized view uses the space allocation of the cluster. Therefore, you do not specify physical attributes or the TABLESPACE clause with the CLUSTER clause.
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.
The column_properties clause lets you specify the storage characteristics of a LOB, nested table, varray, or XMLType column. The object_type_col_properties are not relevant for a materialized view.
CREATE TABLE for detailed information about specifying the parameters of this clause
The table_partitioning_clauses let you specify that the materialized view is partitioned on specified ranges of values or on a hash function. Partitioning of materialized views is the same as partitioning of tables.
table_partitioning_clauses in the CREATE TABLE documentation
For data that will be accessed frequently, CACHE specifies that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables. NOCACHE specifies that the blocks are placed at the least recently used end of the LRU list.
CREATE TABLE for information about specifying CACHE or NOCACHE
The parallel_clause lets you indicate whether parallel operations will be supported for the materialized view and sets the default degree of parallelism for queries and DML on the materialized view after creation.
The build_clause lets you specify when to populate the materialized view.
IMMEDIATE Specify IMMEDIATE to indicate that the materialized view is to be populated immediately. This is the default.
USING INDEX Clause
The USING INDEX clause lets you establish the value of the INITRANS and STORAGE parameters for the default index Oracle Database uses to maintain the materialized view data. If USING INDEX is not specified, then default values are used for the index. Oracle Database uses the default index to speed up incremental ( FAST ) refresh of the materialized view.
Restriction on USING INDEX clause You cannot specify the PCTUSED parameter in this clause.
USING NO INDEX Clause
Specify USING NO INDEX to suppress the creation of the default index. You can create an alternative index explicitly by using the CREATE INDEX statement. You should create such an index if you specify USING NO INDEX and you are creating the materialized view with the incremental refresh method ( REFRESH FAST ).
Use the create_mv_refresh clause to specify the default methods, modes, and times for the database to refresh the materialized view. If the master tables of a materialized view are modified, then the data in the materialized view must be updated to make the materialized view accurately reflect the data currently in its master tables. This clause lets you schedule the times and specify the method and mode for the database to refresh the materialized view.
PL/SQL Packages and Types Reference for more information on refresh methods
Specify FAST to indicate the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. The changes for conventional DML changes are stored in the materialized view log associated with the master table.The changes for direct-path INSERT operations are stored in the direct loader log.
For both conventional DML changes and for direct-path INSERT operations, other conditions may restrict the eligibility of a materialized view for fast refresh.
Materialized views are not eligible for fast refresh if the defining query contains an analytic function.
Oracle Database Advanced Replication for restrictions on fast refresh in replication environments
Oracle Data Warehousing Guide for restrictions on fast refresh in data warehousing environments
The EXPLAIN_MVIEW procedure of the DBMS_MVIEW package for help diagnosing problems with fast refresh and the TUNE_MVIEW procedure of the DBMS_MVIEW package correction of fast refresh problems
Specify COMPLETE to indicate the complete refresh method, which is implemented by executing the defining query of the materialized view. If you request a complete refresh, then Oracle Database performs a complete refresh even if a fast refresh is possible.
Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. This clause may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.
Restrictions on Refreshing ON COMMIT
This clause is not supported for materialized views containing object types.
If you specify this clause, you cannot subsequently execute a distributed transaction on any master table of this materialized view. For example, you cannot insert into the master by selecting from a remote table. The ON DEMAND clause does not impose this restriction on subsequent distributed transactions on master tables.
PL/SQL Packages and Types Reference for information on these procedures
Oracle Data Warehousing Guide on the types of materialized views you can create by specifying REFRESH ON DEMAND
Specify a datetime expression for the first automatic refresh time.
Specify a datetime expression for calculating the interval between automatic refreshes.
Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, then the database determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view. If you specify a START WITH value but omit the NEXT value, then the database refreshes the materialized view only once. If you omit both the START WITH and NEXT values, or if you omit the create_mv_refresh entirely, then the database does not automatically refresh the materialized view.
WITH PRIMARY KEY Clause
Specify WITH ROWID to create a rowid materialized view. Rowid materialized views are useful if the materialized view does not include all primary key columns of the master tables. Rowid materialized views must be based on a single table and cannot contain any of the following:
Distinct or aggregate functions
GROUP BY or CONNECT BY clauses
Rowid materialized views are not eligible for fast refresh after a master table reorganization until a complete refresh has been performed.
USING ROLLBACK SEGMENT Clause
This clause is not valid if your database is in automatic undo mode, because in that mode Oracle Database uses undo tablespaces instead of rollback segments. Oracle strongly recommends that you use automatic undo mode. This clause is supported for backward compatibility with replication environments containing older versions of Oracle Database that still use rollback segments.
MASTER MASTER specifies the remote rollback segment to be used at the remote master site for the individual materialized view.
LOCAL LOCAL specifies the remote rollback segment to be used for the local refresh group that contains the materialized view. This is the default.
Oracle Database Advanced Replication for information on specifying the local materialized view rollback segment using the DBMS_REFRESH package
The USING TRUSTED CONSTRAINTS clause lets Oracle Database use dimension and constraint information that has been declared trustworthy by the database administrator but that has not been validated by the database. If the dimension and constraint information is valid, then performance may improve. However, if this information is invalid, then the refresh procedure may corrupt the materialized view even though it returns a success status.
If you omit this clause, then the default is USING ENFORCED CONSTRAINTS.
NEVER REFRESH Clause
Specify FOR UPDATE to allow a subquery, primary key, object, or rowid materialized view to be updated. When used in conjunction with Advanced Replication, these updates will be propagated to the master.
QUERY REWRITE Clause
The QUERY REWRITE clause lets you specify whether the materialized view is eligible to be used for query rewrite.
ENABLE Clause Specify ENABLE to enable the materialized view for query rewrite.
Restrictions on Enabling Query Rewrite Enabling of query rewrite is subject to the following restrictions:
Query rewrite is disabled by default, so you must specify this clause to make materialized views eligible for query rewrite.
After you create the materialized view, you must collect statistics on it using the DBMS_STATS package. Oracle Database needs the statistics generated by this package to optimize query rewrite.
Oracle Data Warehousing Guide for more information on query rewrite
PL/SQL Packages and Types Reference for information about the DBMS_STATS package
The EXPLAIN_MVIEW procedure of the DBMS_MVIEW package for help diagnosing problems with query rewrite and the TUNE_MVIEW procedure of the DBMS_MVIEW package correction of query rewrite problems
DISABLE Clause Specify DISABLE to indicate that the materialized view is not eligible for use by query rewrite. A disabled materialized view can be refreshed.
Specify the defining query of the materialized view. When you create the materialized view, Oracle Database executes this subquery and places the results in the materialized view. This subquery is any valid SQL subquery. However, not all subqueries are fast refreshable, nor are all subqueries eligible for query rewrite. The length of the subquery is limited to 64K bytes.
Notes on the Defining Query of a Materialized View The following notes apply to materialized views:
Oracle recommends that you qualify each table and view in the FROM clause of the defining query of the materialized view with the schema containing it.
AS subquery in the CREATE TABLE documentation for some additional caveats
Restrictions on the Defining Query of a Materialized View The materialized view query is subject to the following restrictions:
You cannot define a materialized view with a subquery in the select list of the defining query. You can, however, include subqueries elsewhere in the defining query, such as in the WHERE clause.
Materialized join views and materialized aggregate views with a GROUP BY clause cannot select from an index-organized table.
You cannot create a materialized view log on a temporary table. Therefore, if the defining query references a temporary table, then this materialized view will not be eligible for FAST refresh, nor can you specify the QUERY REWRITE clause in this statement.
If the FROM clause of the defining query references another materialized view, then you must always refresh the materialized view referenced in the defining query before refreshing the materialized view you are creating in this statement.
If you are creating a materialized view enabled for query rewrite, then:
Neither the materialized view nor the master tables of the materialized view can be remote.
If you want the materialized view to be eligible for fast refresh using a materialized view log, then some additional restrictions may apply.
Oracle Data Warehousing Guide for more information on restrictions relating to data warehousing
Oracle Database Advanced Replication for more information on restrictions relating to replication
The following examples require the materialized logs that are created in the «Examples» section of CREATE MATERIALIZED VIEW.
Creating Materialized Aggregate Views: Example The following statement creates and populates a materialized aggregate view on the sample sh.sales table and specifies the default refresh method, mode, and time. It uses the materialized view log created in «Creating a Materialized View Log: Examples», as well as the two additional logs shown here:
Creating Materialized Join Views: Example The following statement creates and populates the materialized aggregate view sales_by_month_by_state using tables in the sample sh schema. The materialized view will be populated with data as soon as the statement executes successfully. By default, subsequent refreshes will be accomplished by reexecuting the defining query of the materialized view:
Creating Prebuilt Materialized Views: Example The following statement creates a materialized aggregate view for the preexisting summary table, sales_sum_table :
In this example, the materialized view has the same name and also has the same number of columns with the same datatypes as the prebuilt table. The WITH REDUCED PRECISION clause allows for differences between the precision of the materialized view columns and the precision of the values returned by the subquery.
Creating Subquery Materialized Views: Example The following statement creates a subquery materialized view based on the customers and countries tables in the sh schema at the remote database:
Creating Primary Key Materialized Views: Example The following statement creates the primary key materialized view catalog on the sample table oe.product_information :
Creating Rowid Materialized Views: Example The following statement creates a rowid materialized view on the sample table oe.orders :
Periodic Refresh of Materialized Views: Example The following statement creates the primary key materialized view emp_data and populates it with data from the sample table hr.employees :
Because the materialized view conforms to the conditions for fast refresh, the database will perform a fast refresh. The preceding statement also establishes storage characteristics that the database uses to maintain the materialized view.
Automatic Refresh Times for Materialized Views: Example The following statement creates the complex materialized view all_customers that queries the employee tables on the remote and local databases:
The preceding statement also establishes storage characteristics for both the materialized view and the index that the database uses to maintain it:
The first STORAGE clause establishes the sizes of the first and second extents of the materialized view as 50 kilobytes each.
The second STORAGE clause, appearing with the USING INDEX clause, establishes the sizes of the first and second extents of the index as 25 kilobytes each.
This materialized view requires that materialized view logs be defined on order_items (with product_id as a join column) and on inventories (with quantity_on_hand as a filter column). See «Specifying Filter Columns for Materialized View Logs: Example» and «Specifying Join Columns for Materialized View Logs: Example».
Creating a Nested Materialized View: Example The following example uses the materialized view from the preceding example as a master table to create a materialized view tailored for a particular sales representative in the sample oe schema: