Example showing how the database server completes the query

Informix® performs several steps when completing a query for collection-derived tables.

When completing a query, the database server performs the steps shown in this example:

Scans the parent table to find the row where parents.id = 1001
This operation is listed as a SEQUENTIAL SCAN in the SET EXPLAIN output that Figure 1 shows.
Reads the value of the collection column called children.
Scans the single collection and returns the value of name and id to the application.
This operation is listed as a COLLECTION SCAN in the SET EXPLAIN output that Figure 1 shows.

Figure 1. Query plan that uses a collection-derived table

QUERY:
------
SELECT name, id 
FROM (SELECT children 
FROM parents 
WHERE parents.id
= 1001) c_table(name, id);

Estimated Cost: 2
Estimated # of Rows Returned: 1

  1) lsuto.c_table: COLLECTION SCAN
    Subquery:
    ---------
    Estimated Cost: 1
    Estimated # of Rows Returned: 1

      1) lsuto.parents: SEQUENTIAL SCAN

            Filters: lsuto.parents.id = 1001