Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 
     Oracle:

 
Enabling Materialized Views
 
However, we must make the following changes to Oracle to enable MV’s in your system:
 
 
Grant the following Oracle privileges:
 
grant query rewrite to pubs;
grant create materialized view to pubs;
alter session set query_rewrite_enabled = true;
 
 
Set the following Oracle parameters:
 
 
optimizer_mode = choose
job_queue_interval = 3600
job_queue_processes = 1
query_rewrite_enabled = true
query_rewrite_integrity = enforced
 
 
Let’s take a close look at how this works.  In the following two examples, we will pre-aggregate data and pre-join table together.  Here is a working example showing SQL query re-write in action:
 
 
1. First we create the MV to store department, job and salary summaries in an MV called emp_sum
 
CREATE MATERIALIZED VIEW emp_sum
ENABLE QUERY REWRITE
AS
SELECT
   deptno,
   job,
   SUM(sal)
FROM
   emp
GROUP BY
   deptno,job;


 
 
 
2. Next, we Create Optimizer Statistics and Refresh the Materialized View:

 
execute dbms_utility.analyze_schema('SCOTT','ESTIMATE');

 
execute dbms_mview.refresh('emp_sum');

 
 
 
 
3. Now we can test our MV to ensure that it is using the MV:
 
set autotrace traceonly explain

-- Test the Materialized View:
set autotrace on explain
SELECT
   deptno,
   job,
   SUM(sal)
FROM
   emp
GROUP BY
   deptno, job;
 
Execution Plan
-----------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMP_SUM'
 
Above, we see that the database had dynamically changed the execution plan to reference our pre-aggregated summary, giving the end-user the illusion of instantaneous response time!
 
We also discussed in Tutorial 2 that it takes database resources to pre-join tables together.  The following example shops how we can pre-join two tables and automatically re-write any SQL queries to make them use our pre-joined table:
 
1 – First we create the Materialized View called emp_dept_sum to join the EMP and DEPT tables and summarize salaries:

CREATE MATERIALIZED VIEW emp_dept_sum
ENABLE QUERY REWRITE
AS
SELECT
   dname,
   job,
   SUM(sal)
FROM
   emp e,
   dept d
WHERE
   e.deptno = d.deptno
GROUP BY
   dname,job;
 
2 – Next, we create the SQL Optimizer Statistics and Refresh our new Materialized View:

 

execute dbms_utility.analyze_schema('SCOTT','ESTIMATE');

execute dbms_mview.refresh('emp_dept_sum');

 

3 – Now we can test the Materialized View and verify that the SQL is being re-written:

 

set autotrace on explain

SELECT
   dname,
   job,
   SUM(sal)
FROM
   emp e,
   dept d
WHERE
   e.deptno = d.deptno
GROUP BY
   dname,job;
 
Execution Plan
----------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMP_DEPT_SUM'
 
 
In sum, we must recognize that Materialized views are one of the most important SQL tools in a relational database.  By pre-summarizing data and pre-joining tables, we can get dramatic speed improvements on SQL code, which accesses millions of table rows.


 

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.