Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Materialized views Vs Aggregate Tables 2
- Thread starter mkabre74
- Start date
-
1
- #2
Materialised views are essentially a simple SQL version of an aggregate table. For relatively simple straight aggregate tables you can save time & effort with materialised views.
If you table requires more complex criteria resulting in a SQL statement involving mulitple sub-queries (which will impact load time) or PL/SQL functions (whichwill impact development time) it may be better to use your ETL tool to perform the load. THe choice is a pragmatic one and largely depends how comfortable you are developing straight into Oracle and how good/fast your ETL tool is.
If you table requires more complex criteria resulting in a SQL statement involving mulitple sub-queries (which will impact load time) or PL/SQL functions (whichwill impact development time) it may be better to use your ETL tool to perform the load. THe choice is a pragmatic one and largely depends how comfortable you are developing straight into Oracle and how good/fast your ETL tool is.
SarangPatil123
Technical User
Well MV's are far more than simple SQl version of an aggregate table. Not sure why there is referance to ETL in the previous post. MV are use more for performce improvement. I will try to explain some of the features of MV. But I strongly would recomend u read a Oracle 9i documentation Part Number A96520-01 Chapter 8 for more details.
MV are used primarily to increase the Query performance. Then should not affect the ETL load or ETL methodology in any case.
1. MV helps query rewrite..In shout if u have a MV defined as "select * from sales group by region_id" and u have a query selct * from sales group by region_id fired on the oracle db. Oracle will automatically re-write a query and refer it to MV instade of Sales table. Now in DW environment this is a big performance improvement. There are some paramters which needs to be set for this to happen.
2. MV can undergo fast referesh. In short if i have 10 Mill rows in the Fact table and i add 500 rows. Then b making use of MVLOGS oracle will do a fast refresh on the MView. with extra 500 rows only.
Using MV i was able to cut the query time from 2 Hours to copule of minutes at some of my clients.
There are some more advance feature od MV like PCT ( Partation Change tracking ) etc.
If u have OEM avaliable try and use summary advisior. SA will give u the list of MV's u can use to improve the performance.
Hope this helps.
MV are used primarily to increase the Query performance. Then should not affect the ETL load or ETL methodology in any case.
1. MV helps query rewrite..In shout if u have a MV defined as "select * from sales group by region_id" and u have a query selct * from sales group by region_id fired on the oracle db. Oracle will automatically re-write a query and refer it to MV instade of Sales table. Now in DW environment this is a big performance improvement. There are some paramters which needs to be set for this to happen.
2. MV can undergo fast referesh. In short if i have 10 Mill rows in the Fact table and i add 500 rows. Then b making use of MVLOGS oracle will do a fast refresh on the MView. with extra 500 rows only.
Using MV i was able to cut the query time from 2 Hours to copule of minutes at some of my clients.
There are some more advance feature od MV like PCT ( Partation Change tracking ) etc.
If u have OEM avaliable try and use summary advisior. SA will give u the list of MV's u can use to improve the performance.
Hope this helps.
-
1
- #4
A materialized view may be an aggregate table, or a simple, non-aggregate table (formerly known as a snapshot), or more complex multi-table join. You can have Oracle create materialized views, or register existing aggregate tables (perhaps created by an ETL tool) with Oracle as a materialized view so it can invoke query-rewrite. I'ven't had consistent success with query-rewrite while experimenting with it in my dev environment, but when it works, it flies!
-- Duke Ganote
-- Duke Ganote
- Status