Query plans for collection-derived tables

A collection-derived table is a special method that the database server uses to process a query on a collection. To use a collection-derived table, a query must contain the TABLE keyword in the FROM clause of an SQL statement.

For more information about how to use collection-derived tables in an SQL statement, see the HCL OneDB™ Guide to SQL: Syntax.

Although the database does not actually create a table for the collection, it processes the data as if it were a table. Collection-derived tables allow developers to use fewer cursors and host variables to access a collection, in some cases.

These SQL statements create a collection column called children: 
CREATE ROW TYPE person(name CHAR(255), id INT);
CREATE TABLE parents(name CHAR(255),
id INT,
children LIST(person NOT NULL));
The following query creates a collection-derived table for the children column and treats the elements of this collection as rows in a table: 
SELECT name, id 
FROM TABLE(MUTLISET(SELECT children 
FROM parents 
WHERE parents.id
= 1001)) c_table(name, id);
Alternatively, you can specify a collection-derived table in the FROM clause, as shown in this example: 
SELECT name, id 
FROM (SELECT children 
FROM parents 
WHERE parents.id
= 1001) c_table(name, id);