You are using an outdated browser. Please upgrade to the latest version for the best experience.
How do I narrow my search results?

After you submit your search query:

  1. On the Refine Search results page, select one or more categories of products or services from the left sidebar.
  2. Then for a category with search results, click Select to choose your product and release filters, and then click OK.

For search queries that contain multiple words, surround the query with quotes, and then resubmit your query. Example: "database cloud service"

How do I find the documentation for my product or service?

From the home page, click the technology categories for your products or services. If you do not know which category to pick, try the following features:

  • Click the Find a product tab and search for your product or service.
  • Click Browse All Products & Services at the bottom of the home page to view an alphabetical listing of products and services.
  • Apple Safari: Version 6
  • Google Chrome: Version 29 and later
  • Mozilla Firefox: Version 24 and later
  • Microsoft Internet Explorer: Version 9 and later
Results for materialized views


1 to 60 of 5476 results. Previous page Prev  Next next page
Results per page:   20  | 40 | 60


Database Administrator's Guide, 11g Release 2 (11.2)

Managing Schema Objects

Validating Tables, Indexes, Clusters, and Materialized ViewsTo verify the integrity of the structure of a table, index, cluster, or materialized view, use the … cluster is corrupt, you should drop it and re-create it. If a materialized view is corrupt, perform … , drop and re-create the materialized view. The following statement analyzes the emp table: ANALYZE

Database Upgrade Guide, 11g Release 2 (11.2)

Behavior Changes After Upgrading Oracle Database

Materialized Views… The new names appear in all data dictionary views, but both the old and new names are accepted by the … GRANT and REVOKE SQL statements. CREATE SNAPSHOT changed to CREATE MATERIALIZED VIEW CREATE ANY … SNAPSHOT changed to CREATE ANY MATERIALIZED VIEW ALTER ANY SNAPSHOT changed to ALTER ANY MATERIALIZED

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Naming Materialized Views, you might consider extending this naming scheme to the materialized views so that they are easily … The name of a materialized view must conform to standard Oracle naming conventions. However, if the … materialized view is based on a user-defined prebuilt table, then the name of the materialized view

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

Materialized Views and ModelsExamples of Materialized Views Using UNION ALL The following examples illustrate creation of fast … refreshable materialized views involving UNION ALL. Example 10-5 Materialized View Using UNION ALL … with Two Join Views To create a UNION ALL materialized view with two

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

Invalidating Materialized ViewsDependencies related to materialized views are automatically maintained to ensure correct operation … views is not tracked. Thus, if you use remote materialized views for rewrite, they are considered to be trusted. … . When a materialized view is created, the materialized

Database Concepts, 11g Release 2 (11.2)

Partitions, Views, and Other Schema Objects

Characteristics of Materialized Viewsviews Oracle Database SQL Language Reference to learn about the CREATE MATERIALIZED VIEW statement … Characteristics of Materialized Views Materialized views share some characteristics of nonmaterialized … views and indexes. Materialized views

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Creating Materialized ViewsWhy Use Nested Materialized Views? In a data warehouse, you typically create many aggregate views … distinct materialized aggregate views can take a long time, because the underlying join has to be … performed many times. Using nested materialized views, you can create

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

Compressing Materialized Viewsforeign keys. In particular, materialized views created with the ROLLUP clause are likely candidates

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Materialized Views with AggregatesIn data warehouses, materialized views normally contain aggregates as shown in Example 9-1. For … Materialized Views with Aggregates\". Fast refresh for a materialized view containing joins and … tables upon which the materialized view is based. Here are some examples of materialized

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

10 Advanced Materialized ViewsThis chapter discusses advanced topics in using materialized views. It contains the following … topics: Partitioning and Materialized Views Materialized Views in Analytic Processing Environments … Materialized Views and Models Invalidating Materialized

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

9 Basic Materialized ViewsThis chapter describes the use of materialized views. It contains the following topics: Overview of … Data Warehousing with Materialized Views Types of Materialized Views Creating Materialized Views … Registering Existing Materialized

Database Data Warehousing Guide, 11g Release 2 (11.2)

Maintaining the Data Warehouse

Refreshing Materialized Views… Alternatively, you can control the time when refresh of the materialized views occurs by specifying ON DEMAND … Refresh one or more materialized views. DBMS_MVIEW.REFRESH_ALL_MVIEWS Refresh all materializedviews. DBMS_MVIEW.REFRESH_DEPENDENT Refresh all materialized

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Query Rewrite

