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

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

Why you Should Include Push Notifications in your Mobile App?

Push notifications are a display of your application’s icon and a message in the status bar of your device. We stress the importance of including push notifications in your mobile application for three major reasons:-

  1. Push Notifications allow direct marketing to your clients: When a customer downloads your application and enables push notification, it gives you direct…

Continue

Posted by Alvaro Dee on August 23, 2016 at 10:50pm

Amazon Virtual Assistant Services for Efficient e-store Management

Virtual assistant (VA) is a term that has been buzzing around in the business world for quite some time now. Essentially, a VA is skilled administrator who takes care of your technical/creative business support activities while working offsite. If you already have a designated eCommerce store on Amazon seller central or wish to start one, an Amazon VA will help you manage the store and update it regularly with utmost precision.

Amazon VA creates and updates the…

Continue

Posted by Alvaro Dee on August 23, 2016 at 2:56am

What to Expect When Investing in Data Cleansing Services?

Why invest in data cleansing services?

Imagine a scenario- You are maintaining huge databases containing information about your potential customers and you are all geared up with next direct mailing campaigns. After promotional mailers have been sent, you realize the fact that the major part of your database actually contained supplier information.

Such blunders can happen anytime and can…

Continue

Posted by Alvaro Dee on July 25, 2016 at 11:30pm

Avail Affordable and Specialized Android Games Development Services

Cellular gaming has become hugely popular in the last few years. With cellular gaming and amazing features of the Android operating system mixed together, the demand for Android games development services has increased drastically. Gaming becomes fun on Android based Smartphones with its great features getting the users hooked from the word go!

Android supports clarity…

Continue

Posted by Alvaro Dee on July 24, 2016 at 10:47pm

© 2016   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service