Oracle SQL Tuning and CBO Internal Materialized Views

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

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

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

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

|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!