When you have BI tool which generate reports based on nested views, many columns may get selected every level. Since each view is created with large number of columns, top level query may not use all columns from nested views. Informix server can create a temporary table for processing complex view. View with large number of columns has impact on processing materialized temporary table. Performance degradation will be higher with large number of materialized temporary tables at each nested level.
Informix server 12.10.xC1 release provide temporary table column optimization on views and derived tables. In this approach, Informix server decide to eliminate non-referenced columns dynamically at execution time. With this optimization, size of each materialized temporary table is reduced at each nested level. You will get performance improvement when views have large columns along with higher number of rows in base tables.
Original column map before Informix 12.10.xC1 release :
Query : select v1.vc1, v1.vc2 ,v1.vc3 from v1;
In this example, view v1 has 5 nested views ( v2 to v6), which refer to 6 base tables ( t1 to t6). Each view is materialized into a temp table. At top level v1 view need only 3 projected columns from its base tables. Columns shown in red color below in each temp table are non-referenced columns at every level.
Optimized column map in Informix 12.10.xC1 release :
As shown below, Informix server has eliminated non-referenced columns and retain those required at each query level. These required projected columns are shown in green color. Query performance depends on projected column size for materialized temporary tables. Informix server does not eliminate non-referenced view columns from system catalogs. These non-referenced columns are only removed from the view temp table during execution phase.
What happen when you use a derived table instead of a view ?
-- Informix server apply same column optimization rules for derived tables. In above case , If you use derived table , Informix server will build optimize column map.
Does Informix server eliminate non-reference columns when view ( or derived table) is folded into top level query ?
-- When temp table is not required, non-referenced columns are already eliminated by Informix server. This functionality available in all releases.
Create view view1 (vcol1, vcol2, vcol3) as select col1 , col2, col3 from tab1;
select view1.vcol1 , tab2.col1 from view1 , tab2 where tab2.col1 = view1.vcol1;
Query after view folding :
select tab1.col1 , tab2.col1 from tab1 , tab2 where tab2.col1 = tab1.col1;
In above case, all references of view1 is replaced by tab1. Since vcol2 & vcol3 columns are not selected in projection list, there is no need to perform column optimization.