Default Optimization Modes v10
There are a number of optimization modes that can be chosen as the default setting for an Advanced Server database cluster. This setting can also be changed on a per session basis by using the ALTER SESSION command as well as in individual DELETE, SELECT, and UPDATE commands within an optimizer hint. The configuration parameter that controls these default modes is named OPTIMIZER_MODE.
The following table shows the possible values.
| Hint | Description |
|---|---|
ALL_ROWS | Optimizes for retrieval of all rows of the result set. |
CHOOSE | Does no default optimization based on assumed number of rows to be retrieved from the result set. This is the default. |
FIRST_ROWS | Optimizes for retrieval of only the first row of the result set. |
FIRST_ROWS_10 | Optimizes for retrieval of the first 10 rows of the results set. |
FIRST_ROWS_100 | Optimizes for retrieval of the first 100 rows of the result set. |
FIRST_ROWS_1000 | Optimizes for retrieval of the first 1000 rows of the result set. |
FIRST_ROWS(n) | Optimizes for retrieval of the first n rows of the result set. This form may not be used as the object of the ALTER SESSION SET OPTIMIZER_MODE command. It may only be used in the form of a hint in a SQL command. |
These optimization modes are based upon the assumption that the client submitting the SQL command is interested in viewing only the first “n” rows of the result set and will then abandon the remainder of the result set. Resources allocated to the query are adjusted as such.
Examples
Alter the current session to optimize for retrieval of the first 10 rows of the result set.
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;
The current value of the OPTIMIZER_MODE parameter can be shown by using the SHOW command. Note that this command is a utility dependent command. In PSQL, the SHOW command is used as follows:
SHOW OPTIMIZER_MODE; optimizer_mode ----------------- first_rows_10 (1 row)
The SHOW command, compatible with Oracle databases, has the following syntax:
SHOW PARAMETER OPTIMIZER_MODE; NAME -------------------------------------------------- VALUE -------------------------------------------------- optimizer_mode first_rows_10
The following example shows an optimization mode used in a SELECT command as a hint:
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)