Home » RDBMS Server » Performance Tuning » materialize view (11g, solaris9)
materialize view [message #448634] Wed, 24 March 2010 07:29 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 #448725 is a reply to message #448634] Thu, 25 March 2010 00:56 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
It will use it provided you fulfil all the requirements for query rewrite.

analyze your MView with dbms_mview.explain_rewrite procedure
here
Re: materialize view [message #448733 is a reply to message #448649] Thu, 25 March 2010 01:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #448812 is a reply to message #448634] Thu, 25 March 2010 05:16 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post the full code of the insert procedure along with the explain plan.
Re: materialize view [message #448899 is a reply to message #448737] Thu, 25 March 2010 11:10 Go to previous messageGo to next message
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 #448958 is a reply to message #448899] Fri, 26 March 2010 00:27 Go to previous messageGo to next message
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
Member
It is just one table, so no joins are used and no predicate used. In such case how to fetch records from the 47M rows table.
Re: materialize view [message #448963 is a reply to message #448634] Fri, 26 March 2010 01:03 Go to previous messageGo to next message
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 #448964 is a reply to message #448963] Fri, 26 March 2010 01:06 Go to previous messageGo to next message
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
Member
so you believe creating a MVIEW option will fetch the 47M rows faster
Re: materialize view [message #448966 is a reply to message #448634] Fri, 26 March 2010 01:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #449025 is a reply to message #448634] Fri, 26 March 2010 05:22 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
For query rewrite options,Read this entire document
Re: materialize view [message #449225 is a reply to message #448634] Sun, 28 March 2010 06:29 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: sql tuning
Next Topic: Query fetching time before hand
Goto Forum:
  


Current Time: Fri Dec 09 03:38:07 CST 2016

Total time taken to generate the page: 0.09943 seconds