Optimizer Hints

This topic describes the optimizer hints supported by Relyt and details each of them.

Overview

Relyt provides a powerful query optimizer to determine the most efficient way for executing each SQL command. However, there is still a probability that the query plan chosen by the optimizer is not the most efficient for a specific query. In such cases, optimizer hints can be used to guide the optimizer to perform specific operations.

An optimizer hint is a special directive that can be embedded into SQL commands to instruct the query optimizer on how to execute a query. The following table provides the general information about the optimizer hints offered by Relyt.

Name	Description	Applicable scope
SET	Sets the session value for a system variable temporarily.	Query
JOIN_ORDER	Specifies the join order in a query block.	Query block
REPLICATED, PARTITIONED	Sets the distirbution type for a join.	Query block

As a type of special comments, an optimizer hint must be specified within /*+ ... */. Whitespaces are permitted. For example:

/*+SET (<var_name>, '<value>')[,...]*/

SET hint

A SET hint specifies the session value for a system variable temporarily for a query.

Syntax

The syntax of a SET hint is:

SET (<var_name>, '<value>')

In this syntax,

• <var_name>: specifies the name of the system variable.

• <value>: specifies the new session value for the system variable.

One SET hint can be used to set only one per-session system variable. If you want to set multiple, specify muiltple SET hints in one hint block (that is, one /*+ ... */ comment) in a comma-separated list. Following are two examples:

/*+SET(statistics_cpu_timer_enabled,'true'), SET(query_max_memory, '1GB' )*/
SELECT * FROM sales

Usage notes

Currently, the SET hint must be placed at the very beginning of a query.

If a hint block contains duplicate SET hints, only the last one will take effect.

If a hint block contains conflict SET hints, only the last one will take effect.

Up to one SET hint block can be enforced for a query. If you specify multiple hint blocks in one query, only the first hint block will take effect. For example:

/*+SET(statistics_cpu_timer_enabled,'true')*/
/*+SET(task_concurrency, '1')*/
SELECT * FROM sales

In this example, only SET(statistics_cpu_timer_enabled,'true') will take effect. SET (task_concurrency, '1' ) will be ignored.

Examples

Set session variable preferred_tablescan_read_batch_rows to 40480:

/*+SET(preferred_tablescan_read_batch_rows,'40480')*/
SELECT 123.456E7 from DUAL

JOIN_ORDER hint

A JOIN_ORDER hint affects the order in which the Relyt optimizer joins tables,its scope is a query block.

Syntax

JOIN_ORDER (<left_t>, <right_t>);

• left_t: the table or the list tables on the left of the join.

• right_t: the table or the list of tables on the right of the join.

If left_t or right_t is a list of tables, you must enclose them using parentheses. If the list contains more than two tables, you must use multiple levels of parentheses so that only two elements (a table or the content in a pair of parentheses is regarded as an element) exist in each pair of parentheses. Tables or elements in the innermost parentheses will be joined first.

For example:

JOIN_ORDER (a, (b, c))

In this example, <left_t> is a and <right_t> is (b, c). This hint specifies that a is joined with the join result of b and c.

One more example:

JOIN_ORDER ((a, (b, c)),(d, e))

In this example, <left_t> is (a, (b, c)) and <right_t> is (d, e). This hint specifies that a is joined with the join result of b and c on the left while d is joined with e on the right, and then then left result is joined with the right result.

However, JOIN_ORDER (a, (b, c), d) is invalid, because it contains three elements.

Usage notes

A JOIN_ORDER hint will be ignored if any of the following conditions exist:

• The name or alias of any specified table does not exist.

• It contains duplicate table names or aliases.

• It does not include all tables specified in the query block.

  Suppose the current join order for a query block is a INNER JOIN b INNER JOIN c. If you set /*+ JOIN_ORDER (a, b) */ for the query block, the hint will be ignored. A correct JOIN_ORDER hint in this example is: /*+ JOIN_ORDER (c, (a, b)) */.

If a table has an alias, the JOIN_ORDER hint must refer to the alias.

Only one JOIN_ORDER hint can be enforced for each query block at a time. If you specify multiple JOIN_ORDER hints in a query block, only the first JOIN_ORDER will be in effect.

Examples

Use a JOIN_ORDER hint to join tables orders and customer first and then with table lineitem:

SELECT 
/*+JOIN_ORDER(lineitem,(orders,customer))*/
  l_orderkey, 
  sum(
    l_extendedprice * (1 - l_discount)
  ) AS revenue, 
  o_orderdate, 
  o_shippriority 
FROM 
  customer, 
  orders, 
  lineitem 
WHERE 
  c_mktsegment = 'BUILDING' 
  AND c_custkey = o_custkey 
  AND l_orderkey = o_orderkey 
  AND o_orderdate < date '1995-03-15' 
  AND l_shipdate > date '1995-03-15' 
GROUP BY 
  l_orderkey, 
  o_orderdate, 
  o_shippriority 
ORDER BY 
  revenue DESC, 
  o_orderdate 
LIMIT 
  10;

Join distribution type hints

Relyt provides two hints for configuring distribution types for joins: REPLICATED and PARTITIONED. The REPLICATED hint sets the distribution type for a join to REPLICATED. The PARTITIONED hint sets the distribution type for a join to PARTITIONED.

Syntax

/*+ [REPLICATED | PARTITIONED] (<join_order>)[, ... ] */

In this syntax,

• REPLICATED: sets the distribution type for the specified join to REPLICATED.

• PARTITIONED: sets the distribution type for the specified join to PARTITIONED.

• <join_order>: specifies the order of the join to which the distribution type applies.

  For details about usage notes of the join order, see Usage notes described in section JOIN_ORDER.

In a hint block, you can specify multiple distribution type hints. For example:

/*+ REPLICATED (a, (b, c)), PARTITIONED (b, c) */

Usage notes

If two hints specified in a hint block conflict with each other, only the first hint takes effect. For example:

/*+ REPLICATED (a, b), PARTITIONED (a, b) */

In this example, only the first hint REPLICATED (a, b) will take effect.

In some cases, the distribution type for a join is deterministic and cannot be modified through such hint. For example, the distribution type for a CROSS JOIN b can only be REPLICATED. In this example, setting the PARTITIONED (a, b) hint has no effect.

If the specified join order does not exist, the hint is invalid.

Examples

Set the distribution type for orders JOIN customer to PARTITIONED and that for lineitem JOIN (orders JOIN customer) to REPLICATED:

SELECT
/*+REPLICATED(l,(o,c)), PARTITIONED(o,c)*/
    l.orderkey,
    sum(l.extendedprice * (1 - l.discount)) AS revenue,
    o.orderdate,
    o.shippriority
FROM customer AS c,
     orders AS o,
     lineitem AS l
WHERE c.mktsegment = 'BUILDING'
  AND c.custkey = o.custkey
  AND l.orderkey = o.orderkey
  AND o.orderdate < DATE '1995-03-15'
  AND l.shipdate > DATE '1995-03-15'
GROUP BY l.orderkey,
         o.orderdate,
         o.shippriority
ORDER BY revenue DESC,
         o.orderdate LIMIT 10
;