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

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 Consider Microsoft Exchange Server 2013 For Your Organization?

The Microsoft Exchange Server 2013 is packed with features that make Server management efficient, effective and easy. The following article takes a look at the features of this Microsoft product and how it can help organizations manage their servers in a better way. Read on to know.



Server management can be a challenge for organizations and when taken lightly can cost organizations a fortune. Managing huge databases, confidential and sensitive information…

Continue

Posted by Michael Warne on March 28, 2015 at 2:08am

IBM WODM Online Training @VirtualNuggets:

Web URL       :  http://www.virtualnuggets.com/ibm-websphere-odm.html

Email               : info@virtualnuggets com

Contact           :+1-707 666 8949(USA)                     

                        : +91-888 556 0202(India) 

 

IBM WODM Online Training @VirtualNuggets:

 

           …

Continue

Posted by Thirupathi Myadaveni on March 16, 2015 at 3:30am

Oracle Reports as PL/SQL Stored Procedures

Would anyone be interested in a solution & framework that can create Reports generated as Text or HTML output using PL/SQL as the only programming language?



This would be useful for those developers that have skills in PL/SQL and would like to leverage the use of basic & advanced SQL & PL/SQL to create Database Stored Reports and call them within any preferred front-end or back-end technology.



In case those who are familiar with RPT/RPF (Oracle's first…

Continue

Posted by LongBridge Corporation on March 10, 2015 at 2:36am

Data sync between 2 servers

Say i have 2 servers named server01 and server02.
server01 is my database server and server02 for a backup server.
Now how can I make a connection to save all data server01 to server02(only database data)
Note: both servers are connected to a LAN network.

Posted by Shakaouth Shanto on March 2, 2015 at 9:17am — 1 Comment

© 2015   Created by Steve Karam.

Badges  |  Report an Issue  |  Terms of Service