Multiple Materialized Views) GROUP BY prod_category; Multiple Materialized Views Que ry rewrite has been extended to enable the … rewrite of a query using multiple materialized views. If query rewrite determines that there is no … set of materialized views that returns all of the data, then query rewrite retrieves

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Registering Existing Materialized ViewsSome data warehouses have implemented materialized views in ordinary user tables. Although this … solution provides the performance benefits of materialized views, it does not: Provide query rewrite to … all SQL applications. Enable materialized views defined in one application to be

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Dropping Materialized ViewsUse the DROP MATERIALIZED VIEW statement to drop a materialized view. For example, consider the … following statement: DROP MATERIALIZED VIEW sales_sum_mv; This statement drops the materialized view … sales_sum_mv. If the materialized view was prebuilt on a table, then the table is not dropped, but it

Database Concepts, 11g Release 2 (11.2)

Partitions, Views, and Other Schema Objects

Overview of Materialized ViewsMaterialized views are query results that have been stored or \"materialized\" in advance as schema … objects. The FROM clause of the query can name tables, views, and materialized views. Collectively … term). Materialized views are used to summarize,

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

Altering Materialized Viewssecurity implications for materialized views accessed in this way are much the same as for indexes: all … . Restrictions with Materialized Views and Virtual Private Database Query rewrite does not use its … you directly specify when you create the materialized view. Altering Materialized

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Types of Materialized ViewsFROM clause of a SELECT statement), subqueries, and materialized views can all be joined or referenced … , such as in the WHERE clause. The types of materialized views are: Materialized Views with Aggregates … Materialized Views Containing Only Joins

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Nested Materialized Viewsview. A nested materialized view can reference other relations in the database in addition to referencing materialized views. … A nested materialized view is a materialized view whose definition is based on another materialized

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

Partitioning and Materialized Views… Partitioning the fact tables also improves the opportunity of fast refreshing the materialized view … because this may enable Partition Change Tracking (PCT) refresh on the materialized view. Partitioning … a materialized view also has benefits for refresh, because the refresh procedure can then use … the materialized

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

Rolling Materialized Viewsor list partitioned. PCT refresh is nonatomic. Rolling Materialized Views When a data warehouse or … tables or materialized views include a time dimension and are horizontally partitioned by the time … attribute, then management of rolling materialized views can be reduced to a few fast

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Query Rewrite

Sample Schema and Materialized ViewsThe following sections use the sh sample schema and a few materialized views to illustrate how the … mainly refer to the following materialized views. These materialized views do not necessarily represent … this chapter. The following materialized views

Database Data Warehousing Guide, 11g Release 2 (11.2)

Maintaining the Data Warehouse

Tips After Refreshing Materialized Viewsre-enable integrity constraints (if any) and refresh the materialized views and materialized view … materialized view data is redundant and can always be reconstructed from the detail tables, it might be … preferable to disable logging on the materialized view. To disable logging and run incremental

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Materialized Views for Mobile ComputingYou can also use materialized views to download a subset of data from central servers to mobile … focuses on the use of materialized views in data warehouses. See Oracle Database Advanced Replication

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Materialized Views Containing Only JoinsSome materialized views contain only joins and no aggregates, such as in Example 9-4, where a … inner join. See \"Restrictions on Fast Refresh on Materialized Views with Joins Only\" for more … information regarding the conditions that cause refresh performance to degrade. Materialized Join Views

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Materialized Views for Data WarehousesIn data warehouses, you can use materialized views to precompute and store aggregated data such as … the sum of sales. Materialized views in these environments are often referred to as summaries … aggregations. A materialized view eliminates the overhead associated with expensive joins and aggregations

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Materialized Views for Distributed ComputingIn distributed environments, you can use materialized views to replicate data at distributed sites … materialized views provide local access to data that otherwise would have to be accessed from remote … sites. Materialized views are also useful in remote data marts. See Oracle Database

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

Materialized Views with Set OperatorsOracle Database provides support for materialized views whose defining query involves set operators … . Materialized views with set operators can now be created enabled for query rewrite. You can … marker. See \"Restrictions on Fast Refresh on Materialized Views with UNION ALL\" for

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

