Maliki Hassani
asked on
Oracle 11g: Materialized view creation
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_MAP PING".
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!
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_MAP
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
creating an mv makes no changes to the view at all, querying the view shouldn't have changed in performance.
ASKER
ha ha .. that is a good one.. Dope! Thanks! 457ms!
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;
if so, you need to enable that
ALTER MATERIALIZED VIEW your_mv ENABLE QUERY REWRITE;
and QUERY_REWRITE_ENABLED must be set to TRUE or FORCE
ASKER
Thank you!
ASKER
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?
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?
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?
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?
ASKER
great point.. Will do!
ASKER