SQL Reference

optimize-for-clause

>>-OPTIMIZE FOR--integer----+-ROWS-+---------------------------><
                            '-ROW--'
 

The OPTIMIZE FOR clause requests special processing of the select statement. If the clause is omitted, it is assumed that all rows of the result table will be retrieved; if it is specified, it is assumed that the number of rows retrieved will probably not exceed n where n is the value for integer. The value of n must be a positive integer. Use of the OPTIMIZE FOR clause influences query optimization based on the assumption that n rows will be retrieved. In addition, for cursors that are blocked, this clause will influence the number of rows that will be returned in each block (that is, no more than n rows will be returned in each block). If both the fetch-first-clause and the optimize-for-clause are specified, the lower of the integer values from these clauses will be used to influence the communications buffer size. The values are considered independently for optimization purposes.

This clause does not limit the number of rows that can be fetched or affect the result in any other way than performance. Using OPTIMIZE FOR n ROWS can improve the performance if no more than n rows are retrieved, but may degrade performance if more than n rows are retrieved.

If the value of n multiplied by the size of the row, exceeds the size of the communication buffer 55 the OPTIMIZE FOR clause will have no impact on the data buffers.

Examples of a select-statement

Example 1:  Select all columns and rows from the EMPLOYEE table.

  SELECT * FROM EMPLOYEE

Example 2:  Select the project name (PROJNAME), start date (PRSTDATE), and end date (PRENDATE) from the PROJECT table. Order the result table by the end date with the most recent dates appearing first.

  SELECT PROJNAME, PRSTDATE, PRENDATE
   FROM PROJECT          
   ORDER BY PRENDATE DESC

Example 3:  Select the department number (WORKDEPT) and average departmental salary (SALARY) for all departments in the EMPLOYEE table. Arrange the result table in ascending order by average departmental salary.

  SELECT WORKDEPT, AVG(SALARY)
     FROM EMPLOYEE   
     GROUP BY WORKDEPT 
     ORDER BY 2

Example 4:  Declare a cursor named UP_CUR to be used in a C program to update the start date (PRSTDATE) and the end date (PRENDATE) columns in the PROJECT table. The program must receive both of these values together with the project number (PROJNO) value for each row.

  EXEC SQL  DECLARE UP_CUR CURSOR FOR
              SELECT PROJNO, PRSTDATE, PRENDATE
                FROM PROJECT
                FOR UPDATE OF PRSTDATE, PRENDATE;

Example 5:  This example names the expression SAL+BONUS+COMM as TOTAL_PAY

   SELECT SALARY+BONUS+COMM AS TOTAL_PAY
     FROM EMPLOYEE
     ORDER BY TOTAL_PAY

Example 6:  Determine the employee number and salary of sales representatives along with the average salary and head count of their departments. Also, list the average salary of the department with the highest average salary.

Using a common table expression for this case saves the overhead of creating the DINFO view as a regular view. During statement preparation, accessing the catalog for the view is avoided and, because of the context of the rest of the fullselect, only the rows for the department of the sales representatives need to be considered by the view.

 WITH
    DINFO (DEPTNO, AVGSALARY, EMPCOUNT) AS
        (SELECT OTHERS.WORKDEPT, AVG(OTHERS.SALARY), COUNT(*)
          FROM EMPLOYEE OTHERS
          GROUP BY OTHERS.WORKDEPT
        ),
    DINFOMAX AS
        (SELECT MAX(AVGSALARY) AS AVGMAX FROM DINFO)
 SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY,
        DINFO.AVGSALARY, DINFO.EMPCOUNT, DINFOMAX.AVGMAX
  FROM EMPLOYEE THIS_EMP, DINFO, DINFOMAX
  WHERE THIS_EMP.JOB = 'SALESREP'
  AND THIS_EMP.WORKDEPT = DINFO.DEPTNO


Footnotes:

55
The size of the communication buffer is defined by the RQRIOBLK or the ASLHEAPSZ configuration parameter. See the Administration Guide for details.


[ Top of Page | Previous Page | Next Page ]