Security Issues with Materialized ViewsTo create a materialized view in your own schema, you must have the CREATE MATERIALIZED VIEW … materialized view in another schema, you must have the CREATE ANY MATERIALIZED VIEW privilege and the … owner of the materialized view needs SELECT privileges to the tables referenced if they are from another

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Choosing Indexes for Materialized Viewsdefined on each materialized view key column. In the case of materialized views containing only joins … The two most common operations on a materialized view are query execution and fast refresh, and … of the materialized view key columns, and might need to join and aggregate over a subset of those

Database Concepts, 11g Release 2 (11.2)

Partitions, Views, and Other Schema Objects

Refresh Methods for Materialized ViewsThe database maintains data in materialized views by refreshing them after changes to their master … huge amounts of data. A fast refresh eliminates the need to rebuild materialized views from scratch … . Thus, processing only the changes can result in a very fast refresh time. Materialized views

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

The Need for Materialized ViewsYou can use materialized views to increase the speed of queries on very large databases. Queries to … determines how the materialized view is refreshed and used by query rewrite. Materialized views … and not to the underlying detail tables. In general, rewriting queries to use materialized

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Adding Comments to Materialized Viewsto data dictionary views for the existing materialized view: COMMENT ON MATERIALIZED VIEW sales_mv IS … You can add a comment to a materialized view. For example, the following statement adds a comment … 'sales materialized view'; To view the comment after the preceding statement execution, the user

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

Benefits of Partitioning Materialized ViewsMaterialized views with multiple aggregate groups give their best performance for refresh and query … effective partitioning scheme for these materialized views is to use composite partitioning (range-list … on ( time, GROUPING_ID ) columns). By partitioning the materialized views this

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Overview of Data Warehousing with Materialized ViewsMaterialized views can perform a number of roles, such as improving query performance or providing replicated … summaries that had been defined. The database administrator creates one or more materialized views, which … . Although materialized views are usually accessed through

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Using Materialized Views with NLS ParametersWhen using certain materialized views, you must ensure that your NLS parameters are the same as … when you created the materialized view. Materialized views with this restriction are as follows … materialized view after a refresh operation. Expressions that generate internal conversion

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Materialized View Schema Designand you require fewer materialized views if your schema design complies with these guidelines. A … applications, so that a database administrator can create or drop materialized views at any time without … Summary management can perform many useful functions, including query rewrite and materialized

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Enabling Query RewriteBefore creating a materialized view, you can verify what types of query rewrite are possible by … materialized view so that many types of query rewrite are possible. Once the materialized view has been … query. Even though a materialized view is defined, it will not automatically be used by the query … QUERY REWRITE

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

