Multiple optimization modes are available. You can select one optimization mode as as the default mode for a PolarDB for PostgreSQL(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.

HintDescription
ALL_ROWSOptimizes retrieval of all rows of the result set.
CHOOSEDoes 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_ROWSOptimizes retrieval of only the first row of the result set.
FIRST_ROWS_10Optimizes retrieval of the first 10 rows of the results set.
FIRST_ROWS_100Optimizes retrieval of the first 100 rows of the result set.
FIRST_ROWS_1000Optimizes 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 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)