DB2 v8.2/AIX 5.3 (Non Partitioned)
Why the cost of NLJOIN(11) suddenly goes too high(102969) in the below plan?
Costs of the input streams are respectively 134 & 51.
reorgchk flags only F6 for the index XPKECMBUSINESSSTP as below and the clusterratio is very good.
HTML Code:
SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: CCSOWNER.ECMBUSINESSSTEP
CCSOWNER ECMBUSINESSSTEP 1.4e+08 0 2e+06 2e+06 - 3.29e+10 0 99 100 ---
----------------------------------------------------------------------------------------
SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: CCSOWNER.ECMBUSINESSSTEP
CCSOWNER XPKECMBUSINESSSTP 1e+08 2e+05 0 4 12 0 1e+08 100 81 190 0 0 --*--
HTML Code:
2000
^NLJOIN
( 11)
102969
8054.03
/-------+------\
2000 1
TBSCAN FETCH
( 12) ( 18)
134.387 51.4365
54.0295 4
| /---+---\
2000 1 1.36401e+08
SORT IXSCAN TABLE: CCSOWNER
( 13) ( 19) ECMBUSINESSSTEP
134.209 38.5812 Q3
54.0295 3
| |
2000 1.36401e+08
FETCH INDEX: CCSOWNER
( 14) XPKECMBUSINESSSTP
132.402 Q3
54.0295
/---+---\
2000 1.36401e+08
RIDSCN TABLE: INTXN
( 15) TASK
33.3465 Q2
2.43003
|
2000
SORT
( 16)
33.346
2.43003
|
2000
IXSCAN
( 17)
32.4777
2.43003
|
1.36401e+08
INDEX: INTXN
TASK_I3
Q2
dBforums
>
Cost of an NLJOIN from db2exfmt
03-06-09, 10:24
Linear Mode
