Directs the optimizer to choose the merge join access plan in a SELECT query for all tables listed in the FROM clause. The MERGE JOIN option is used when two tables are approximately equal in size and the data is distributed equally. It is faster than a LOOP JOIN when an equal amount of rows are joined. For joining data, MERGE JOIN supports a maximum of three tables. The joining table is ordered by joining columns and combining the results of the columns.
You can use this hint when the data is sorted by a join key and the nested loop join performance is not adequate. The optimizer selects the merge join only where there is an equal predicate between tables (for example, “table1.col1 = table2.col1”). Otherwise, the optimizer selects LOOP JOIN even if the MERGE JOIN hint is specified.
When data is not sorted before performing the merge operation, the solidDB® query executor sorts the data.
Keep in mind that the merge join with a sort is more resource intensive than the merge join without the sort.
TRIPLE MERGE JOIN
TRIPLE MERGE JOIN is a variant of MERGE JOIN. It has three table sources which are merged on equal basis instead of the two in MERGE JOIN. The TRIPLE MERGE JOIN hint instructs the SQL interpreter to use the triple merge join algorithm whenever possible. The triple merge join algorithm can only be used in situations where in all three table sources there is one single field that should be equal in all the resulting rows after evaluating the WHERE condition.
LOOP JOIN
Directs the Optimizer to pick the nested loop join in a select query for all tables listed in the FROM clause. By default, the Optimizer does not pick the nested loop join.
The LOOP JOIN loops through both inner and outer tables to find matches between columns in the inner and outer tables. For better performance, the joining columns should be indexed.
Using the loop join when tables are small and fit in memory may offer greater efficiency than using other join algorithms.
JOIN ORDER FIXED
Specifies that the optimizer use tables in a join in the order listed in the FROM clause of the query. This means that the optimizer does not attempt to rearrange the join order and does not try to find alternate access paths to complete the join.
Tip: You should test the hint by running the EXPLAIN PLAN output to ensure that the generated plan is optimal for the given query.
INTERNAL SORT
Specifies that the query executor use the internal sorter. Use this hint if the expected resultset is small (hundreds of rows as opposed to thousands of rows); for example, if you are performing some aggregates, ORDER BY with small resultsets, or GROUP BY with small resultsets, and so on.
This hint avoids the use of the more expensive external sorter.
EXTERNAL SORT
Specifies that the query executor use the external sorter. Use this hint when the expected resultset is large and does not fit in memory; for example, if the expected result set has thousands of rows.
INDEX [REVERSE] table_name.index_name
Forces a given index scan for a given table. In this case, the optimizer does not proceed to evaluate if there are any other indexes that can be used to build the access plan or whether a table scan is better for the given query. Tip: You should test the hint by running the EXPLAIN PLAN output to ensure that the generated plan is optimal for the given query.
The optional keyword REVERSE returns the rows in the reverse order. In this case, the query executor begins with the last page of the index and starts returning the rows in the descending (reverse) key order of the index.
In tablename.indexname, the tablename is a fully qualified table name which can include the catalogname and schemaname.
PRIMARY KEY [REVERSE] table_name
Forces a primary key scan for a given table.
The optional keyword REVERSE returns the rows in the reverse order.
If the primary key is not available for the given table, you will receive a runtime error.
FULL SCAN table_name
Forces a table scan for a given table. In this case, the optimizer does not proceed to evaluate if there are any other indexes that can be used to build the access plan or whether a table scan is better for the given query. Tip: You should test the hint by running the EXPLAIN PLAN output to ensure that the generated plan is optimal for the given query.
[NO] SORT BEFORE GROUP BY
Indicates whether the SORT operation occurs before the resultset is grouped by the GROUP BY columns.
If the grouped items are few (hundreds of rows), use NO SORT BEFORE. On the other hand, if the grouped items are large (thousands of rows), use SORT BEFORE.
UNION FOR OR
The UNION FOR OR hint instructs the SQL interpreter to replace an OR condition of style A = 1 OR A = 2 with a construction of the following type:
SELECT ... WHERE A = 1 UNION ALL SELECT ... WHERE A = 2
In most cases the SQL interpreter performs the replacement automatically; the UNION FOR OR hint ensures the UNION-type execution is used always.
Note Conditions of type A = 1 OR B = 2 can also be handled, but this may be problematic since the conditions are not mutually exclusive. Because of this, the construction for A = 1 OR B = 2 is the following:
SELECT ... WHERE A = 1 UNION ALL SELECT ... WHERE B = 2 AND UtoT NOT (A = 1)
where UtoT stands for UNKNOWN TO TRUE.
The UtoT operator is needed for handling cases with NULL values. Without the UtoT operator, a row which has values A = NULL and B = 2 would not appear correctly in the UNION variant.
OR FOR OR
The OR FOR OR hint is the opposite for UNION FOR OR. It prevents the interpreter from using the UNION-type solution.
LOOP FOR OR
The LOOP FOR OR hint is an alternative query execution plan that falls between UNION FOR OR and OR FOR OR. With LOOP FOR OR the OR values are passed individually to the data table level, but conditions like A = 1 OR B = 2 cannot be handled (see description of UNION FOR OR for details on A = 1 OR B = 2).