Home » RDBMS Server » Performance Tuning » materialize view (11g, solaris9)
| materialize view [message #448634] |
Wed, 24 March 2010 07:29  |
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
|
Member |
|
|
Can anybody clear my dillema regarding the following:-
Let's say I have created a materialize view with the query below
Select c1,c2,c3,c4,sum(c1),sum(c2),sum(c3),sum(c4)
from t1
group by c1,c2,c3,c4
Now say another query is
Select c1,c4,sum(c1),sum(c3),sum(c4)
from t1
group by c1,c4
Will this 2nd query use the above materialze view.
Thanks in advance
|
|
|
|
| Re: materialize view [message #448649 is a reply to message #448634] |
Wed, 24 March 2010 08:35  |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You appear to be summing the same columns as you are grouping by, are you sure that's right?
If you want to know if it uses the materialized view just trace the session and check for yourself.
|
|
|
|
|
|
| Re: materialize view [message #448733 is a reply to message #448649] |
Thu, 25 March 2010 01:25 |
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
|
Member |
|
|
Sorry the sum() are on different columns. Modified:-
This should be the main query for the MV
select c1,c2,c3,c4,sum(c5),sum(c6),sum(c7),sum(c8)
from t1
group by ci,c2,c3,c4
|
|
|
|
| Re: materialize view [message #448737 is a reply to message #448733] |
Thu, 25 March 2010 01:36 |
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
|
Member |
|
|
select c1,c2,c3,c4,sum(c5),sum(c6),sum(c7),sum(c8)
from t1
group by ci,c2,c3,c4
Actually the table t1 is having 47Million rows. Execution time of the above query is 6to9 minutes.But when the total rows are fetched it is taking more than 45 minutes. As this multitable insert is done based on this type of query how can I reduce the fetching time so that the multitable inserts take less time.
Any sort of guidance is welcome.
Thanks
|
|
|
|
|
|
| Re: materialize view [message #448899 is a reply to message #448737] |
Thu, 25 March 2010 11:10 |
sblindgren
Messages: 4
Registered: June 2007
Location: Salt Lake City, Utah
|
Junior Member |
|
|
We also have some very large tables at the size of 5 and 6 million records. You want to look at the Index's you have on your base tables. Your Materialize View will inherit the Index's from the Base Tables and depending on the way you are displaying the data, it can cause Oracle to read 1 record in this table then 100K records in another table if the index's are not correct.
As and example, we had a report that took several hours to produce. Adding some index's and if need be, put hint's on the select statement, it now runs in minutes.
Regards, Scott
|
|
|
|
|
|
| Re: materialize view [message #448963 is a reply to message #448634] |
Fri, 26 March 2010 01:03 |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
Go for MVIEW option.
Your query:
select c1,c2,c3,c4,sum(c5),sum(c6),sum(c7),sum(c8)
from t1
group by ci,c2,c3,c4
can be easily converted to an mview provided you follow all the guidelines mention in above link i provided.
[Updated on: Fri, 26 March 2010 01:05] Report message to a moderator |
|
|
|
|
|
| Re: materialize view [message #448966 is a reply to message #448634] |
Fri, 26 March 2010 01:14 |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
of course yes!!
Materialized views store the results of the query, so they will store all the processed data for your query.
You can schedule manual or automatic refresh so that next time your tables changes,those changes should be propagated to the mview.
|
|
|
|
| Re: materialize view [message #448980 is a reply to message #448966] |
Fri, 26 March 2010 01:50 |
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
|
Member |
|
|
Will this same mview be used by other queries and fetch the set of data faster way for those as
select /*+ parallel (agg,5) */ c3,c4,sum(c5),sum(c6),sum(c7),sum(c8)
from t1
group by c3,c4
or
SELECT /*+ parallel (agg,5) */ * from t1;
or
INSERT /*+ append parallel (t1,5) */ALL
WHEN 1 = 1
THEN
INTO tab1
values(c3,c4,a1,a2,a3,a4)
select /*+ parallel (agg,5) */ c3,c4,sum(c5)a1,sum(c6)a2,sum(c7)a3,sum(c8) a4
from t1
group by c3,c4
|
|
|
|
|
|
| Re: materialize view [message #449225 is a reply to message #448634] |
Sun, 28 March 2010 06:29 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
If you want to know about parallel query and query rewrite using materialized views, you will need to do some reading like the rest of us do.
To summarize:
Quote:1) your materialized view must be constructed with all the necessary pieces before it can be used in a query rewrite.
2) your instance must have certain init.ora parameters set correctly in order for query rewrite to occurr.
3) your query must be compatible with the materialized view for query rewrite to occurr.
In general however, if your query is a subset of the mview query, it is possible to get oracle to rewrite it to use mviews.
You know your query is a subset of the mview query, if you can rewrite it yourself to use the mview with out adding or subtracting columns.
Thus if you can do this (eg. it is syntactically correct and thus executes):
with
my_mview_query as (
select c1,c2,c3,c4,sum(c5),sum(c6),sum(c7),sum(c8)
from t1
group by ci,c2,c3,c4
)
select /*+ parallel (agg,5) */ c3,c4,sum(c5),sum(c6),sum(c7),sum(c8)
from my_mview_query
group by c3,c4
/
Then query rewrite is possible if you add any additional items needed to the my_mview_query when you create your mview.
Seems to me that given the right MVIEW definition, query rewrite should occurr for the second query.
Kevin
|
|
|
|
Goto Forum:
Current Time: Sun May 12 17:47:53 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
