互联网服务
处女帖！NLJOIN后，cost，io 剧增
三表关联，关联字段都有索引。不明白，为什么左下 NL join后，cpu和io 会剧增。。收起
查看其它 19 个回答宝贝敏敏的回答
感谢楼上两位大哥回帖。
统计信息是最新的。
db2exfmt如下：
DB2_VERSION: 09.07.4
Database Context:
----------------
Parallelism: None
CPU Speed: 2.519169e-07
Comm Speed: 100
Buffer Pool size: 9801024
Sort Heap size: 5327
Database Heap size: 3365
Lock List size: 131072
Maximum Lock List: 80
Average Applications: 1
Locks Available: 3355443
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 3
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
select ordi.ORDER_ITEM_ID as orderItemId, ordi.POSORDER_ID as posOrderId,
ordi.SAP_ORDER_ID as sapOrderId, ordi.ORDER_TYPE as orderType,
ord.ORDER_TIME as orderDate, ordi.STORE_CODE as storeCode,
ordi.SAP_VOUCHER_NO as sapVoucherNo
from OMSADM.SO_ORDI ordi ,OMSADM.SO_RET_ORDER retord ,OMSADM.SO_ORD ord
Where retord.SYNCPOSFLAG=0 and ordi.ORDER_ITEM_ID=retord.RETORD_ITEM_ID and
ordi.ORDER_ID = ord.ORDER_ID
fetch first 150 rows only
Optimized Statement:
-------------------
SELECT Q3.ORDER_ITEM_ID AS "ORDERITEMID", Q3.POSORDER_ID AS "POSORDERID",
Q3.SAP_ORDER_ID AS "SAPORDERID", Q3.ORDER_TYPE AS "ORDERTYPE",
Q1.ORDER_TIME AS "ORDERDATE", Q3.STORE_CODE AS "STORECODE",
Q3.SAP_VOUCHER_NO AS "SAPVOUCHERNO"
FROM OMSADM.SO_ORD AS Q1, OMSADM.SO_RET_ORDER AS Q2, OMSADM.SO_ORDI AS Q3
WHERE (Q3.ORDER_ID = Q1.ORDER_ID) AND (Q3.ORDER_ITEM_ID = Q2.RETORD_ITEM_ID)
AND (DECFLOAT(Q2.SYNCPOSFLAG, '............') = 0)
Access Plan:
-----------
Total Cost: 122793
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
150
^NLJOIN
( 2)
122793
84718.2
/---+----\
12850.9 1
TBSCAN IXSCAN
( 3) ( 8)
59450.2 22.7073
23757.8 3
| |
12850.9 2.80705e+06
SORT INDEX: OMSADM
( 4) I_ORD_03
59449.4 Q1
23757.8
|
12850.9
^NLJOIN
( 5)
59441.3
23757.8
/-----+------\
12850.9 1
IXSCAN IXSCAN
( 6) ( 7)
3081.51 22.7033
1909.88 3
| |
343872 3.53634e+06
INDEX: OMSADM INDEX: OMSADM
I_RETORDER_11 I_ORDI_07
Q2 Q3
Extended Diagnostic Information:
--------------------------------
No extended Diagnostic Information for this statement.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 122793
Cumulative CPU Cost: 2.4061e+09
Cumulative I/O Cost: 84718.2
Cumulative Re-Total Cost: 456.36
Cumulative Re-CPU Cost: 1.81155e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 59472.1
Estimated Bufferpool Buffers: 60960.4
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v9.7.0.4 : s110330
HEAPUSE : (Maximum Statement Heap Usage)
128 Pages
PREPTIME: (Statement prepare time)
21 milliseconds
STMTHEAP: (Statement heap size)
8192
Input Streams:
-------------
10) From Operator #2
Estimated number of rows: 150
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.SAPVOUCHERNO+Q4.STORECODE+Q4.ORDERDATE
+Q4.ORDERTYPE+Q4.SAPORDERID+Q4.POSORDERID
+Q4.ORDERITEMID
2) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 122793
Cumulative CPU Cost: 2.4061e+09
Cumulative I/O Cost: 84718.2
Cumulative Re-Total Cost: 456.36
Cumulative Re-CPU Cost: 1.81155e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 59472.1
Estimated Bufferpool Buffers: 60960.4
Arguments:
---------
EARLYOUT: (Early Out flag)
LEFT
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
38549
Predicates:
----------
2) Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 3.56245e-07
Predicate Text:
--------------
(Q3.ORDER_ID = Q1.ORDER_ID)
Input Streams:
-------------
7) From Operator #3
Estimated number of rows: 12850.9
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ID(A)+Q3.SAP_VOUCHER_NO
+Q3.STORE_CODE+Q3.ORDER_TYPE+Q3.SAP_ORDER_ID
+Q3.POSORDER_ID+Q3.ORDER_ITEM_ID
9) From Operator #8
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.ORDER_ID(A)+Q1.ORDER_TIME
Output Streams:
--------------
10) To Operator #1
Estimated number of rows: 150
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.SAPVOUCHERNO+Q4.STORECODE+Q4.ORDERDATE
+Q4.ORDERTYPE+Q4.SAPORDERID+Q4.POSORDERID
+Q4.ORDERITEMID
3) TBSCAN: (Table Scan)
Cumulative Total Cost: 59450.2
Cumulative CPU Cost: 1.47067e+09
Cumulative I/O Cost: 23757.8
Cumulative Re-Total Cost: 316.156
Cumulative Re-CPU Cost: 1.255e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 59449.4
Estimated Bufferpool Buffers: 0
Arguments:
---------
JN INPUT: (Join input leg)
OUTER
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
SCANDIR : (Scan Direction)
FORWARD
SPEED : (Assumed speed of scan, in sharing structures)
SLOW
THROTTLE: (Scan may be throttled, for scan sharing)
FALSE
VISIBLE : (May be included in scan sharing structures)
FALSE
WRAPPING: (Scan may start anywhere and wrap)
FALSE
Input Streams:
-------------
6) From Operator #4
Estimated number of rows: 12850.9
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ID(A)+Q3.SAP_VOUCHER_NO
+Q3.STORE_CODE+Q3.ORDER_TYPE+Q3.SAP_ORDER_ID
+Q3.POSORDER_ID+Q3.ORDER_ITEM_ID
Output Streams:
--------------
7) To Operator #2
Estimated number of rows: 12850.9
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ID(A)+Q3.SAP_VOUCHER_NO
+Q3.STORE_CODE+Q3.ORDER_TYPE+Q3.SAP_ORDER_ID
+Q3.POSORDER_ID+Q3.ORDER_ITEM_ID
4) SORT : (Sort)
Cumulative Total Cost: 59449.4
Cumulative CPU Cost: 1.46745e+09
Cumulative I/O Cost: 23757.8
Cumulative Re-Total Cost: 315.346
Cumulative Re-CPU Cost: 1.25179e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 59449.4
Estimated Bufferpool Buffers: 23758
Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
FALSE
NUMROWS : (Estimated number of rows)
12851
ROWWIDTH: (Estimated width of rows)
92
SORTKEY : (Sort Key column)
1: Q3.ORDER_ID(A)
TEMPSIZE: (Temporary Table Page Size)
8192
UNIQUE : (Uniqueness required flag)
FALSE
Input Streams:
-------------
5) From Operator #5
Estimated number of rows: 12850.9
Number of columns: 8
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RETORD_ITEM_ID+Q3.SAP_VOUCHER_NO
+Q3.STORE_CODE+Q3.ORDER_TYPE+Q3.SAP_ORDER_ID
+Q3.POSORDER_ID+Q3.ORDER_ID+Q3.ORDER_ITEM_ID
Output Streams:
--------------
6) To Operator #3
Estimated number of rows: 12850.9
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ID(A)+Q3.SAP_VOUCHER_NO
+Q3.STORE_CODE+Q3.ORDER_TYPE+Q3.SAP_ORDER_ID
+Q3.POSORDER_ID+Q3.ORDER_ITEM_ID
5) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 59441.3
Cumulative CPU Cost: 1.43539e+09
Cumulative I/O Cost: 23757.8
Cumulative Re-Total Cost: 315.346
Cumulative Re-CPU Cost: 1.25179e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 38.0698
Estimated Bufferpool Buffers: 23758
Arguments:
---------
EARLYOUT: (Early Out flag)
LEFT
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
20019
Predicates:
----------
3) Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 2.82778e-07
Predicate Text:
--------------
(Q3.ORDER_ITEM_ID = Q2.RETORD_ITEM_ID)
Input Streams:
-------------
2) From Operator #6
Estimated number of rows: 12850.9
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RETORD_ITEM_ID(A)+Q2.SYNCPOSFLAG(A)
4) From Operator #7
Estimated number of rows: 1
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ITEM_ID(A)+Q3.SAP_VOUCHER_NO(A)
+Q3.STORE_CODE(A)+Q3.ORDER_TYPE(A)
+Q3.SAP_ORDER_ID(A)+Q3.POSORDER_ID(A)
+Q3.ORDER_ID
Output Streams:
--------------
5) To Operator #4
Estimated number of rows: 12850.9
Number of columns: 8
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RETORD_ITEM_ID+Q3.SAP_VOUCHER_NO
+Q3.STORE_CODE+Q3.ORDER_TYPE+Q3.SAP_ORDER_ID
+Q3.POSORDER_ID+Q3.ORDER_ID+Q3.ORDER_ITEM_ID
6) IXSCAN: (Index Scan)
Cumulative Total Cost: 3081.51
Cumulative CPU Cost: 9.10672e+08
Cumulative I/O Cost: 1909.88
Cumulative Re-Total Cost: 226.185
Cumulative Re-CPU Cost: 8.97854e+08
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 15.3665
Estimated Bufferpool Buffers: 1910
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
JN INPUT: (Join input leg)
OUTER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1608
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Predicates:
----------
4) Sargable Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0373712
Predicate Text:
--------------
(DECFLOAT(Q2.SYNCPOSFLAG, '............') = 0)
Input Streams:
-------------
1) From Object OMSADM.I_RETORDER_11
Estimated number of rows: 343872
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RETORD_ITEM_ID(A)+Q2.SYNCPOSFLAG(A)
+Q2.$RID$
Output Streams:
--------------
2) To Operator #5
Estimated number of rows: 12850.9
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RETORD_ITEM_ID(A)+Q2.SYNCPOSFLAG(A)
7) IXSCAN: (Index Scan)
Cumulative Total Cost: 22.7033
Cumulative CPU Cost: 92402.5
Cumulative I/O Cost: 3
Cumulative Re-Total Cost: 7.56779
Cumulative Re-CPU Cost: 30941.5
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 22.7033
Estimated Bufferpool Buffers: 20022.4
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
JN INPUT: (Join input leg)
INNER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Predicates:
----------
3) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 2.82778e-07
Predicate Text:
--------------
(Q3.ORDER_ITEM_ID = Q2.RETORD_ITEM_ID)
3) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 2.82778e-07
Predicate Text:
--------------
(Q3.ORDER_ITEM_ID = Q2.RETORD_ITEM_ID)
Input Streams:
-------------
3) From Object OMSADM.I_ORDI_07
Estimated number of rows: 3.53634e+06
Number of columns: 8
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ITEM_ID(A)+Q3.SAP_VOUCHER_NO(A)
+Q3.STORE_CODE(A)+Q3.ORDER_TYPE(A)
+Q3.SAP_ORDER_ID(A)+Q3.POSORDER_ID(A)+Q3.$RID$
+Q3.ORDER_ID
Output Streams:
--------------
4) To Operator #5
Estimated number of rows: 1
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ITEM_ID(A)+Q3.SAP_VOUCHER_NO(A)
+Q3.STORE_CODE(A)+Q3.ORDER_TYPE(A)
+Q3.SAP_ORDER_ID(A)+Q3.POSORDER_ID(A)
+Q3.ORDER_ID
8) IXSCAN: (Index Scan)
Cumulative Total Cost: 22.7073
Cumulative CPU Cost: 108169
Cumulative I/O Cost: 3
Cumulative Re-Total Cost: 7.57177
Cumulative Re-CPU Cost: 46708.1
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 22.7073
Estimated Bufferpool Buffers: 99718
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
JN INPUT: (Join input leg)
INNER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Predicates:
----------
2) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 3.56245e-07
Predicate Text:
--------------
(Q3.ORDER_ID = Q1.ORDER_ID)
2) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 3.56245e-07
Predicate Text:
--------------
(Q3.ORDER_ID = Q1.ORDER_ID)
Input Streams:
-------------
8) From Object OMSADM.I_ORD_03
Estimated number of rows: 2.80705e+06
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.ORDER_ID(A)+Q1.$RID$+Q1.ORDER_TIME
Output Streams:
--------------
9) To Operator #2
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.ORDER_ID(A)+Q1.ORDER_TIME
Objects Used in Access Plan:
---------------------------
Schema: OMSADM
Name: SO_ORD
Type: Table (reference only)
Schema: OMSADM
Name: SO_ORDI
Type: Table (reference only)
Schema: OMSADM
Name: SO_RET_ORDER
Type: Table (reference only)
Schema: OMSADM
Name: I_ORDI_07
Type: Index
Time of creation: 2013-03-27-09.29.46.614713
Last statistics update: 2013-06-13-00.03.14.550000
Number of columns: 12
Number of rows: 3536339
Width of rows: -1
Number of buffer pool pages: 394639
Distinct row values: Yes
Tablespace name: IDX_HOT_SPACE
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 192
Container extent page count: 32
Index clustering statistic: 0.973099
Index leaf pages: 75900
Index tree levels: 4
Index full key cardinality: 3536344
Index first key cardinality: 3536344
Index first 2 keys cardinality: 3536344
Index first 3 keys cardinality: 3536344
Index first 4 keys cardinality: 3536344
Index sequential pages: 56044
Index page density: 89
Index avg sequential pages: 1245
Index avg gap between sequences:440
Index avg random pages: 14
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 3536344
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: OMSADM
Base Table Name: SO_ORDI
Columns in index:
ORDER_ITEM_ID(A)
SAP_VOUCHER_NO(I)
STORE_CODE(I)
ORDER_TYPE(I)
SAP_ORDER_ID(I)
POSORDER_ID(I)
SALE_ORG(I)
ORDER_ID(I)
B2CORDER_ITEM_ID(I)
B2C_ORDER_ID(I)
ORDITM_CLASS(I)
CREATED_TIME(I)
Schema: OMSADM
Name: I_ORD_03
Type: Index
Time of creation: 2013-03-27-09.29.46.144777
Last statistics update: 2013-06-13-00.00.35.810000
Number of columns: 29
Number of rows: 2807054
Width of rows: -1
Number of buffer pool pages: 113275
Distinct row values: Yes
Tablespace name: IDX_HOT_SPACE
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 192
Container extent page count: 32
Index clustering statistic: 0.983532
Index leaf pages: 99715
Index tree levels: 4
Index full key cardinality: 2807055
Index first key cardinality: 2807055
Index first 2 keys cardinality: 2807055
Index first 3 keys cardinality: 2807055
Index first 4 keys cardinality: 2807055
Index sequential pages: 98834
Index page density: 87
Index avg sequential pages: 19766
Index avg gap between sequences:217
Index avg random pages: 6
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 2807055
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: OMSADM
Base Table Name: SO_ORD
Columns in index:
ORDER_ID(A)
BILL_TYPE(I)
ACTIVE_FLAG(I)
MEN_DBNO(I)
MEM_CARD_ID(I)
MEM_IN_CARD_NO(I)
MEM_OUT_CARD_NO(I)
MEM_TYPE(I)
MEM_NAME(I)
MEM_LEVEL(I)
CLIENT_ID(I)
KZ_GROUP_CARD(I)
TOTAL_ITEM(I)
SERVICE_AMOUNT(I)
IS_INVOICE(I)
TRANSPORT_FEE(I)
USER_ID(I)
ORDER_PAY_AMOUNT(I)
ORDER_SALE_AMOUNT(I)
ORDER_STATUS(I)
REAL_PAY_AMOUNT(I)
VERI_CODE(I)
ORDER_CATEGORY(I)
PAY_ITEM_NUM(I)
ORDER_COMMENTS(I)
ORDER_CHANNEL(I)
ORDER_RES(I)
ORDER_TIME(I)
B2C_ORDER_ID(I)
Schema: OMSADM
Name: I_RETORDER_11
Type: Index
Time of creation: 2013-04-16-00.50.43.794651
Last statistics update: 2013-06-13-00.05.24.150000
Number of columns: 2
Number of rows: 343872
Width of rows: -1
Number of buffer pool pages: 11946
Distinct row values: Yes
Tablespace name: IDX_NORMAL_SPACE
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 192
Container extent page count: 32
Index clustering statistic: 0.994797
Index leaf pages: 1608
Index tree levels: 3
Index full key cardinality: 343872
Index first key cardinality: 343872
Index first 2 keys cardinality: 343872
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 1473
Index page density: 83
Index avg sequential pages: 1473
Index avg gap between sequences:0
Index avg random pages: 127
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 343872
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: OMSADM
Base Table Name: SO_RET_ORDER
Columns in index:
RETORD_ITEM_ID(A)
SYNCPOSFLAG(I)
Base Table For Index Not Already Shown:
---------------------------------------
Schema: OMSADM
Name: SO_ORD
Time of creation: 2013-03-27-09.27.49.168468
Last statistics update: 2013-06-13-00.00.35.813008
Number of data partitions: 1
Number of columns: 38
Number of rows: 2807054
Number of pages: 113275
Number of pages with rows: 113275
Table overflow record count: 27
Indexspace name: IDX_HOT_SPACE
Tablespace name: HOT_SPACE
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Prefetch page count: -1
Container extent page count: 32
Long tablespace name: LOBSPACE
Schema: OMSADM
Name: SO_ORDI
Time of creation: 2013-03-27-09.27.51.983524
Last statistics update: 2013-06-13-00.03.14.552644
Number of data partitions: 1
Number of columns: 100
Number of rows: 3536339
Number of pages: 394639
Number of pages with rows: 394639
Table overflow record count: 2238
Indexspace name: IDX_HOT_SPACE
Tablespace name: HOT_SPACE
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Prefetch page count: -1
Container extent page count: 32
Long tablespace name: LOBSPACE
Schema: OMSADM
Name: SO_RET_ORDER
Time of creation: 2013-03-27-09.28.43.603037
Last statistics update: 2013-06-13-00.05.24.156095
Number of data partitions: 1
Number of columns: 33
Number of rows: 343872
Number of pages: 11946
Number of pages with rows: 11946
Table overflow record count: 472
Indexspace name: IDX_NORMAL_SPACE
Tablespace name: NORMAL_SPACE
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Prefetch page count: -1
Container extent page count: 32
Long tablespace name: LOBSPACE
统计信息是最新的。
db2exfmt如下：
DB2_VERSION: 09.07.4
Database Context:
----------------
Parallelism: None
CPU Speed: 2.519169e-07
Comm Speed: 100
Buffer Pool size: 9801024
Sort Heap size: 5327
Database Heap size: 3365
Lock List size: 131072
Maximum Lock List: 80
Average Applications: 1
Locks Available: 3355443
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 3
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
select ordi.ORDER_ITEM_ID as orderItemId, ordi.POSORDER_ID as posOrderId,
ordi.SAP_ORDER_ID as sapOrderId, ordi.ORDER_TYPE as orderType,
ord.ORDER_TIME as orderDate, ordi.STORE_CODE as storeCode,
ordi.SAP_VOUCHER_NO as sapVoucherNo
from OMSADM.SO_ORDI ordi ,OMSADM.SO_RET_ORDER retord ,OMSADM.SO_ORD ord
Where retord.SYNCPOSFLAG=0 and ordi.ORDER_ITEM_ID=retord.RETORD_ITEM_ID and
ordi.ORDER_ID = ord.ORDER_ID
fetch first 150 rows only
Optimized Statement:
-------------------
SELECT Q3.ORDER_ITEM_ID AS "ORDERITEMID", Q3.POSORDER_ID AS "POSORDERID",
Q3.SAP_ORDER_ID AS "SAPORDERID", Q3.ORDER_TYPE AS "ORDERTYPE",
Q1.ORDER_TIME AS "ORDERDATE", Q3.STORE_CODE AS "STORECODE",
Q3.SAP_VOUCHER_NO AS "SAPVOUCHERNO"
FROM OMSADM.SO_ORD AS Q1, OMSADM.SO_RET_ORDER AS Q2, OMSADM.SO_ORDI AS Q3
WHERE (Q3.ORDER_ID = Q1.ORDER_ID) AND (Q3.ORDER_ITEM_ID = Q2.RETORD_ITEM_ID)
AND (DECFLOAT(Q2.SYNCPOSFLAG, '............') = 0)
Access Plan:
-----------
Total Cost: 122793
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
150
^NLJOIN
( 2)
122793
84718.2
/---+----\
12850.9 1
TBSCAN IXSCAN
( 3) ( 8)
59450.2 22.7073
23757.8 3
| |
12850.9 2.80705e+06
SORT INDEX: OMSADM
( 4) I_ORD_03
59449.4 Q1
23757.8
|
12850.9
^NLJOIN
( 5)
59441.3
23757.8
/-----+------\
12850.9 1
IXSCAN IXSCAN
( 6) ( 7)
3081.51 22.7033
1909.88 3
| |
343872 3.53634e+06
INDEX: OMSADM INDEX: OMSADM
I_RETORDER_11 I_ORDI_07
Q2 Q3
Extended Diagnostic Information:
--------------------------------
No extended Diagnostic Information for this statement.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 122793
Cumulative CPU Cost: 2.4061e+09
Cumulative I/O Cost: 84718.2
Cumulative Re-Total Cost: 456.36
Cumulative Re-CPU Cost: 1.81155e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 59472.1
Estimated Bufferpool Buffers: 60960.4
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v9.7.0.4 : s110330
HEAPUSE : (Maximum Statement Heap Usage)
128 Pages
PREPTIME: (Statement prepare time)
21 milliseconds
STMTHEAP: (Statement heap size)
8192
Input Streams:
-------------
10) From Operator #2
Estimated number of rows: 150
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.SAPVOUCHERNO+Q4.STORECODE+Q4.ORDERDATE
+Q4.ORDERTYPE+Q4.SAPORDERID+Q4.POSORDERID
+Q4.ORDERITEMID
2) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 122793
Cumulative CPU Cost: 2.4061e+09
Cumulative I/O Cost: 84718.2
Cumulative Re-Total Cost: 456.36
Cumulative Re-CPU Cost: 1.81155e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 59472.1
Estimated Bufferpool Buffers: 60960.4
Arguments:
---------
EARLYOUT: (Early Out flag)
LEFT
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
38549
Predicates:
----------
2) Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 3.56245e-07
Predicate Text:
--------------
(Q3.ORDER_ID = Q1.ORDER_ID)
Input Streams:
-------------
7) From Operator #3
Estimated number of rows: 12850.9
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ID(A)+Q3.SAP_VOUCHER_NO
+Q3.STORE_CODE+Q3.ORDER_TYPE+Q3.SAP_ORDER_ID
+Q3.POSORDER_ID+Q3.ORDER_ITEM_ID
9) From Operator #8
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.ORDER_ID(A)+Q1.ORDER_TIME
Output Streams:
--------------
10) To Operator #1
Estimated number of rows: 150
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.SAPVOUCHERNO+Q4.STORECODE+Q4.ORDERDATE
+Q4.ORDERTYPE+Q4.SAPORDERID+Q4.POSORDERID
+Q4.ORDERITEMID
3) TBSCAN: (Table Scan)
Cumulative Total Cost: 59450.2
Cumulative CPU Cost: 1.47067e+09
Cumulative I/O Cost: 23757.8
Cumulative Re-Total Cost: 316.156
Cumulative Re-CPU Cost: 1.255e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 59449.4
Estimated Bufferpool Buffers: 0
Arguments:
---------
JN INPUT: (Join input leg)
OUTER
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
SCANDIR : (Scan Direction)
FORWARD
SPEED : (Assumed speed of scan, in sharing structures)
SLOW
THROTTLE: (Scan may be throttled, for scan sharing)
FALSE
VISIBLE : (May be included in scan sharing structures)
FALSE
WRAPPING: (Scan may start anywhere and wrap)
FALSE
Input Streams:
-------------
6) From Operator #4
Estimated number of rows: 12850.9
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ID(A)+Q3.SAP_VOUCHER_NO
+Q3.STORE_CODE+Q3.ORDER_TYPE+Q3.SAP_ORDER_ID
+Q3.POSORDER_ID+Q3.ORDER_ITEM_ID
Output Streams:
--------------
7) To Operator #2
Estimated number of rows: 12850.9
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ID(A)+Q3.SAP_VOUCHER_NO
+Q3.STORE_CODE+Q3.ORDER_TYPE+Q3.SAP_ORDER_ID
+Q3.POSORDER_ID+Q3.ORDER_ITEM_ID
4) SORT : (Sort)
Cumulative Total Cost: 59449.4
Cumulative CPU Cost: 1.46745e+09
Cumulative I/O Cost: 23757.8
Cumulative Re-Total Cost: 315.346
Cumulative Re-CPU Cost: 1.25179e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 59449.4
Estimated Bufferpool Buffers: 23758
Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
FALSE
NUMROWS : (Estimated number of rows)
12851
ROWWIDTH: (Estimated width of rows)
92
SORTKEY : (Sort Key column)
1: Q3.ORDER_ID(A)
TEMPSIZE: (Temporary Table Page Size)
8192
UNIQUE : (Uniqueness required flag)
FALSE
Input Streams:
-------------
5) From Operator #5
Estimated number of rows: 12850.9
Number of columns: 8
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RETORD_ITEM_ID+Q3.SAP_VOUCHER_NO
+Q3.STORE_CODE+Q3.ORDER_TYPE+Q3.SAP_ORDER_ID
+Q3.POSORDER_ID+Q3.ORDER_ID+Q3.ORDER_ITEM_ID
Output Streams:
--------------
6) To Operator #3
Estimated number of rows: 12850.9
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ID(A)+Q3.SAP_VOUCHER_NO
+Q3.STORE_CODE+Q3.ORDER_TYPE+Q3.SAP_ORDER_ID
+Q3.POSORDER_ID+Q3.ORDER_ITEM_ID
5) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 59441.3
Cumulative CPU Cost: 1.43539e+09
Cumulative I/O Cost: 23757.8
Cumulative Re-Total Cost: 315.346
Cumulative Re-CPU Cost: 1.25179e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 38.0698
Estimated Bufferpool Buffers: 23758
Arguments:
---------
EARLYOUT: (Early Out flag)
LEFT
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
20019
Predicates:
----------
3) Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 2.82778e-07
Predicate Text:
--------------
(Q3.ORDER_ITEM_ID = Q2.RETORD_ITEM_ID)
Input Streams:
-------------
2) From Operator #6
Estimated number of rows: 12850.9
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RETORD_ITEM_ID(A)+Q2.SYNCPOSFLAG(A)
4) From Operator #7
Estimated number of rows: 1
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ITEM_ID(A)+Q3.SAP_VOUCHER_NO(A)
+Q3.STORE_CODE(A)+Q3.ORDER_TYPE(A)
+Q3.SAP_ORDER_ID(A)+Q3.POSORDER_ID(A)
+Q3.ORDER_ID
Output Streams:
--------------
5) To Operator #4
Estimated number of rows: 12850.9
Number of columns: 8
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RETORD_ITEM_ID+Q3.SAP_VOUCHER_NO
+Q3.STORE_CODE+Q3.ORDER_TYPE+Q3.SAP_ORDER_ID
+Q3.POSORDER_ID+Q3.ORDER_ID+Q3.ORDER_ITEM_ID
6) IXSCAN: (Index Scan)
Cumulative Total Cost: 3081.51
Cumulative CPU Cost: 9.10672e+08
Cumulative I/O Cost: 1909.88
Cumulative Re-Total Cost: 226.185
Cumulative Re-CPU Cost: 8.97854e+08
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 15.3665
Estimated Bufferpool Buffers: 1910
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
JN INPUT: (Join input leg)
OUTER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1608
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Predicates:
----------
4) Sargable Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0373712
Predicate Text:
--------------
(DECFLOAT(Q2.SYNCPOSFLAG, '............') = 0)
Input Streams:
-------------
1) From Object OMSADM.I_RETORDER_11
Estimated number of rows: 343872
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RETORD_ITEM_ID(A)+Q2.SYNCPOSFLAG(A)
+Q2.$RID$
Output Streams:
--------------
2) To Operator #5
Estimated number of rows: 12850.9
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RETORD_ITEM_ID(A)+Q2.SYNCPOSFLAG(A)
7) IXSCAN: (Index Scan)
Cumulative Total Cost: 22.7033
Cumulative CPU Cost: 92402.5
Cumulative I/O Cost: 3
Cumulative Re-Total Cost: 7.56779
Cumulative Re-CPU Cost: 30941.5
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 22.7033
Estimated Bufferpool Buffers: 20022.4
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
JN INPUT: (Join input leg)
INNER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Predicates:
----------
3) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 2.82778e-07
Predicate Text:
--------------
(Q3.ORDER_ITEM_ID = Q2.RETORD_ITEM_ID)
3) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 2.82778e-07
Predicate Text:
--------------
(Q3.ORDER_ITEM_ID = Q2.RETORD_ITEM_ID)
Input Streams:
-------------
3) From Object OMSADM.I_ORDI_07
Estimated number of rows: 3.53634e+06
Number of columns: 8
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ITEM_ID(A)+Q3.SAP_VOUCHER_NO(A)
+Q3.STORE_CODE(A)+Q3.ORDER_TYPE(A)
+Q3.SAP_ORDER_ID(A)+Q3.POSORDER_ID(A)+Q3.$RID$
+Q3.ORDER_ID
Output Streams:
--------------
4) To Operator #5
Estimated number of rows: 1
Number of columns: 7
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ORDER_ITEM_ID(A)+Q3.SAP_VOUCHER_NO(A)
+Q3.STORE_CODE(A)+Q3.ORDER_TYPE(A)
+Q3.SAP_ORDER_ID(A)+Q3.POSORDER_ID(A)
+Q3.ORDER_ID
8) IXSCAN: (Index Scan)
Cumulative Total Cost: 22.7073
Cumulative CPU Cost: 108169
Cumulative I/O Cost: 3
Cumulative Re-Total Cost: 7.57177
Cumulative Re-CPU Cost: 46708.1
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 22.7073
Estimated Bufferpool Buffers: 99718
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
JN INPUT: (Join input leg)
INNER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Predicates:
----------
2) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 3.56245e-07
Predicate Text:
--------------
(Q3.ORDER_ID = Q1.ORDER_ID)
2) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 3.56245e-07
Predicate Text:
--------------
(Q3.ORDER_ID = Q1.ORDER_ID)
Input Streams:
-------------
8) From Object OMSADM.I_ORD_03
Estimated number of rows: 2.80705e+06
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.ORDER_ID(A)+Q1.$RID$+Q1.ORDER_TIME
Output Streams:
--------------
9) To Operator #2
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.ORDER_ID(A)+Q1.ORDER_TIME
Objects Used in Access Plan:
---------------------------
Schema: OMSADM
Name: SO_ORD
Type: Table (reference only)
Schema: OMSADM
Name: SO_ORDI
Type: Table (reference only)
Schema: OMSADM
Name: SO_RET_ORDER
Type: Table (reference only)
Schema: OMSADM
Name: I_ORDI_07
Type: Index
Time of creation: 2013-03-27-09.29.46.614713
Last statistics update: 2013-06-13-00.03.14.550000
Number of columns: 12
Number of rows: 3536339
Width of rows: -1
Number of buffer pool pages: 394639
Distinct row values: Yes
Tablespace name: IDX_HOT_SPACE
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 192
Container extent page count: 32
Index clustering statistic: 0.973099
Index leaf pages: 75900
Index tree levels: 4
Index full key cardinality: 3536344
Index first key cardinality: 3536344
Index first 2 keys cardinality: 3536344
Index first 3 keys cardinality: 3536344
Index first 4 keys cardinality: 3536344
Index sequential pages: 56044
Index page density: 89
Index avg sequential pages: 1245
Index avg gap between sequences:440
Index avg random pages: 14
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 3536344
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: OMSADM
Base Table Name: SO_ORDI
Columns in index:
ORDER_ITEM_ID(A)
SAP_VOUCHER_NO(I)
STORE_CODE(I)
ORDER_TYPE(I)
SAP_ORDER_ID(I)
POSORDER_ID(I)
SALE_ORG(I)
ORDER_ID(I)
B2CORDER_ITEM_ID(I)
B2C_ORDER_ID(I)
ORDITM_CLASS(I)
CREATED_TIME(I)
Schema: OMSADM
Name: I_ORD_03
Type: Index
Time of creation: 2013-03-27-09.29.46.144777
Last statistics update: 2013-06-13-00.00.35.810000
Number of columns: 29
Number of rows: 2807054
Width of rows: -1
Number of buffer pool pages: 113275
Distinct row values: Yes
Tablespace name: IDX_HOT_SPACE
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 192
Container extent page count: 32
Index clustering statistic: 0.983532
Index leaf pages: 99715
Index tree levels: 4
Index full key cardinality: 2807055
Index first key cardinality: 2807055
Index first 2 keys cardinality: 2807055
Index first 3 keys cardinality: 2807055
Index first 4 keys cardinality: 2807055
Index sequential pages: 98834
Index page density: 87
Index avg sequential pages: 19766
Index avg gap between sequences:217
Index avg random pages: 6
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 2807055
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: OMSADM
Base Table Name: SO_ORD
Columns in index:
ORDER_ID(A)
BILL_TYPE(I)
ACTIVE_FLAG(I)
MEN_DBNO(I)
MEM_CARD_ID(I)
MEM_IN_CARD_NO(I)
MEM_OUT_CARD_NO(I)
MEM_TYPE(I)
MEM_NAME(I)
MEM_LEVEL(I)
CLIENT_ID(I)
KZ_GROUP_CARD(I)
TOTAL_ITEM(I)
SERVICE_AMOUNT(I)
IS_INVOICE(I)
TRANSPORT_FEE(I)
USER_ID(I)
ORDER_PAY_AMOUNT(I)
ORDER_SALE_AMOUNT(I)
ORDER_STATUS(I)
REAL_PAY_AMOUNT(I)
VERI_CODE(I)
ORDER_CATEGORY(I)
PAY_ITEM_NUM(I)
ORDER_COMMENTS(I)
ORDER_CHANNEL(I)
ORDER_RES(I)
ORDER_TIME(I)
B2C_ORDER_ID(I)
Schema: OMSADM
Name: I_RETORDER_11
Type: Index
Time of creation: 2013-04-16-00.50.43.794651
Last statistics update: 2013-06-13-00.05.24.150000
Number of columns: 2
Number of rows: 343872
Width of rows: -1
Number of buffer pool pages: 11946
Distinct row values: Yes
Tablespace name: IDX_NORMAL_SPACE
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 192
Container extent page count: 32
Index clustering statistic: 0.994797
Index leaf pages: 1608
Index tree levels: 3
Index full key cardinality: 343872
Index first key cardinality: 343872
Index first 2 keys cardinality: 343872
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 1473
Index page density: 83
Index avg sequential pages: 1473
Index avg gap between sequences:0
Index avg random pages: 127
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 343872
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: OMSADM
Base Table Name: SO_RET_ORDER
Columns in index:
RETORD_ITEM_ID(A)
SYNCPOSFLAG(I)
Base Table For Index Not Already Shown:
---------------------------------------
Schema: OMSADM
Name: SO_ORD
Time of creation: 2013-03-27-09.27.49.168468
Last statistics update: 2013-06-13-00.00.35.813008
Number of data partitions: 1
Number of columns: 38
Number of rows: 2807054
Number of pages: 113275
Number of pages with rows: 113275
Table overflow record count: 27
Indexspace name: IDX_HOT_SPACE
Tablespace name: HOT_SPACE
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Prefetch page count: -1
Container extent page count: 32
Long tablespace name: LOBSPACE
Schema: OMSADM
Name: SO_ORDI
Time of creation: 2013-03-27-09.27.51.983524
Last statistics update: 2013-06-13-00.03.14.552644
Number of data partitions: 1
Number of columns: 100
Number of rows: 3536339
Number of pages: 394639
Number of pages with rows: 394639
Table overflow record count: 2238
Indexspace name: IDX_HOT_SPACE
Tablespace name: HOT_SPACE
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Prefetch page count: -1
Container extent page count: 32
Long tablespace name: LOBSPACE
Schema: OMSADM
Name: SO_RET_ORDER
Time of creation: 2013-03-27-09.28.43.603037
Last statistics update: 2013-06-13-00.05.24.156095
Number of data partitions: 1
Number of columns: 33
Number of rows: 343872
Number of pages: 11946
Number of pages with rows: 11946
Table overflow record count: 472
Indexspace name: IDX_NORMAL_SPACE
Tablespace name: NORMAL_SPACE
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Prefetch page count: -1
Container extent page count: 32
Long tablespace name: LOBSPACE
浏览652