1) RETURN: (Return Result)
Cumulative Total Cost: 3.23844e+10
Cumulative CPU Cost: 3.05847e+16
Cumulative I/O Cost: 20372
Cumulative Re-Total Cost: 3.23843e+10
Cumulative Re-CPU Cost: 3.05847e+16
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 24334.7
Cumulative Comm Cost: 227622
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 20018
Remote communication cost: 143984
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v7.1.0.82 : s030617
ENVVAR : (Environment Variable)
DB2_HASH_JOIN = ON
Input Streams:
-------------
8) From Operator #2
Estimated number of rows:
2.21859e+06
Partition Map ID: -100
Partitioning: (COOR )
Coordin-
ator Pa-
rtition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+BILL_ACCT_NUM+BILL_ACCT_KEY
Partition Column Names:
----------------------
+NONE
2) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 3.23844e+10
Cumulative CPU Cost: 3.05847e+16
Cumulative I/O Cost: 20372
Cumulative Re-Total Cost: 3.23843e+10
Cumulative Re-CPU Cost: 3.05847e+16
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 24334.7
Cumulative Comm Cost: 227622
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 20018
Remote communication cost: 143984
Arguments:
---------
EARLYOUT: (Early Out flag)
FALSE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
Predicates:
----------
2) Predicate used in Join
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 5.5674e-07
Predicate Text:
--------------
(Q2.$C1 = CHAR(("SYSFUN
"."RIGHT"($INTERNAL_FUNC$(DIGITS(Q3.NUMBER_1_3 ), 4000),
"." 3) || "SYSFUN
"."
"."RIGHT"($INTERNAL_FUNC$(DIGITS(Q3.NUMBER_4_6 ), 4000),
"." 3) || "SYSFUN
"."
"."RIGHT"($INTERNAL_FUNC$(DIGITS(Q3.NUMBER_7_9 ), 4000),
"." 3)), 9))
Input Streams:
-------------
3) From Operator #3
Estimated number of rows:
1.43694e+07
Partition Map ID: -100
Partitioning: (COOR )
Coordin-
ator Pa-
rtition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+BILL_ACCT_KEY+BILL_ACCT_NUM
Partition Column Names:
----------------------
+NONE
7) From Operator #5
Estimated number of rows: 0.154397
Partition Map ID: -100
Partitioning: (COOR )
Coordin-
ator Pa-
rtition
Number of columns: 3
Subquery predicate ID: Not
Applicable
Column Names:
------------
+NUMBER_7_9+NUMBER_4_6+NUMBER_1_3
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
8) To Operator #1
Estimated number of rows:
2.21859e+06
Partition Map ID: -100
Partitioning: (COOR )
Coordin-
ator Pa-
rtition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+BILL_ACCT_NUM+BILL_ACCT_KEY
Partition Column Names:
----------------------
+NONE
3) TQ : (Table Queue)
Cumulative Total Cost: 83601.9
Cumulative CPU Cost: 9.05114e+09
Cumulative I/O Cost: 18913
Cumulative Re-Total Cost: 3275
Cumulative Re-CPU Cost: 3.09301e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.1882
Cumulative Comm Cost: 227622
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 18913
Arguments:
---------
JN INPUT: (Join input leg)
OUTER
LISTENER: (Listener Table Queue type)
FALSE
TQMERGE : (Merging Table Queue flag)
FALSE
TQREAD : (Table Queue Read type)
READ AHEAD
TQSEND : (Table Queue Write type)
DIRECTED
UNIQUE : (Uniqueness required flag)
FALSE
Input Streams:
-------------
2) From Operator #4
Estimated number of rows:
1.79617e+06
Partition Map ID: -100
Partitioning: (MULT )
Multiple
Partiti-
ons
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+BILL_ACCT_KEY+BILL_ACCT_NUM
Partition Column Names:
----------------------
+1: BILL_ACCT_KEY
Output Streams:
--------------
3) To Operator #2
Estimated number of rows:
1.43694e+07
Partition Map ID: -100
Partitioning: (COOR )
Coordin-
ator Pa-
rtition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+BILL_ACCT_KEY+BILL_ACCT_NUM
Partition Column Names:
----------------------
+NONE
4) TBSCAN: (Table Scan)
Cumulative Total Cost: 77388.4
Cumulative CPU Cost: 3.18289e+09
Cumulative I/O Cost: 18913
Cumulative Re-Total Cost: 3275
Cumulative Re-CPU Cost: 3.09301e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.0522
Cumulative Comm Cost: 0
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 18913
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
Input Streams:
-------------
1) From Object BILL_DW.BILL_ACCT
Estimated number of rows:
1.79617e+06
Partition Map ID: 6
Partitioning: (MULT )
Multiple
Partiti-
ons
Number of columns: 3
Subquery predicate ID: Not
Applicable
Column Names:
------------
+$RID$+BILL_ACCT_NUM+BILL_ACCT_KEY
Partition Column Names:
----------------------
+1: BILL_ACCT_KEY
Output Streams:
--------------
2) To Operator #3
Estimated number of rows:
1.79617e+06
Partition Map ID: -100
Partitioning: (MULT )
Multiple
Partiti-
ons
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+BILL_ACCT_KEY+BILL_ACCT_NUM
Partition Column Names:
----------------------
+1: BILL_ACCT_KEY
5) TBSCAN: (Table Scan)
Cumulative Total Cost: 11966.4
Cumulative CPU Cost: 2.94321e+09
Cumulative I/O Cost: 354
Cumulative Re-Total Cost: 2253.7
Cumulative Re-CPU Cost: 2.12846e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 11966.4
Cumulative Comm Cost: 0
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 1105
Remote communication cost: 143984
Arguments:
---------
JN INPUT: (Join input leg)
INNER
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
SCANDIR : (Scan Direction)
FORWARD
Predicates:
----------
2) Sargable Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 5.5674e-07
Predicate Text:
--------------
(Q2.$C1 = CHAR(("SYSFUN
"."RIGHT"($INTERNAL_FUNC$(DIGITS(Q3.NUMBER_1_3 ), 4000),
"." 3) || "SYSFUN
"."
"."RIGHT"($INTERNAL_FUNC$(DIGITS(Q3.NUMBER_4_6 ), 4000),
"." 3) || "SYSFUN
"."
"."RIGHT"($INTERNAL_FUNC$(DIGITS(Q3.NUMBER_7_9 ), 4000),
"." 3)), 9))
Input Streams:
-------------
6) From Operator #6
Estimated number of rows: 277324
Partition Map ID: -100
Partitioning: (COOR )
Coordin-
ator Pa-
rtition
Number of columns: 3
Subquery predicate ID: Not
Applicable
Column Names:
------------
+NUMBER_7_9+NUMBER_4_6+NUMBER_1_3
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
7) To Operator #2
Estimated number of rows: 0.154397
Partition Map ID: -100
Partitioning: (COOR )
Coordin-
ator Pa-
rtition
Number of columns: 3
Subquery predicate ID: Not
Applicable
Column Names:
------------
+NUMBER_7_9+NUMBER_4_6+NUMBER_1_3
Partition Column Names:
----------------------
+NONE
6) TEMP : (Create Temporary Table)
Cumulative Total Cost: 9712.65
Cumulative CPU Cost: 8.14717e+08
Cumulative I/O Cost: 354
Cumulative Re-Total Cost: 0
Cumulative Re-CPU Cost: 0
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 9712.65
Cumulative Comm Cost: 0
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 1105
Remote communication cost: 143984
Arguments:
---------
CSETEMP : (Temp over common sub-expression flag)
FALSE
SLOWMAT : (Slow Materialization flag)
FALSE
TEMPSIZE: (Temporary Table Page Size)
4096
Input Streams:
-------------
5) From Operator #7
Estimated number of rows: 277324
Partition Map ID: -100
Partitioning: (COOR )
Coordin-
ator Pa-
rtition
Number of columns: 3
Subquery predicate ID: Not
Applicable
Column Names:
------------
+NUMBER_7_9+NUMBER_4_6+NUMBER_1_3
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
6) To Operator #5
Estimated number of rows: 277324
Partition Map ID: -100
Partitioning: (COOR )
Coordin-
ator Pa-
rtition
Number of columns: 3
Subquery predicate ID: Not
Applicable
Column Names:
------------
+NUMBER_7_9+NUMBER_4_6+NUMBER_1_3
Partition Column Names:
----------------------
+NONE
--
Posted via
http://dbforums.com