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

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

Join cases between related tables in data computing and analytics

Related computing can be: Inner join or outer join. The outer join can be further divided into left join, right join, and full join. With esProc, such kinds ofrelated computing can be easily implemented. In the discussion below, we will introduce the join operations with some examples using the table emp and table sOrder.

Table structure:

The table emp stores the employee data table, in which the…

Continue

Posted by Jim King on August 25, 2014 at 1:35am

Oracle RAC Classes

Contact me for Oracle High Availability Training / Online Classes - Dubai shans.dba@gmail.com

Posted by Shanmugam on August 18, 2014 at 1:00am

Code Examples of In-Memory Grouping for more efficient data computing and analytics

It is convenient to realize some common in-memory grouping with esProc, such as, equal grouping, alignment grouping and enumeration grouping. They are to be illustrated with the following examples.

Equal grouping 

Grouping basis of equal grouping is certain fields (or computed columns derived from fields) within a data set. Each group is a subset of original data set.

Case description: Group sales orders by the year. …

Continue

Posted by Jim King on August 15, 2014 at 12:21am

© 2014   Created by Steve Karam.

Badges  |  Report an Issue  |  Terms of Service