Derived tables folded into parent queries

You can improve the performance of collection-derived tables by using SQL to fold derived tables in simple queries into a parent query instead of into query results that are put into a temporary table.

Use SQL like that in this example:

select * from ((select col1, col2 from tab1)) as vtab(c1,c2)

However, if the query is complex because it involves aggregates, ORDER BY operations, or the UNION operation, the server creates a temporary table.

The database server folds derived tables in a manner that is similar to the way the server folds views through the IFX_FOLDVIEW configuration parameter (described in Enable view folding to improve query performance). When the IFX_FOLDVIEW configuration parameter is enabled, views are folded into a parent query. The views are not folded into query results that are put into a temporary table.

The following examples show derived tables folded into the main query.

Figure 1. Query plan that uses a derived table folded into the parent query

select * from ((select vcol0, tab1.col1 from
        table(multiset(select col2 from tab2 where col2 > 50 )) 
	vtab2(vcol0),tab1 )) vtab1(vcol1,vcol2) 
	where vcol1 = vcol2

Estimated Cost: 2
Estimated # of Rows Returned: 1

  1) informix.tab2: SEQUENTIAL SCAN

        Filters: informix.tab2.col2 > 50 

  2) informix.tab1: SEQUENTIAL SCAN

        Filters: 
        Table Scan Filters: informix.tab1.col1 > 50 

DYNAMIC HASH JOIN 
    Dynamic Hash Filters: informix.tab2.col2 = informix.tab1.col1

Figure 2. Second query plan that uses a derived table folded into the parent query

select * from (select col1 from tab1 where col1 = 100) as vtab1(c1)
left join (select col1 from tab2 where col1 = 10) as vtab2(vc1) 
on  vtab1.c1 =  vtab2.vc1

Estimated Cost: 5
Estimated # of Rows Returned: 1

      1) informix.tab1: SEQUENTIAL SCAN

            Filters: informix.tab1.col1 = 100 

      2) informix.tab2: AUTOINDEX PATH

        (1) Index Keys: col1   (Key-Only)
            Lower Index Filter: informix.tab1.col1 = informix.tab2.col1 
            Index Key Filters:  (informix.tab2.col1 = 10 )

    ON-Filters:(informix.tab1.col1 = informix.tab2.col1 
            AND informix.tab2.col1 = 10 ) 
    NESTED LOOP JOIN(LEFT OUTER JOIN)

The following example shows a complex query involving the UNION operation. Here, a temporary table has been created.

Figure 3. Complex derived-table query that creates a temporary table

select * from (select col1 from tab1 union select col2 from tab2 )
as vtab(vcol1)  where vcol1 < 50

Estimated Cost: 4
Estimated # of Rows Returned: 1

  1) (Temp Table For Collection Subquery): SEQUENTIAL SCAN