RBO secret #6

If two tables are joined, rule-based optimizer must select a driving table. Selected table can have a huge impact on performance. This is particularly so if the optimizer chooses to use nested loops. First row is returned coming from the control table and then the corresponding rows will be selected from the second table. An important issue is to ensure that the control table has been selected as the smallest number of rows.

In order to select the rule-based optimizer control table below uses the rules.

• Possession of a unique key index table or principal always gives its priority in the selection of the control table before the table not having such an index.
• The index for the access to all the columns is done by using the = operator takes precedence over the index, which selects only some of the columns, which affects the choice of the appropriate table for the query as a control table.
• A table that has a higher percentage of their columns in the index table takes precedence over a lower percentage of the columns in the index.
• two-column table containing the index, which is referenced in the WHERE clause query takes precedence over the table containing two single-column indexes.
• If two tables have the same number of columns in the index, the table control becomes the one listed in the FROM clause as the last. The following SQL command, control table emp table will be as listed in the FROM clause it last.

SELECT ....
FROM dept d, emp e
WHERE e.emp_name = 'Gurry'
AND d.dept_name = 'FINANCE'
AND d.dept_no = e.dept_no;