Multiple optimization modes are available. You can select one optimization mode as as the default mode for a PolarDB database cluster compatible with Oracle. You can also change this setting on a per-session basis by running the ALTER SESSION statement and or by running the DELETE, SELECT, or UPDATE statement within an optimizer hint. The configuration parameter that specifies the default mode is named OPTIMIZER_MODE. The following table shows the valid values of this parameter.

Hint Description
ALL_ROWS Optimizes retrieval of all rows of the result set.
CHOOSE Does not implement the default optimization based on the assumed number of rows to be retrieved from the result set. This is the default value.
FIRST_ROWS Optimizes retrieval of only the first row of the result set.
FIRST_ROWS_10 Optimizes retrieval of the first 10 rows of the results set.
FIRST_ROWS_100 Optimizes retrieval of the first 100 rows of the result set.
FIRST_ROWS_1000 Optimizes retrieval of the first 1,000 rows of the result set.
FIRST_ROWS(n) Optimizes retrieval of the first n rows of the result set. This form cannot be used as the object of the ALTER SESSION SET OPTIMIZER_MODE statement. This form can only be used as a hint in a SQL statement.

If you submit the SQL statement to use these optimization modes, you can only view the first n rows of the result set and abandon the other rows of the result set. The system allocates resources to the query based on this rule.

Examples

Modify the current session to optimize retrieval of the first 10 rows of the result set.

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;

You can run the SHOW statement to show the current value of the OPTIMIZER_MODE parameter. This statement is a utility dependent statement. In PSQL, the SHOW statement is used as follows:

SHOW OPTIMIZER_MODE;

optimizer_mode
----------------
 first_rows_10
(1 row)

The SHOW statement is compatible with Oracle databases and supports the following syntax:

SHOW PARAMETER OPTIMIZER_MODE;

NAME
--------------------------------------------------
VALUE
--------------------------------------------------
optimizer_mode
first_rows_10

The following example shows an optimization mode used as a hint in a SELECT statement:

SELECT /*+ FIRST_ROWS(7) */ * FROM emp;

 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+--------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
(14 rows)