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.
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 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)