Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Dynamically create complex objects with Oracle materialized views

April 2, 2002 - Donald Burleson



当现有的Materialized views被用来满足请求时,查询优化器可以自动识别,紧接着就会重新写入请求。查询直接指向materialized view,而不用知道具体细节,这样又使系统性能得到改善。

现在Oracle9I SQL又有了其他查询重写能力,它通常会把相关查询重新写到子目录中,比如,如果有人采用不正确的查询方式使用不存在的语句时,Oracle9I SQL优化器就会自动探测到这种情况。同时会以更快的速度用标准的查询来代替SQL。


select customer_name from customer
where not exists (select customer_name from bad_credit);



Select customer_name
from customer c, bad_credit b
where b.customer_name(+) = c.customer_name
and b.customer_name is null;


图B所示为在新SQL进入Oracle库缓存时Oracle SQL优化器如何检查数据目录的情况。

图 B

Oracle SQL 查询重写

在下一个例子中,我们要创建一个materialized view来确定数据库中每一项工作的平均工资,一旦创建完成,就可以直接进行查询,而且可以从materialized view中直接得到平均工资数,而不用象表2那样要费好长时间通览整个列表。


在SQL查询重写中,Oracle要执行非常复杂的过程,Oracle DBA可以控制SQL优化器到达materialized view以完成整个功能,选择如下:

  • SQL全文匹配使用这个方法,在materialized view定义的查询中,需要把SQL中的select statement语句和select语句进行比较。
  • 部分匹配如果全文匹配失败,优化器就要进行部分比较,优化器把SQL查询内容(从from语句开始)同materialized view定义的查询内容进行比较。
  • 不匹配如果全文和部分比较全失败的话,优化器就要采用查询重写法开启materialized view。
使用materialized view的一些技巧

当使用查询重写时,可以创建materialized view来满足大量的SQL查询需求,例如,如果定义了20个查询,就可以用5到6个已经写好的materialized view来满足需求。

如果不太确信要创建哪一个materialized view,在DBMS_OLAP包中,Oracle也提供一些建议,以帮助你设计和评估materialized view。

  • 如果在查询重写中采用materialized view,那么就必须把他们存储在同一数据库中
  • materialized view可以分割,在不同的列表中也可以定义一个materialized view,materialized view也可以有一个或更多索引

Oracle materialized view的引入大大改善了处理复杂SQL系统的性能,同时也提高了响应时间。






Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational