Table or View

[Former Member]

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.

[markmumy]

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

[Former Member]

Hi Mark,

We are on IQ 15 and currently have no plans of moving to IQ 16.

We need a solution until then.

Thanks. How would the view/table get populated every so often whenever there is an update in base table.

[markmumy]

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

[Former Member]

Thanks Mark for your reply.

I was thinking of creating a summary table and get the data loaded into this table from other existing tables.

Do you see any performance impacts if 1 summary table is created/loaded from multiple other tables?