This topic describes the OPT_PARAM hint of PolarDB-O.

You can specify the values of specific parameters at the database level, session level, or user level. This method is traditional. When you execute a specific SQL statement, you cannot control the values of specific parameters for the statement. In this case, you can use the OPT_PARAM hint to specify the values of specific parameters for a SQL statement. The OPT_PARAM hint is valid only in the SQL statement.

The OPT_PARAM hint allows you to specify the values of the following three parameters:

  • enable_hashjoin
  • enable_mergejoin
  • enable_bitmapscan

Syntax

SELECT /*+ opt_param('enable_hashjoin', 'off'),
opt_param('enable_mergejoin', 'off') */ ...;

Examples

EXPLAIN (COSTS OFF) SELECT * FROM sampletable WHERE x < 423;
EXPLAIN (COSTS OFF) SELECT /*+ opt_param('enable_bitmapscan', 'off') */ * FROM sampletable WHERE x < 423;
EXPLAIN (COSTS OFF) SELECT /*+ opt_param('enable_mergejoin', 'off') */ tt1.*, tt2.* FROM tt1 JOIN tt2 on tt1.joincol = tt2.joincol;
EXPLAIN (COSTS OFF) SELECT/*+ opt_param('enable_hashjoin', 'on') */ tt1.*, tt2.* FROM tt1 JOIN tt2 on tt1.joincol = tt2.joincol;