1. Is there an index on key?
2. Have you executed the runstats command on the table and indexes? You
must rebind after runstats is performed.
2. How many rows are in the answer set without the fetch first row only?
3. I would use "where key between curr_key and key_max" although it might
not make any difference depending on which query optimization level is
used.
Interesting enough I found the db2expln for this sql statement and the SAME
sql statement on another system where things perform better ,, Now that I
found this info not sure what to do with it:
--Path on bad system--
Section Code Page = 819
Estimated Cost = 104.929337
Estimated Cardinality = 1.000000
Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Piped Inner
| Access Table Name = PRODPCIA.RIAUDT ID = 16,4
| | #Columns = 0
| | Index Scan: Name = PRODPCIA.AUD01_ RIAUDT_PK ID = 1
| | | Regular Index (Clustered)
| | | Index Columns:
| | | | 1: LFKEY (Ascending)
| | | #Key Columns = 1
| | | | Start Key: Exclusive Value
| | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | Index-Only Access
| | | Index Prefetch: None
| | | Insert Into Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 76
| | | | | Row Width = 12
| | | | Piped
| | | | Duplicate Elimination
| | Isolation Level: Uncommitted Read
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| Sorted Temp Table Completion ID = t1
| List Prefetch Preparation
| | Access Table Name = PRODPCIA.RIAUDT ID = 16,4
| | | #Columns = 2
| | | Relation Scan
| | | | Prefetch: 10 Pages
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Predicate(s)
| | | | #Predicates = 2
Insert Into Sorted Temp Table ID = t2
| #Columns = 2
| #Sort Key Columns = 1
| | Key 1: LFKEY (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 1
| | Row Width = 140
| | Sort Limited To Estimated Row Count
| Piped
Access Temp Table ID = t2
| #Columns = 2
| Single Record
| Relation Scan
| | Prefetch: Eligible
Return Data to Application
| #Columns = 2
End of section
--- On good system --
SQL Statement:
SELECT LFKEY, CDATA INTO :H00004 :H00005 , :H00010 :H00011
FROM RIAUDT
WHERE LFKEY > :H00004 :H00005 AND LFKEY <= :H00012 :H00013
ORDER BY lfkey
FETCH FIRST 1 ROW ONLY
Section Code Page = 819
Estimated Cost = 0.161109
Estimated Cardinality = 0.000000
Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Access Table Name = DB2C81.RIAUDT ID = 3,25
| | #Columns = 2
| | Index Scan: Name = DB2C81.RIAUDT_P K ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: LFKEY (Ascending)
| | | #Key Columns = 1
| | | | Start Key: Exclusive Value
| | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | Data Prefetch: None
| | | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Return Data to Application
| #Columns = 2
End of section
