|
|
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 09.07.5
SOURCE_NAME: SQLC2H22
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2012-03-28-17.56.34.357001
EXPLAIN_REQUESTER: ADMINISTRATOR
Database Context:
----------------
Parallelism: None
CPU Speed: 1.417033e-007
Comm Speed: 0
Buffer Pool size: 3429
Sort Heap size: 52
Database Heap size: 1223
Lock List size: 4120
Maximum Lock List: 98
Average Applications: 1
Locks Available: 129203
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 203 ----------------
QUERYNO: 1
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
select *
from test.a_t070701 t1 , test.a_t070702 t2
where t1.a=t2.a and t1.b=t2.b and t1.a=1
Optimized Statement:
-------------------
SELECT 1 AS "A", Q2.B AS "B", 1 AS "A", Q1.B AS "B"
FROM TEST.A_T070702 AS Q1, TEST.A_T070701 AS Q2
WHERE (Q2.A = 1) AND (Q2.B = Q1.B) AND (1 = Q1.A)
Access Plan:
-----------
Total Cost: 0.0118336
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1
^NLJOIN
( 2)
0.0118336
0
/-----+------\
1 1
IXSCAN IXSCAN
( 3) ( 4)
0.00600723 0.00582641
0 0
| |
2 2
INDEX: TEST INDEX: TEST
UI_070702 UI_070701
Q1 Q2
Extended Diagnostic Information:
--------------------------------
Diagnostic Identifier: 1
Diagnostic Details: EXP0147W The following statistical view was used
by the optimizer to estimate cardinalities: "TEST
"."V_T1".
Diagnostic Identifier: 2
Diagnostic Details: EXP0148W The following MQT or statistical view was
considered in query matching: "TEST "."V_T1".
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 0.0118336
Cumulative CPU Cost: 83510
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.00178376
Cumulative Re-CPU Cost: 12588
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.0115898
Estimated Bufferpool Buffers: 1
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v9.7.500.702 : s111017
HEAPUSE : (Maximum Statement Heap Usage)
96 Pages
PREPTIME: (Statement prepare time)
59 milliseconds
STMTHEAP: (Statement heap size)
8192
Input Streams:
-------------
5) From Operator #2
Estimated number of rows: 1
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.B+Q3.A+Q3.B+Q3.A
2) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 0.0118336
Cumulative CPU Cost: 83510
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.00178376
Cumulative Re-CPU Cost: 12588
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.0115898
Estimated Bufferpool Buffers: 1
Arguments:
---------
EARLYOUT: (Early Out flag)
LEFT
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
Predicates:
----------
2) Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1
Predicate Text:
--------------
(Q1.A = Q2.A)
4) Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.5
Predicate Text:
--------------
(Q2.B = Q1.B)
Input Streams:
-------------
2) From Operator #3
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.B(A)+Q1.A
4) From Operator #4
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.B(A)+Q2.A
Output Streams:
--------------
5) To Operator #1
Estimated number of rows: 1
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.B+Q3.A+Q3.B+Q3.A
3) IXSCAN: (Index Scan)
Cumulative Total Cost: 0.00600723
Cumulative CPU Cost: 42393
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.000982287
Cumulative Re-CPU Cost: 6932
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.00576336
Estimated Bufferpool Buffers: 1
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
JN INPUT: (Join input leg)
OUTER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
ALL
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:
----------
5) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.5
Predicate Text:
--------------
(1 = Q1.A)
5) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.5
Predicate Text:
--------------
(1 = Q1.A)
Input Streams:
-------------
1) From Object TEST.UI_070702
Estimated number of rows: 2
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.B(A)+Q1.$RID$+Q1.A
Output Streams:
--------------
2) To Operator #2
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.B(A)+Q1.A
4) IXSCAN: (Index Scan)
Cumulative Total Cost: 0.00582641
Cumulative CPU Cost: 41117
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.000801474
Cumulative Re-CPU Cost: 5656
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.00582641
Estimated Bufferpool Buffers: 1
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
JN INPUT: (Join input leg)
INNER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
ALL
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: 1
Predicate Text:
--------------
(Q1.A = Q2.A)
2) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1
Predicate Text:
--------------
(Q1.A = Q2.A)
3) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.5
Predicate Text:
--------------
(Q2.A = 1)
3) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.5
Predicate Text:
--------------
(Q2.A = 1)
4) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.5
Predicate Text:
--------------
(Q2.B = Q1.B)
4) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.5
Predicate Text:
--------------
(Q2.B = Q1.B)
Input Streams:
-------------
3) From Object TEST.UI_070701
Estimated number of rows: 2
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.B(A)+Q2.$RID$+Q2.A
Output Streams:
--------------
4) To Operator #2
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.B(A)+Q2.A
Objects Used in Access Plan:
---------------------------
Schema: TEST
Name: A_T070701
Type: Table (reference only)
Schema: TEST
Name: A_T070702
Type: Table (reference only)
Schema: TEST
Name: V_T1
Type: View (reference only)
Schema: TEST
Name: UI_070701
Type: Index
Time of creation: 2012-03-26-12.24.09.973001
Last statistics update: 2012-03-26-12.24.11.770000
Number of columns: 2
Number of rows: 2
Width of rows: -1
Number of buffer pool pages: 1
Distinct row values: Yes
Tablespace name: USERSPACE1
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 32
Index clustering statistic: 100.000000
Index leaf pages: 1
Index tree levels: 1
Index full key cardinality: 2
Index first key cardinality: 2
Index first 2 keys cardinality: 2
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 0
Index page density: 0
Index avg sequential pages: 0
Index avg gap between sequences:0
Index avg random pages: 1
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 2
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: TEST
Base Table Name: A_T070701
Columns in index:
A(A)
B(A)
Schema: TEST
Name: UI_070702
Type: Index
Time of creation: 2012-03-26-12.24.10.645002
Last statistics update: 2012-03-26-12.24.12.000000
Number of columns: 2
Number of rows: 2
Width of rows: -1
Number of buffer pool pages: 1
Distinct row values: Yes
Tablespace name: USERSPACE1
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 32
Index clustering statistic: 100.000000
Index leaf pages: 1
Index tree levels: 1
Index full key cardinality: 2
Index first key cardinality: 2
Index first 2 keys cardinality: 2
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 0
Index page density: 0
Index avg sequential pages: 0
Index avg gap between sequences:0
Index avg random pages: 1
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 2
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: TEST
Base Table Name: A_T070702
Columns in index:
A(A)
B(A)
Base Table For Index Not Already Shown:
---------------------------------------
Schema: TEST
Name: A_T070701
Time of creation: 2012-03-26-12.24.09.098001
Last statistics update: 2012-03-26-12.24.11.770000
Number of data partitions: 1
Number of columns: 2
Number of rows: 2
Number of pages: 1
Number of pages with rows: 1
Table overflow record count: 0
Indexspace name: USERSPACE1
Tablespace name: USERSPACE1
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Prefetch page count: -1
Container extent page count: 32
Long tablespace name: USERSPACE1
Schema: TEST
Name: A_T070702
Time of creation: 2012-03-26-12.24.09.723002
Last statistics update: 2012-03-26-12.24.12.004001
Number of data partitions: 1
Number of columns: 2
Number of rows: 2
Number of pages: 1
Number of pages with rows: 1
Table overflow record count: 0
Indexspace name: USERSPACE1
Tablespace name: USERSPACE1
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Prefetch page count: -1
Container extent page count: 32
Long tablespace name: USERSPACE1
|
|
楼主: amtom
IP卡
狗仔卡
楼主
显身卡
发表于 2012-3-28 17:57
