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
| Index Scan: Name = MODL.EADMFN00_ASC ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: KEY0 (Ascending)
| #Columns = 0
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: ?
| | Stop Key: End of Index
| Index-Only Access
| Index Prefetch: None
| Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Index Predicate(s)
| | Insert Into Sorted Temp Table ID = t1
| | | #Columns = 1
| | | #Sort Key Columns = 1
| | | | Key 1: (Ascending)
| | | Sortheap Allocation Parameters:
| | | | #Rows = 1857
| | | | Row Width = 12
| | | Piped
| | | Duplicate Elimination
Sorted Temp Table Completion ID = t1
List Prefetch Preparation
| Access Table Name = MODL.EADMFN00 ID = 6,22
| | #Columns = 2
| | Fetch Using Prefetched List
| | | Prefetch: 50 Pages
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
| | | Insert Into Sorted Temp Table ID = t2
| | | | #Columns = 2
| | | | #Sort Key Columns = 1
| | | | | Key 1: KEY0 (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 1857
| | | | | Row Width = 744
| | | | Piped
Sorted Temp Table Completion ID = t2
Access Temp Table ID = t2
| #Columns = 2
| Relation Scan
| | Prefetch: Eligible
| Sargable Predicate(s)
| | Return Data to Application
| | | #Columns = 1
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
| Index Scan: Name = MODL.EADMFN00_ASC ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: KEY0 (Ascending)
| #Columns = 2
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: ?
| | Stop Key: End of Index
| Data Prefetch: Eligible 20
| Index Prefetch: Eligible 20
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Return Data to Application
| | | #Columns = 1
Return Data Completion
End of section
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail:
No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
