Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

Oracle Tips by Burleson

Oracle SQL Tuning and CBO Internal

Chapter 2: Materialized Views

For very large tables in a data warehousing application, materialized views (MVs) are used to drastically improve performance. The MVs are really tables that are refreshed on a regular basis. The following example is an MV on an 880,000-row table. A better example would be an MV with 300 million rows, but disk space is tight for this chapter.

 

The following DESCRIBE command shows there are just two columns in the stats table.

 

SQL>  DESC  STATS

 

Name      Null?  Type

------- -------- -------

STAT_NO          NUMBER

RESULT           CHAR(1)

 

There are 880,000 rows in the STATS table, and the average for the STAT_NO column is 440,001. The STAT_NO column contains values from 1 to 880,000. Notice it requires nearly 5 seconds to return this average.

 

SQL>  SELECT AVG(STAT_NO) -- 880,000 Rows

 2    FROM   STATS;

 

AVG(STAT_NO)

============

      440001

 

real: 4953(Requires 4.953 Seconds)

 

To avoid possible Oracle errors, from SYS grant the system privilege query rewrite to SYSTEM.

 

SQL> GRANT QUERY REWRITE TO SYSTEM;

Grant succeeded.

SQL> CONNECT SYSTEM/MANAGER

 

The query rewrite feature can only be used by the cost-based optimizer. Set your session to either all_rows or first_rows to use the cost-based optimizer.

 

SQL> ALTER SESSION SET OPTIMIZER_GOAL =

       ALL_ROWS;

Session altered.

 

Just to be safe, before creating the MV, enable the query rewrite feature for your session.

 

SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

Session altered.

 

So that you can see how long the following commands require executing, set timing ON for your session:

 

SQL> SET TIMING ON

 

Now you are ready to create the MV named mv_stats  on your 880,000-row table stats.

 

SQL> CREATE  MATERIALIZED VIEW mv_stats

 2           BUILD       IMMEDIATE

 3           REFRESH     COMPLETE

–Truncates And Inserts. Also FAST, FORCE, or NEVER

 4           ENABLE      QUERY REWRITE

 5   AS

 6   SELECT  avg(stat_no) avg_statno,

 7           min(stat_no) min_statno,

 8           max( stat_no) max_statno

 9*  FROM    stats;

Materialized view created.

real: 9688 (Requires 9.688 Seconds)

 

The materialized view, mv_stats, might be used when writing the query shown in Exhibit 3. Use EXPLAIN PLAN to determine if the Oracle optimizer would rewrite your query and use your materialized view mv_stats. The “Name” column in the table plan_table indicates that Oracle would rewrite the statement using your MV instead of performing a full table scan of the 880,000-row stats table.

 

SQL> EXPLAIN PLAN FOR

 2 SELECT avg(stat_no) avg_statno,

 3   min(stat_no) min_statno,

 4   max(stat_no) max_statno

 5*FROM stats;

Explained.

SQL> @F:\V8I\RDBMS\ADMIN\UTLXPLS

 

Plan Table

=============================================================

|Operation          | Name    | Rows| Bytes| Cost|Pstart|Pstop|

-------------------------------------------------------––––––

|SELECT STATEMENT   |         |   21|   819|    1|      |     |

| TABLE ACCESS FULL |MV_STATS |   21|   819|    1|      |     |

------------------------------------------------------–––––––

Exhibit 3. Query Using mv_stats

 

The following query uses your MV and returns the information you need from an 880,000-row table in less than 0.4 seconds. Well, actually, your query is rewritten to use the MV containing one row.

 

SQL> SELECT avg(stat_no) avg_statno,

 2          min(stat_no) min_statno,

 3          max(stat_no) max_statno

 4*  FROM   stats;

 

AVG_STATNO  MIN_STATNO  MAX_STATNO

==========  ==========  ==========

    440001           1      880000

real: 391 (Only Requires .391 Of A Second Using Your MV!)

 

Now try executing the same query not using your MV by setting the optimizer goal to RULE. Wow! It requires Oracle a whopping 6.657 seconds to return the information you need when not using your materialized view. Using your MV only requires less than 0.4 seconds! What a great enhancement, Oracle Corporation!

 

SQL> SELECT avg(stat_no) avg_statno,

 2          min(stat_no) min_statno,

 3          max(stat_no) max_statno

 4*  FROM   stats;

 

AVG_STATNO  MIN_STATNO  MAX_STATNO

==========  ==========  ==========

    440001           1      880000

real: 6657

 


The above text is an excerpt from:

Oracle SQL Tuning & CBO Internals
ISBN 0-9745993-3-6

by Kimberly Floss


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.