on 07-20-2015 4:14 PM
Hi Experts,
I want to create an aggregated or summary level at database level.
As my raw data is huge and my raw tables are partitioned into different tables to store such huge amount of data.
I want to create several summary table/view which can aggregate data at different levels.
What is the best option to do this -
1. Creation of permanent table ('select into' or 'create table')
2. Creation of simple view with 'union all' and group by.
3. Creation of Materialized view (never used this before so not sure of its impacts)
Also I want this table/view to be updated every so often. How can we achieve this?
Appreciate any help on this. Thanks a lot.
IQ does not support materialized views. They are in the docs, but only apply to catalog tables.
Personally, I would try the view approach first since that is the easiest. If it performs to your satisfaction, there is no need to create a permanent table. Which version of IQ are you on? We made some significant improvements to VIEW and UNION ALL processing in IQ 16 SP10 that may help you out.
Mark
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
If you use the view approach, there is not updating to be done. A view will always use the newest data in the base tables.
If you choose to use a permanent table that gets populated when data is loaded, you will have to determine the best way of updating the data based on your data model and load patterns. In some cases, the summary tables are rebuilt from scratch. In others you may be able to scan the new records and update the summary table. The point is that it will really depend on the type of summary being done, the load pattern, etc. This is one of the main reasons why I favor a view approach as it requires no data movement.
Mark
User | Count |
---|---|
91 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.