Data Warehousing New Features - Oracle 11g

Data warehousing features are rich with functionality to improve information management.The functionality in Oracle Database 11g includes the ability to change reporting and aggregation summary outputs with simple additions to the SQL language.In addition,Oracle captures and tracks change information and provides data staleness validation to businesses.

These features are just a fraction of the functionality provided in the data warehouse environment.First we’ll focus on the improvements introduced to materialized views.The next topic of discussion will center on the new ways to use pivot and unpivot operations to create executive reports using simple SQL statements.

Partition Change Tracking

Although partition change tracking (PCT) was introduced in Oracle 9i Database, Oracle did not expose this information in the data dictionary views. Oracle Database 11g exposes new views and adds columns to existing views to provide essential information to DBAs and developers to make well-informed decisions about materialized view refresh staleness. Materialized view refresh information is provided down to the partition level.Users can now query the data dictionary and see whether the materialized view partition is refreshed with updated data or is still stagnant with stale data.

Note All the views that will be mentioned in this section apply to the DBA_, ALL_, and USER_ views. For simplicity, only the ALL_ view names will be used in the examples so that view names do not have to be repeated three times.

Four views are pertinent to materialized view freshness:


The first two views, ALL_MVIEWS and ALL_DETAIL_RELATIONS, have additional columns.The ALL_MVIEW_DETAIL_PARTITION andALL_MVIEW_DETAIL_SUBPARTITION views are new to Oracle Database 11g.Let’s start with the ALL_MVIEWS view. When you describe ALL_MVIEWS, you will notice three new columns:

The NUM_PCT_TABLES, NUM_FRESH_PCT_REGIONS,andNUM_STALE_PCT_REGIONS columns are new additions to Oracle Database 11g to show how many detail partitions support PCT and the amount of stale and fresh PCT regions.

The ALL_MVIEW_DETAIL_RELATIONS view represents the named detail relations that either are in the from list of a materialized view or are indirectly referenced through views in the from list. Additionally, Oracle provides three new columns to the ALL_MVIEW_DETAIL_ RELATIONS view, as shown next, to inform whether PCT is supported and, if so, to show the number of fresh and stale partition regions:


Oracle Database 11g introduces a new view called ALL_MVIEW_DETAIL_PARTITION to provide freshness information for each PCT partition, as shown here:

The ALL_ MVIEW_DETAIL_PARTITION view provides freshness information of each PCT detail partition in the materialized view.Also new to Oracle Database 11g is the ALL_MVIEW_ DETAIL_SUBPARTITION view.Similar to the ALL_ MVIEW_DETAIL_PARTITION view, this view provides freshness information for each PCT detail subpartition in the materialized view.

Materialized View Refresh Performance Improvements

Several enhancements to the materialized view refresh mechanism areintroduced in Oracle Database 11g to reduce time and throughput.All of the enhancements are made automatically by Oracle.Prior to Oracle Database 11g,when a materialized view was created with a union all,an index was not created.

To take advantage of the performance implications associated with a fast refresh,an index needed to be created manually.Oracle Database 11g creates this index for you automatically.Another improvement that is available is partition change tracking for materialized views with union all clauses.

This materialized view improvement may be more noticeable of all the materialized view improvements.During the atomic materialized view refresh process,query rewrite against the materialized view will continue to work. Applications will see the data at the transactional state prior to the last materialized view refresh.For this to work, you must set thequery_rewrite_integrity initialization parameter to stale_tolerated.

Materialized View QUERY REWRITE Enhancements

When a SQL query being executed equals the query that generated the materialized view,a rewrite occurs to use the materialized view instead of executing the query against the underlying base tables.The ultimate goal of the cost-based optimizer is to select an execution path with the least cost.

The cost-based optimizer generates the plan using the materialized view and another plan against the base tables of the SQL statement and chooses the favorable plan based on cost.If there are situations of nested materialized views,the optimization process occurs again to see whether another rewrite operation can utilize another materialized view to reduce the amount of data. Query rewrite recursively occurs until all the possible materialized views are exhausted.This process of query rewrite can significantly improve performance by reducing the amount of data to be processed.

Oracle Database 11g increases the eligibility of query rewrites.In previous releases,the syntax of the inline views in the materialized view needed to exactly match the syntax of the submitted query.Oracle Database 10g treated the inline views as named views.In Oracle Database 11g, this functionality is enhanced by allowing equivalent inline views to be rewritten.An inline view is considered to be equivalent when it meets the conditions listed here:

  • The select columns are the same.
  • The from clause lists the same or equivalent objects.
  • The having clause is the same.
  • Join conditions, including where clauses, are equivalent.

Let’s take a look at a materialized view that has query rewrite enabled.This particular materialized view joins data from two tables,ORDER_HEADER and ORDER_DETAIL,and contains an inline view:

Let’s look at the submitted query to see whether it qualifies for query rewrite.The following query does not match the inline view of the materialized view,but query rewrite will use the materialized view:

If you observe carefully, the inline views do not match. Notice that the order of the table names in the from clause are listed in reverse order.In the previous release,this query would not be a candidate for a query rewrite.In Oracle Database 11g,Oracle is aware of the same table names in the inline views and matches the table names based on the object number.Query rewrite takes place and leverages the materialized view.

You can use an explain plan to identify the materialized view object usage or use the DBMS_MVIEW.EXPLAIN_REWRITE procedure to validate whether a query rewrite truly occurred. Obviously,the best test-case scenariois if the query runs with the desired performance gain of using the materialized view.It never hurts to perform an explain plan to validate that query rewrite has truly occurred.Here, we will execute an explain plan for the submitted query and confirm that query rewrite has occurred:

You can query the OPERATION and OBJECT_NAME columns from PLAN_TABLE to confirm that the query rewrite of the materialized view occurred.You should expect to see the operation mat_view rewrite access in the output, as illustrated earlier.

The following are elements that disqualify a query for rewrite:

  • Grouping set clauses
  • Set operators
  • Nested subqueries
  • Nested inline views

Query rewrite for remote tables is not supported, but query rewrite can be used on materialized views that reference remote tables.The gains achieved from query rewrite can be astronomical considering the elimination of a remote join and reducing (or even eliminating) network traffic. Because Oracle is not aware of the integrity constraints of the remote database,query rewrite will not use any constraint information. As usual, there are some caveats associated with this rule:

  • The materialized view can reference only a single remote database.
  • The materialized view must reside on the local database.
  • The query_rewrite_integrity initialization parameter must be set to stale_tolerated.

Let’s see an example of how this works.The following query will create a materialized view that references remote tables using a database link:

In this example,all the remote tables are from the single database,andthus,the materialized view qualifies for query rewrite.Now,let’s look at the submitted query to see whether it will take advantage of the new materialized view:

Since this query references all the remote tables from a single database, Oracle will rewrite the query to utilize the local materialized view and thus eliminate network traffic to access the remote tables.

Pivot Operators

Pivoting records is a common business intelligence practice that produces executive summary information and calculations.Oracle Database 11g provides the ability to easily produce crosstabular reports as part of the SQLstatement.Oracle introduces the new pivot operator to transform multiple rows into more columns and,in the process, aggregate data.A pivot operation typically produces additional columns by condensing large data volumes into smaller understandable summaries.

The classic examples of using the pivot operation are monthly salesreports,quarterly sales reports,summarized marketing analysis data,and so on.For example,you can take the monthly sales data and pivot the rows so that each month of data presented in a row will now show up as a column in a single inline row.

In the previous releases of Oracle, you could accomplish a pivot operation using case statements,as shown here:

This same query can be rewritten with the pivot syntax:

select *
from (select video_name, month, quantity_rented
from video_mstr_vw)
pivot (sum(quantity_rented)
for month in
('01' as jan,
'02' as feb,
'03' as mar,
'04' as apr,
'05' as may,
'06' as jun,
'07' as jul,
'08' as aug,
'09' as sep,
'10' as oct,
'11' as nov,
'12' as dec ))
order by video_name desc;

The pivot operator is a breeze compared to the old way of writing the same code.This example shows that the pivot operation is performed on the month with aggregation functions on the QUANTITY_RENTED column.The cost-based optimizer is optimized to handle pivot aggregations more efficiently in Oracle Database 11g.

The pivot clause can be specified with multiple pivot columns.Additional options include the capability to aggregate on multiple columns, use wildcards,and use aliases.You can pivot on multiple aggregations using the syntax pivot (expression) as Alias,(expression) as Alias2 for column.The pivot column must be a column of the table.If an expression needs to be pivoted, you should create an alias. Now we’ll show an example of pivoting on multiple columns.This example will use the alias names for the pivoted columns to perform the sorting:

Note that the output results in four aliased columns (SR_JAN, IR_JAN,SR_FEB, and IR_FEB) from the in list.If the in list does not have an alias,the column heading will be same as the values specified in the in list.Notice that we are able to leverage the alias names in the order by clause.You can see from the next example that without aliases in the in list,the column headings have the values of the in_clause as the column headings:

Notice the virtual headings can be used in the order by clause.Oracle offers the facility to provide a wildcard argument or subquery in the pivoting columns.You can use the any keyword for wildcard,and all values of the pivot columns will be used as pivot columns.You can use the subquery option to specify pivot column values based on a query.

The pivot xml keywords are required to specify a subquery or to use the any keyword in pivot_in_clause.The any and subquery syntax can be used if you are not familiar with the data or do not know the specific values to pivot on.The output of the XML pivot operation will result in XML.

Now, let’s look at what the any keyword does.The any keyword acts as a wildcard. By specifying the any keyword for pivot_in_clause,all the values found in the pivot column will be used for pivoting.There are a couple of things to consider when using the any clause:

  • The any operation can be used only as part of the XML operation.
  • The value for each row must match the input columns from the FOR argument.

Here’s an example of using the any keyword with the pivot xml syntax:

In this particular example,the output will display data only where a valid rental type transaction exists.Sometimes,the preferable situation may be to use a subquery in pivot_in_clause to retrieve the data.In this case,instead of using the any keyword,you can replace pivot_in_ clause with the query select distinct rental_type from rental_types,as shown here:

By using a subquery, the XMLType will show values for RENTAL_TYPE even if there are no values in SUM(QUANTITY_RENTED).The subquery will typically result in a larger output than using the any clause in pivot_in_clause.

Unpivot Operators

The unpivot operation rotates data from columns into rows.The unpivot operation does not reverse a pivot operation.The function of the unpivot operator is to take columns and convert them into rows.

The unpivot operation does not undo any aggregations/summarizations/ calculations that were performed by the pivot operator.Think of unpivot as a mechanism to normalize data structures. unpivot will essentially reduce the number of columns and create morerows. Like the pivot operator,you can use the unpivot operation on multiple columns.Additional options include the ability to measure columns and aliasing.

By taking the previous pivot example, you can see how the unpivot works.A table named VIDEO_RENTAL_PIVOT_TABLE is created as a ctas from the previous multicolumn pivot example. In the following example,the data set is unpivoted on the month column:

Similarly to the pivot operation, the unpivot operation can also be applied on multiple columns and aggregations.The following example will unpivot the data based on store rentals and Internet rentals:

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics