Fetching Component Tables: Example
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.
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:
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:
Finally, for each retrieved employee, the DEPARTMENT table is queried:
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:
In this case, the EMPLOYEE component table is queried first:
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:
Finally, the database tables are queried to retrieve the remaining information:
This way of handling the SQL statement results in a better performance because now only three queries are issued.