Fetching Component Tables: Example

See Also

Consider the following SQL statement. DEPARTMENT (100 records) and EMPLOYEE (1000 records) are component tables, ORDER (1000 records) and ORDERLINES (10,000 records) are database tables.

SELECT    *

FROM    ORDER o, EMPLOYEE e, ORDERLINES l, DEPARTMENT d

WHERE    o.emp_id = e.id

AND    d.id = e.dept.id

AND    l.order_id = o.id

AND    o.status = 'OPEN'

AND    e.name = 'Jones'

If DEPARTMENT and EMPLOYEE do NOT support rowset size hints, the tables are queried in the order as they appear in the FROM clause. ORDER is a database table, so first the database tables ORDER and ORDERLINES are queried within one SQL statement:

SELECT    *

FROM    ORDER o, ORDERLINES l

WHERE    l.order_id = o.id

AND    o.status = 'OPEN'

For each fetched record, the values are substituted in the remaining SQL statement, and the first component table in the list is queried. This might result in hundreds of queries:

SELECT    *

FROM    EMPLOYEE e

WHERE    :1 = e.id

AND    e.name = 'Jones'

(1)

Finally, for each retrieved employee, the DEPARTMENT table is queried:

SELECT    *

FROM    DEPARTMENT d

WHERE    d.id = :1

(6)

The ORDER and ORDERLINES database tables contain many records. So, from a performance point of view, this is not the best way to handle this SQL statement.

If the EMPLOYEE component table supports rowset size hints, then the expected rowset size would probably be very small (1), because of the condition:

e.name='Jones'

In this case, the EMPLOYEE component table is queried first:

SELECT    *

FROM    EMPLOYEE e

WHERE    e.name = 'Jones'

For these one or two fetched employee records, the values are substituted in the remaining SQL statement. If the DEPARTMENT component table also supports rowset size hints, the expected rowset size would again be 1 for each employee, so the DEPARTMENT table is queried next:

SELECT    *

FROM    DEPARTMENT d

WHERE    d.id = :1

(6)

Finally, the database tables are queried to retrieve the remaining information:

SELECT    *

FROM    ORDER o, ORDERLINES l

WHERE    o.emp_id = :1

AND    l.order_id = o.id

AND    o.status = 'OPEN'

(1)

This way of handling the SQL statement results in a better performance because now only three queries are issued.