 |
|
Oracle Tips by Burleson |
Oracle 10g Summary Management Improvements
In this section, we will focus on summary
management features, which include materialized view logs, column
aliases, and explain plans for materialized views.
Materialized View Column Aliases
In general, when a materialized view is
created, if its defining query contains same-name columns in its
select list, the name conflicts need to be resolved by specifying
unique aliases for those columns.
However, using aliases in the select list for
name resolution may have unintended consequences, such as
restricting the use of the full text match query rewrite. For the
full text match rewrite to occur, the text of the materialized view
defining query and the text of the user input query need to be
identical.
To avoid this kind of situation, Oracle10g
extends the CREATE MATERIALIZED VIEW SQL syntax by adding an
optional column alias list.
The column alias list explicitly resolves the
column name conflicts without attaching aliases in the select list
of the materialized view. The syntax of the materialized view column
alias list is identical to those in the CREATE TABLE and CREATE VIEW
statements.
Here is an example showing the use of the new
syntax.
CREATE
MATERIALIZED VIEW sales_mv
(sales_tid, costs_tid)
ENABLE QUERY REWRITE AS
SELECT s.time_id, c.time_id
FROM sales s, products p, costs c
SEE
CODE DEPOT
This improvement helps to provide more rewrite
facilities.
Get the complete Oracle10g story:
To get the code instantly, click here:
Need an Oracle Mentor?
BEI is now offering personal mentors for Oracle DBAs where you can have an
Oracle expert right at your fingertips, anytime day or night. We work with
hundreds of Oracle databases every year, so we know exactly how to quickly
assist you with any Oracle question.
Why risk an unplanned outage? You can now get telephone access to Don
Burleson or any of his Oracle Certified DBAs with more than 20 years of
full-time IT experience. Click here for details:
http://www.dba-oracle.com/service_oracle_backup.htm
|
