Home arrow Oracle arrow Large Database Features In Oracle, conclusion

Large Database Features In Oracle, conclusion

When you are working with large databases with Oracle Database 10g, there are certain topics with which you need to be familiar. These topics are covered in this article, which is the second of two parts. It is excerpted from chapter nine of the book Oracle Database 10g A Beginner's Guide, written by Ian Abramson et al (McGraw-Hill/Osborne, 2004; ISBN: 0072230789).

  1. Large Database Features In Oracle, conclusion
  2. RAC Architecture
  3. Self-Managing Databases
  4. cube
  5. Analysis Functions
  6. Windowing Functions
  7. Project 9-2 Using Analytic SQL Functions and Models
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 6
December 15, 2005

print this article



Query Rewrite

Earlier, we stated that one of the benefits of using materialized views was that they are transparent to the users. But what exactly does that mean and how can they be

FIGURE 9-8.  Summarization using materialized views

used if the users can’t see them? In fact, because materialized views are so much like tables, you can give the users access to materialized views, but generally this is not done.

Instead, as indicated in Figure 9-8, the users always query the tables with the detail data—they don’t usually query the materialized views directly. This is because the query optimizer in Oracle Database 10g knows about the materialized views and their relationships to the detail tables and can rewrite the query on the fly to access the materialized views instead. This results in huge performance gains without the user having to do anything special—just query the detail data. There is a maintenance benefit of this feature for the user as well: the queries do not have to change to point to different summary tables, as is the case with the more traditional summarization approach.

In order for the query to be rewritten, the structure of the materialized view must satisfy the criteria of the query. The following two listings demonstrate the query rewrite process. Let’s assume we need to summarize the COMMISSION table we saw in the data compression section using the following query:

select prod_id, to_char(comm_date, 'YYYY-MM'), count(*), sum(comm_amt)
from commission
group by prod_id, to_char(comm_date, 'YYYY-MM');

Assume further that a materialized view (called comm_prod_mv) exists that contains summarized commission data by sales_rep_id, prod_id, and comm_date (full date). In this case, the query would be automatically rewritten as follows:

select prod_id, to_char(comm_date, 'YYYY-MM'), count(*), sum(comm_amt)
from comm_prod_mv
group by prod_id, to_char(comm_date, 'YYYY-MM');

By rewriting the query to use the materialized view instead, a large amount of data-crunching has been saved and the results will return much more quickly. Let’s now turn our attention to determining what materialized views should be created.

When to Create Materialized Views

At this point, you may be asking yourself: “How do I determine what materialized views to create and at what level of summarization?” Oracle Database 10g has some utilities to help. These utilities are collectively called the SQLAccess Advisor and will recommend materialized views based on historical queries, or based on theoretical scenarios. They can be run from the Oracle Enterprise Manager Grid Control (OEM) or by calling the dbms_advisor package.

Create Materialized Views

Materialized views are created using a create materialized view statement, which is similar to a create table statement. This can be performed using SQL*Plus or OEM. The following listing shows a simple example of how to create the comm_prod_mv materialized view mentioned earlier and Table 9-16 provides an explanation of the syntax.

1 create materialized view comm_prod_mv
2   tablespace comm_prod_mv_ts
3   storage (initial 50k next 50k)
4   refresh complete next sysdate + 7
5   enable query rewrite
6 as select sales_rep_id, prod_id,
  comm_date, count(*), sum(comm_amt)
7   from commission
8   group by sales_rep_id, prod_id, 

In the next three sections, we will be discussing some higher-level concepts: Real Application Clusters, Automatic Storage Management, and Grid Computing. But first, a progress check.



Important Points


Specify the tablespace and storage parameters.


Specifies how and when to refresh the data. In this case, the materialized view will be populated immediately and be completely refreshed every seven days thereafter.


Specifies that query rewrite is to be enabled.


Specify the query that will act as the source of the data.

TABLE 9-16.  Explanation of Materialized View Creation


Progress Check

  1. True or False: Tables with many foreign keys are good candidates for compression.
  2. Name the two processing components involved in Oracle Database 10g’s parallel processing.
  3. What is the function of the SQLAccess Advisor?
  4. True or False: In order to access the data in a materialized view, a user or application must query the materialized view directly?
  5. List the ways in which parallel processing can be invoked.
  6. In what situation can index key compression not be used on a unique index?



Real Application Clusters: A Primer

When working with large databases, issues such as database availability, performance and scalability are very important. In today’s 24/7 environments, it is not usually acceptable for a database to be unavailable for any length of time—even for planned maintenance or for coping with unexpected failures. Here’s where Oracle Database 10g’s Real Application Clusters (RAC) comes in.

Originally introduced in Oracle9i and only available with the Enterprise Edition, Real Application Clusters is a feature that allows database hardware and instances to be grouped together to act as one database using a shared-disk architecture. Following is a high-level discussion on RAC’s architecture.

Progress Check Answers

  1. True.
  2. The Parallel Execution Coordinator and the Parallel Execution Servers.
  3. The SQLAccess Advisor recommends potential materialized views based on historical or theoretical scenarios.
  4. False. While the end user or application can query the materialized view directly, usually the target of a query is the detail data and Oracle’s query rewrite capabilities will automatically return the results from the materialized view instead of the detail table (assuming the materialized view meets the query criteria).
  5. Parallel processing can be invoked based on the parallelism specified for a table at the time of its creation, or by providing the parallel hint in a select query.
  6. If the unique index has only one attribute, key compression cannot be used.


>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: