Hi,

I didn't get chance to use Materialized views in my job yet but to sharpen my skills for future, I was working on it.

I created a table EMP from HR.EMPLOYEES table. EMPLOYEES table has 107 rows in it. I copied it to EMP table multiple times and total number of rows in it are 54784 now.

After setting SET TIMING ON

1. First, I ran SELECT statement on EMP table without creating Materialized View and
2. then I create a Materialized View called EMP_MV and
3. I reran the SELECT statement both on EMP table and EMP_MV view.

Timing came almost same in both cased (with and without Materialize view).

Can somebody pls show me a scenario, which can really make me believe that Materialize Views are really worth to use?

Thanks in advance...!! Will appreciate your response..!!!

--------------------------------------------------------------------------------------------------------------------------------------------
SQL> select count(1) from emp;

COUNT(1)
----------
54784
--------------------------------------------------------------------------------------------------------------------------------------------

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56469 | 7334K| 141 (1)| 00:00:02 |
| 1 | TABLE ACCESS FULL| EMP | 56469 | 7334K| 141 (1)| 00:00:02 |
--------------------------------------------------------------------------

54784 rows selected.

Elapsed: 00:02:19.61
--------------------------------------------------------------------------------------------------------------------------------------------
Execution Plan
----------------------------------------------------------
Plan hash value: 1199708657

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44487 | 5778K| 127 (1)| 00:00:02 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| EMP_MV | 44487 | 5778K| 127 (1)| 00:00:02 |
---------------------------------------------------------------------------------------

54784 rows selected.

Elapsed: 00:02:19.61

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

Views: 467

Comment by Eddie Awad on August 20, 2010 at 12:57pm
I suggest you read the Basic Materialized Views chapter in the Oracle Database Data Warehousing Guide first.
Comment by Parry on August 20, 2010 at 1:04pm
Sure. Thanks Eddie.

Comment

You need to be a member of Oracle Community to add comments!

Join Oracle Community

Oracle Community On

I'm not a fan of advertising, and so I will not be including any advertisements on OracleCommunity.net. However, managing this community does not come without cost! If you are willing to donate to help pay for the monthly community fees and domain services I accept Bitcoin and PayPal donations.

Donate Bitcoins

Badge

Loading…

Blog Posts

Routing Oracle AQ messages using Apache Camel in ServiceMix

I'm working in how to connect Oracle AQ JMS messaging with other systems, using Apache Camel in ServiceMix: https://fcosfc.wordpress.com/2016/02/06/routing-oracle-aq-messages-using-apache-camel-in-servicemix/

I hope it will be useful!

Posted by Paco Saucedo on February 13, 2016 at 2:37am

Start a great career in Data Integration with Oracle Training and Certification Courses

Organizations today rely on data integration technologies in order to gain a unified view of data coming from disparate sources for informed and timely decision making. These technologies bring together data from multiple sources and transform them into meaningful and valuable information. Oracle is one of the leading providers of data integration technologies that are widely accepted by organizations all over the world. Oracle training and certification courses in the field of data…

Continue

Posted by Michael Warne on January 25, 2016 at 1:57am

Roll Forward Physical standby database in 12c

As you are aware that rolling forward a physical standby via an incremental SCN backup method is the simplest method of getting your standby database with lag into sync with the primary database. The lag can be due to missing archives on the primary which haven’t been shipped or applied on the standby.

 

With 12c, the roll forward technique is a bit different. In the prior versions, we had to initially take an incremental backup of the primary database from the SCN where the…

Continue

Posted by Shivananda Rao P on January 11, 2016 at 11:37pm

Learn IBM Sterling B2B Integrator Online Training at VirtualNuggets

VirtualNuggets is Offering Instructor Led Live IBM Sterling B2B Integrator Online Training. we are the Industry leader in providing IBM Sterling B2B Integrator Tools Training Services, Offering Corporate Trainings Both In Online and In-House and Online Training for Individuals. VirtualNuggets is Offering Online Training for all IT technologies across the globe. Our IBM Sterling B2B Integrator online training methodology focused on hands on experience of Sterling Integrator. We also arrange…

Continue

Posted by Vasu Buddi on October 9, 2015 at 11:25am

© 2016   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service