对的,建议先看一下书。平时就是要多看计划。看多了慢慢就熟了。一般计划里都有一个执行树。一般是从下往上,从做左往右看的。这里举个简单的例子:
select a.*, b.*from test2 a, test3 b where a.c3 > b.c1 and&nbs...
显示全部对的,建议先看一下书。
平时就是要多看计划。看多了慢慢就熟了。一般计划里都有一个执行树。一般是从下往上,从做左往右看的。
这里举个简单的例子:
select a.*, b.*from test2 a, test3 b where a.c3 > b.c1 and a.c3 < b.c2;
Total Cost: 949.849
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
139972
NLJOIN
( 2)
949.849
499
/-----+------
80000 1.74965
TBSCAN TBSCAN
( 3) ( 4)
482.414 6.7847
498 1
| |
80000 7
TABLE: DB2IODSH TABLE: DB2IODSH
TEST2 TEST3
Q2 Q1
是说两个表关联,采用nl的join方式,左边的表为驱动表也就是外表,右边的表为内表。
上面的数据说明如下:
139972 <---- 优化器估计执行nljoin后得到的记录数
NLJOIN <---- 操作符
( 2) <---- 步骤,可以拿这个步骤去看计划里的详细说明
949.849 <----- 总成本 包括cpu成本和IO成本。
499 <------ IO 成本
2)步骤的详细描述如下:
2) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 949.849
Cumulative CPU Cost: 1.8128e+09
Cumulative I/O Cost: 499
Cumulative Re-Total Cost: 498.615
Cumulative Re-CPU Cost: 1.80963e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 13.5613
Estimated Bufferpool Buffers: 499
Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
Predicates:
----------
2) Predicate used in Join,
Comparison Operator: Less Than (<)
Subquery Input Required: No
Filter Factor: 0.49995
Predicate Text:
--------------
(Q2.C3 < Q1.C2)
3) Predicate used in Join,
Comparison Operator: Less Than (<)
Subquery Input Required: No
Filter Factor: 0.49995
Predicate Text:
--------------
(Q1.C1 < Q2.C3)
Input Streams:
-------------
2) From Operator #3
Estimated number of rows: 80000
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.C2+Q2.C1+Q2.C3
4) From Operator #4
Estimated number of rows: 1.74965
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.C2+Q1.C1
Output Streams:
--------------
5) To Operator #1
Estimated number of rows: 139972
Number of columns: 5
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.C2+Q3.C1+Q3.C3+Q3.C2+Q3.C1
收起