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: 460

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

New Oracle Database Utility,TyphoonDBMS 1.0 Beta (Free) !!!

TyphoonDBMS Software today announces the release of 1.0 Beta Version of Typhoon. TyphoonDBMS is a complex and efficient Java-based software (free) solution that enables user to easily access and manage tables on Oracle Database Servers.



Typhoon is a comprehensive and reliable application that was developed to assist user in managing the contents of Oracle databases, enabling user to add, edit or remove tables without needing to resort to SQL statements to…

Continue

Posted by Prince Kabutey Daker on February 19, 2015 at 3:02am

Hello Oracle Community

Hello everyone in Oracle Community

We are from the Abo Akademi University, Finland.

Currently we are doing a scientific research study in the corporation between Abo Akademi University and Delft University of Technology (Netherlands) about online brand community and the engagement from members.



Oracle is one of the strong communities that we propose to do the research. We hope that you could save 10 minutes helping us doing the survey. If you agree, please follow this link…

Continue

Posted by Giang Nguyen on December 26, 2014 at 8:02am

Oracle AQ: working with PL/SQL asynchronous notifications

I'd like to share a basic example of how to consume Oracle AQ messages using PL/SQL: https://fcosfc.wordpress.com/2014/12/17/oracle-aq-working-with-plsql-asynchronous-notifications/

Posted by Paco Saucedo on December 17, 2014 at 12:45pm

© 2015   Created by Steve Karam.

Badges  |  Report an Issue  |  Terms of Service