Example showing how the database server completes the query

HCL OneDB™ 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:
  1. 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 Query plan that uses a collection-derived table shows.

  2. Reads the value of the collection column called children.
  3. 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 Query plan that uses a collection-derived table 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