Solved

Oracle 11g:  Materialized view creation

Posted on 2015-01-21
10
398 Views
Last Modified: 2015-01-22
Experts,

I am new to materialized views and how to go about creating them.  The reason for a materialized view is to speed up my table joins.  As for what type of materialized view, I need it to refresh every 15 minutes.  The data doesn't change very often, and the index field should be "NODE_HUB_NAME".  The current view below is called "VIEW_TKT_CHG_MGMT_CIS_MAPPING".

SELECT NODE_HUB_NAME,
            CAST(SUM(IMPACT_SCORE) AS NUMBER(10)) AS IMPACT_SCORE_HUB,
            CAST(COUNT(*)AS NUMBER(10)) TKT_CNT_HUB,
            CAST(SUM(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 7 THEN IMPACT_SCORE END)AS NUMBER(10)) PREV_CIS_7,
            CAST(COUNT(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 7 THEN TKT_ID END)AS NUMBER(10)) PREV_CNT_7,
            CAST(SUM(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 6 THEN IMPACT_SCORE END)AS NUMBER(10)) PREV_CIS_6,
            CAST(COUNT(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 6 THEN TKT_ID END)AS NUMBER(10)) PREV_CNT_6,
            CAST(SUM(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 5 THEN IMPACT_SCORE END)AS NUMBER(10)) PREV_CIS_5,
            CAST(COUNT(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 5 THEN TKT_ID END)AS NUMBER(10)) PREV_CNT_5,
            CAST(SUM(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 4 THEN IMPACT_SCORE END)AS NUMBER(10)) PREV_CIS_4,
            CAST(COUNT(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 4 THEN TKT_ID END)AS NUMBER(10)) PREV_CNT_4,
            CAST(SUM(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 3 THEN IMPACT_SCORE END)AS NUMBER(10)) PREV_CIS_3,
            CAST(COUNT(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 3 THEN TKT_ID END)AS NUMBER(10)) PREV_CNT_3,
            CAST(SUM(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 2 THEN IMPACT_SCORE END)AS NUMBER(10)) PREV_CIS_2,
            CAST(COUNT(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 2 THEN TKT_ID END)AS NUMBER(10)) PREV_CNT_2,
            CAST(SUM(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 1 THEN IMPACT_SCORE END)AS NUMBER(10)) PREV_CIS_1,
            CAST(COUNT(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 1 THEN TKT_ID END)AS NUMBER(10)) PREV_CNT_1,
            CAST(SUM(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 0 THEN IMPACT_SCORE END)AS NUMBER(10)) CUR_CIS,
            CAST(COUNT(CASE WHEN TRUNC(TKT_DATE_IMPACTED) = TRUNC(SYSDATE) - 0 THEN TKT_ID END)AS NUMBER(10)) CUR_CNT
     FROM VIEW_TKT_CHG_MGMT_MAPPING
 GROUP BY NODE_HUB_NAME

I need assistance on how to get this setup as a materialized view.  I apologize if I left out important data but not sure what else is needed.

Thank you!
0
Comment
Question by:Maliki Hassani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40562728
CREATE MATERIALIZED VIEW your_mv
    REFRESH COMPLETE NEXT SYSDATE + 15 / 1440
AS
      SELECT node_hub_name,
             CAST(SUM(impact_score) AS NUMBER(10)) AS impact_score_hub,
             CAST(COUNT(*) AS NUMBER(10)) tkt_cnt_hub,
             CAST(
                 SUM(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 7 THEN impact_score END) AS NUMBER(10)
             )
                 prev_cis_7,
             CAST(
                 COUNT(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 7 THEN tkt_id END) AS NUMBER(10)
             )
                 prev_cnt_7,
             CAST(
                 SUM(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 6 THEN impact_score END) AS NUMBER(10)
             )
                 prev_cis_6,
             CAST(
                 COUNT(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 6 THEN tkt_id END) AS NUMBER(10)
             )
                 prev_cnt_6,
             CAST(
                 SUM(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 5 THEN impact_score END) AS NUMBER(10)
             )
                 prev_cis_5,
             CAST(
                 COUNT(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 5 THEN tkt_id END) AS NUMBER(10)
             )
                 prev_cnt_5,
             CAST(
                 SUM(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 4 THEN impact_score END) AS NUMBER(10)
             )
                 prev_cis_4,
             CAST(
                 COUNT(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 4 THEN tkt_id END) AS NUMBER(10)
             )
                 prev_cnt_4,
             CAST(
                 SUM(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 3 THEN impact_score END) AS NUMBER(10)
             )
                 prev_cis_3,
             CAST(
                 COUNT(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 3 THEN tkt_id END) AS NUMBER(10)
             )
                 prev_cnt_3,
             CAST(
                 SUM(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 2 THEN impact_score END) AS NUMBER(10)
             )
                 prev_cis_2,
             CAST(
                 COUNT(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 2 THEN tkt_id END) AS NUMBER(10)
             )
                 prev_cnt_2,
             CAST(
                 SUM(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 1 THEN impact_score END) AS NUMBER(10)
             )
                 prev_cis_1,
             CAST(
                 COUNT(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 1 THEN tkt_id END) AS NUMBER(10)
             )
                 prev_cnt_1,
             CAST(
                 SUM(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 0 THEN impact_score END) AS NUMBER(10)
             )
                 cur_cis,
             CAST(
                 COUNT(CASE WHEN TRUNC(tkt_date_impacted) = TRUNC(SYSDATE) - 0 THEN tkt_id END) AS NUMBER(10)
             )
                 cur_cnt
        FROM view_tkt_chg_mgmt_mapping
    GROUP BY node_hub_name
0
 

Author Comment

by:Maliki Hassani
ID: 40562849
Thank you!, so I created the mv and I run the view "VIEW_TKT_CHG_MGMT_CIS_MAPPING" and it still takes the same amount of time run, any ideas?  It is taking 30 seconds from 15... hmm
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40562879
what if you query the mv instead of the view?


creating an mv makes no changes to the view at all, querying the view shouldn't have changed in performance.
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:Maliki Hassani
ID: 40562883
ha ha .. that is a good one..  Dope!  Thanks! 457ms!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40562885
if you are trying to implement query rewrite, that's a different story.

if so, you need to enable that

ALTER MATERIALIZED VIEW your_mv ENABLE QUERY REWRITE;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40562896
and QUERY_REWRITE_ENABLED  must be set to TRUE or FORCE
0
 

Author Closing Comment

by:Maliki Hassani
ID: 40562967
Thank you!
0
 

Author Comment

by:Maliki Hassani
ID: 40564354
sdstuber,  

I have a question for you about this materialized view.  I had a dba for my company contact me that the MV was adding a heavy load to the server and I should drop it.  The query itself only takes 15 seconds to run.  So not sure what is happening?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40564391
The MV will add a load to the server while it's refreshing; but it should be reducing the load of every query that would have hit the normal view.

Measure the loads of queries with and without the mv and compare.  Also take into account how often you will query the data.  

If you query the data once per day, then the MV load is unreasonable because you'll do the work 96 times in a day.
If you query the data several hundred times a day then the MV load should be helpful even if it's a big spike when it runs.

In any case, measure it, and talk with your business unit.  If they system really can't handle the load, but the extra speed is of enough value to the business then that becomes a point in which some hardware investments can be made.

Also, it doesn't hurt to look at the query underlying your base view.  Can you make some optimizations within it to help the mv query? If not, can you take the query out of the view and make some optimizations that way?
0
 

Author Comment

by:Maliki Hassani
ID: 40564404
great point..  Will do!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question