Materialized Views in Analytic Processing Environmentsthese types of queries. It also illustrates the best approach for creating materialized views using a common scenario.

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Data Warehousing Terminologythe typical work load, can be used to create materialized views. See Chapter 11, \"Dimensions\" for … fact table, and each foreign key joins with exactly one row of a dimension table. A materialized view … tables. Among builders of data warehouses, a materialized view is also known as a summary.

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Overview of Materialized View Management TasksThe motivation for using materialized views is to improve performance, but the overhead associated … following: Identifying what materialized views to create initially. Indexing the materialized views … . Ensuring that all materialized views and materialized

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Table 9-2 Requirements for Materialized Views with AggregatesRequirements for Using Materialized Views with Aggregates Table 9-2 illustrates the aggregate … requirements for materialized views. If aggregate X is present, aggregate Y is required and aggregate Z … is optional. Table 9-2 Requirements for Materialized Views with Aggregates X Y Z COUNT(expr)

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Storage And Table Compressionforeign keys. This is particularly useful for materialized views created with the ROLLUP clause … Unless the materialized view is based on a user-defined prebuilt table, it requires and occupies … storage space inside the database. Therefore, the storage needs for the materialized view should be … know how much

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Build Method DescriptionBUILD IMMEDIATE Create the materialized view and then populate it with data. BUILD DEFERRED Create … the materialized view definition but do not populate it with data.

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Refresh OptionsMaterialized View Restrictions You should keep in mind the following restrictions: The defining … query of the materialized view cannot contain any non-repeatable expressions ( ROWNUM, SYSDATE, non … datatypes or object REF s. If the materialized view was registered as PREBUILT, the precision of the … as an eligible materialized

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Using Oracle Enterprise ManagerUsing the FORCE Option with Materialized View Logs If you specify FORCE and any items specified … with the ADD clause have already been specified for the materialized view log, Oracle does not return … an error, but silently ignores the existing elements and adds to the materialized view log any items … . Materialized

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Build MethodsTwo build methods are available for creating the materialized view, as shown in Table 9-3. If you … select BUILD IMMEDIATE, the materialized view definition is added to the schema objects in the data … results are stored in the materialized view. Depending on the size of the tables to be scanned … DEFERRED clause, which creates

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

Partition Change Trackingrewrite against partial stale materialized views if partition change tracking on the changed table … not supported for a materialized view that refers to views, remote tables, or outer joins. … It is possible and advantageous to track freshness to a finer grain than the entire materialized … view.

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

Partitioning a Prebuilt TableAlternatively, a materialized view can be registered to a partitioned prebuilt table as illustrated … sum_unit_sales FROM sales s GROUP BY s.time_id, s.cust_id; CREATE MATERIALIZED VIEW … materialized view was registered to use the prebuilt table. This materialized view is eligible for query

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

ROLLUP By Time ROLLUP By Productyear, quarter, month division, brand, item year, quarter division, brand year division all times all products Note that as you increase the number of dimensions and levels, the number of groups to calculate increases dramatically. This example involves 16 groups, but if you were to add just two more dimensions with the same number of levels, you would have 4 x 4 x 4 x 4 = 256 different groups. Also,

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Components of Summary ManagementSummary management consists of: Mechanisms to define materialized views and dimensions. A refresh … mechanism to ensure that all materialized views contain the latest data. A query rewrite capability … materialized views, partitions, and indexes to create. TUNE_MVIEW, which shows you

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Creating Materialized Views with Column Alias ListsCurrently, when a materialized view is created, if its defining query contains same-name columns in … columns. Otherwise, the CREATE MATERIALIZED VIEW statement fails with the error messages of columns … of the materialized view's defining query and the text of user input query are identical. Thus, if … the user specifies select

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Using the DBMS_MVIEW.EXPLAIN_MVIEW ProcedureThe name of an existing materialized view or the query definition or the entire CREATE MATERIALIZED … VIEW statement of a potential materialized view you want to analyze. msg-array The PL/SQL VARRAY that … receives the output. EXPLAIN_MVIEW analyzes the specified materialized view in terms of its refresh

Database Data Warehousing Guide, 11g Release 2 (11.2)

Advanced Materialized Views

CubesWhile data warehouse environments typically view data in the form of a star schema, for analytical SQL queries, data is held in the form of a hierarchical cube. A hierarchical cube includes the data aggregated along the rollup hierarchy of each of its dimensions and these aggregations are combined across dimensions. It includes the typical set of aggregations needed for business intelligence queries.

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Table 9-1 Schema Design GuidelinesSQL Language Reference and \"Types of Materialized Views\" for more information. Guideline 5 … , subject to some restrictions, is to use outer joins in the materialized view definition (that is, in the … CREATE MATERIALIZED VIEW statement). You must not create dimensions in any schema that does not … satisfy these

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Query Rewrite RestrictionsQuery rewrite is not possible with all materialized views. If query rewrite is not occurring when … DBMS_ADVISOR.TUNE_MVIEW to see if the materialized view can be defined differently so that query rewrite is … possible. Also, check to see if your materialized view satisfies all of the following conditions.

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

ORDER BY Clausematerialized views cannot have remote detail tables. Nested materialized views must have a join or … aggregate. Materialized join views and materialized aggregate views with a GROUP BY clause cannot select … from an index-organized table. Restrictions

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Materialized View Logson top of that materialized view, which is the case with nested materialized views. For fast refresh … of materialized views, the definition of the materialized view logs must normally specify the ROWID … clause. In addition, for aggregate materialized views,

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Loading Data into Data WarehousesQUERY_REWRITE_ENABLED = FALSE statement until all the materialized views are refreshed. If … session level to any users who do not require the materialized views to reflect the data from the latest … modes that only materialized views with updated data participate in a query rewrite.

Database Data Warehousing Guide, 11g Release 2 (11.2)

Basic Materialized Views

Refresh Mode Descriptionautomatically at commit time. You can specify how you want your materialized views to be refreshed from … ON COMMIT Refresh occurs automatically when a transaction that modified one of the materialized … view's detail tables commits. This can be specified as long as the materialized view is fast … DBMS_MVIEW package