This section provides an explanation of the D3 SQL Query Optimizer and tips for optimizing your queries.
SQL queries can become long and complex when accessing multiple tables because, for each table encountered, the optimizer generates a query plan for referencing that table. When accessing multiple tables, there are often many possible combinations of query plans available. The optimizer selects the most efficient combination and order based on the expected cost of each plan. The optimizer considers the factors listed below:
| Factor | Optimization Tip |
|---|---|
| If the table is nested within a parent table and the primary keys of the two tables are equal, the optimizer instructs the runtime to extract the nested data from the parent data record. This is the most efficient way to reference a table. | Use nested tables to represent related data. |
| If the table is not a nested child, but its primary key can be resolved to a constant or a column generated by another table, a direct read of the row can be performed. | Tie related tables together using primary keys whenever possible. |
| If the table does not satisfy either of the above criteria,
but there is an equality clause where an indexed column is compared
to a constant or a secondary key column generated by another table,
the index is referenced at runtime to get the list of rows to be processed. If multiple indexes can be resolved, those indexes are intersected before retrieving row data. |
Ensure that all columns have associated indexes if selection is based on equality to those columns. |
| If the query includes a complex WHERE clause, where more than one indexed column is compared to a constant or a secondary key column generated by another table, the optimizer processes the expressions in the WHERE clause left to right. | If more than one index can be used on a table in a given query, put the comparison with the column that provides the least amount of key matches first in the WHERE clause. |
| If no direct match to an index is found, the optimizer tries to locate an index on a secondary key pointing to a primary key of another table where an index on the secondary table can be compared to a constant. This is slightly slower than using a direct index, but is much faster than sequential scanning. If multiple indexes are found meeting these criteria, the intersection between those indexes is used. This allows very efficient processing of so called star joins used for OLAP applications. | Create indexes on all columns that point to primary keys in other tables. |
If none of the above optimizations can be performed, the optimizer instructs the runtime to sequentially scan every row in the table.
Index administration can be performed with the SQL statements, CREATE INDEx and DROP INDEX.
For D3 releases prior to 7.1, use the TCL commands, CREATE-INDEX and DELETE-INDEX. See the D3 Reference Manual for more information on these TCL commands.
The D3 SQL query optimizer recognizes and uses standard D3 indexes on all releases. The optimizer uses the index if the index’s a correlative matches the correlative of a column used in the query. If there is no SQL correlative for a particular column, the optimizer searches for an index of the form a followed by the column number.