Folks,

 Our client has a program that browses whole table from begining to end.
 The table has 1 million rows in it. REORGCHK does not show any problems.
 It has unique index defined on KEY0 column.

 If I use SELECT statement without OPTIMIZE FOR clause, then it uses
 temporary table to sort the data, but if I use OPTIMIZE clause then it
 uses index access without temporary table. If I use OPTIMIZE FOR more than
 700 rows then it uses temporary table again.

 How can I influence the optimizer not to use temporary table?

 If I use OPTIMIZE FOR clause then what will happen to the performance
 after 700th row?

 I will not be able to modify the program.

 Thanks.

# db2expln -d test -f /home/db2modl/tmp/test.ddl  -t

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

       Isolation Level          = Cursor Stability
       Blocking                 = Block Unambiguous Cursors
       Query Optimization Class = 5

       Partition Parallel       = No
       Intra-Partition Parallel = No

       SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "DB2MODL"

SQL Statement:
 
 SELECT REC
 FROM MODL.EADMFN00
 WHERE KEY0 >=?
 ORDER BY KEY0 ASC
 FOR
 FETCH ONLY

Section Code Page = 819

Estimated Cost = 107.394234
Estimated Cardinality = 1856.888184

Access Table Name = MODL.EADMFN00  ID = 6,22

Sorted Temp Table Completion  ID = t1
List Prefetch Preparation

Sorted Temp Table Completion  ID = t2
Access Temp Table  ID = t2

Return Data Completion

End of section

# db2expln -d test -f /home/db2modl/tmp/test1.ddl  -t

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

       Isolation Level          = Cursor Stability
       Blocking                 = Block Unambiguous Cursors
       Query Optimization Class = 5

       Partition Parallel       = No
       Intra-Partition Parallel = No

       SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "DB2MODL"

SQL Statement:
 
 SELECT REC
 FROM MODL.EADMFN00
 WHERE KEY0 >=?
 ORDER BY KEY0 ASC
 OPTIMIZE
 FOR 700 ROWS
 FOR
 FETCH ONLY

Section Code Page = 819

Estimated Cost = 238.005768
Estimated Cardinality = 1856.888184

Access Table Name = MODL.EADMFN00  ID = 6,22

Return Data Completion

End of section