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