Hi,
I'm trying to interpet a db2 explain. I'm trying to determine if the SQL is
being executed as I expect/hope.
The sql is generally:
select key from table where key1=value and key2 > value2 and key2 < maxkey
order by key2 fetch first 1 row only
2 notes: key1 = value is 99% of the table and the max key is more or less
useless b/c its always the last possible key, making the statement for
practical purposes:
select key from table key2 > value2 order by key2 fetch first 1 row only
By big question is: If their were 26 rows w/ keys A-Z , and the value of
value2 is B, does the explain show that the sql statement still inserts C-Z
into the temp table and sort it?
Below is the explain of the statement in question.
Thanks!
Frank
--
-------------------- SECTION ---------------------------------------
Section = 17
SQL Statement:
SELECT LFKEY, CDATA INTO :H00038 :H00039 , :H00018 :H00019
FROM RCFGTD
WHERE LFPFX=:H00004 :H00005 AND LFKEY >= :H00038 :H00039 AND LFKEY
<= :H00020 :H00021
ORDER BY lfkey
FETCH FIRST 1 ROW ONLY
Section Code Page = 819
Estimated Cost = 78.628693
Estimated Cardinality = 1.000000
Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Piped Inner
| Access Table Name = PRODPCIA.RCFGTD ID = 22,115
| | #Columns = 0
| | Index Scan: Name = PRODPCIA.LNG01_RCFGTD_PK ID = 1
| | | Regular Index (Clustered)
| | | Index Columns:
| | | | 1: LFPFX (Ascending)
| | | | 2: LFKEY (Ascending)
| | | #Key Columns = 2
| | | | Start Key: Inclusive Value
| | | | | 1: ?
| | | | | 2: ?
| | | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | | | 2: ?
| | | 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 = 43
| | | | | 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.RCFGTD ID = 22,115
| | | #Columns = 2
| | | Relation Scan
| | | | Prefetch: 5 Pages
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Predicate(s)
| | | | #Predicates = 3
Insert Into Sorted Temp Table ID = t2
| #Columns = 2
| #Sort Key Columns = 1
| | Key 1: LFKEY (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 1
| | Row Width = 3504
| | 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
Optimizer Plan:
RETURN
( 1)
|
TBSCAN
( 2)
|
SORT
( 3)
|
NLJOIN
( 4)
/ \
TBSCAN RIDSCN
( 5) ( 7)
| |
TFunc: SORT
SYSIBM ( 8)
GENROW |
IXSCAN
( 9)
/ \
Index: Table:
PRODPCIA PRODPCIA
LNG01_RCFGTD_PK RCFGTD
