***********************
第二部分T1关联T
[sql] view plaincopy
***********************
Join order[2]: T1[T1]#1 T[T]#0
***************
Now joining: T[T]#0
***************
NL Join
Outer table: Card: 99.01 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 6
Inner table: T Alias: T
Access Path: TableScan
NL Join: Cost: 2139.43 Resp: 2139.43 Degree: 1
Cost_io: 2069.00 Cost_cpu: 1057393301
Resp_io: 2069.00 Resp_cpu: 1057393301
Access Path: index (index (FFS))
Index: IND_T
resc_io: 30.34 resc_cpu: 9409631
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Inner table: T Alias: T
Access Path: index (FFS)
NL Join: Cost: 3069.06 Resp: 3069.06 Degree: 1
Cost_io: 3007.00 Cost_cpu: 931611861
Resp_io: 3007.00 Resp_cpu: 931611861
kkofmx: index filter:"T"."X"<100
Access Path: index (AllEqJoinGuess)
Index: IND_T
resc_io: 1.00 resc_cpu: 8171
ix_sel: 1.9740e-05 ix_sel_with_filters: 3.1964e-08
NL Join (ordered): Cost: 102.06 Resp: 102.06 Degree: 1
Cost_io: 102.00 Cost_cpu: 872287
Resp_io: 102.00 Resp_cpu: 872287
Best NL cost: 102.06 ------------>ENSTED LOOPS JOIN 方式最好的代价是102.06
resc: 102.06 resc_io: 102.00 resc_cpu: 872287
resp: 102.06 resp_io: 102.00 resp_cpu: 872287
Join Card: 81.22 = outer (99.01) * inner (82.03) * sel (0.01)
SM Join
Outer table:
resc: 3.00 card 99.01 bytes: 6 deg: 1 resp: 3.00
Inner table: T Alias: T
resc: 2.00 card: 82.03 bytes: 4 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 1435 Area size: 1048576 Max Area size: 251238400
Degree: 1
Blocks to Sort: 1 Row size: 15 Total Rows: 82
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 15035869
Total Temp space used: 0
SM join: Resc: 6.01 Resp: 6.01 [multiMatchCost=0.00]
SM cost: 6.01 ------------>SORT MERGE JOIN(SM Join) 方式最好的代价是6.01
resc: 6.01 resc_io: 5.00 resc_cpu: 15125076
resp: 6.01 resp_io: 5.00 resp_cpu: 15125076
HA Join
Outer table:
resc: 3.00 card 99.01 bytes: 6 deg: 1 resp: 3.00
Inner table: T Alias: T
resc: 2.00 card: 82.03 bytes: 4 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 256 (max=61338) Hash join: Resc: 5.51 Resp: 5.51 [multiMatchCost=0.00]
HA cost: 5.51 ------------>HASH JOIN(HA Join) 方式最好的代价是5.51
resc: 5.51 resc_io: 5.00 resc_cpu: 7618448 ------------>并行执行cpu代价7618448 高于 上一种表连接方式的（7617598）
resp: 5.51 resp_io: 5.00 resp_cpu: 7618448
Join order aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
(newjo-save) [1 0 ]
Final - All Rows Plan: Best join order: 1 ------------>第一种关联顺序
Cost: 5.5074 Degree: 1 Card: 81.0000 Bytes: 810
Resc: 5.5074 Resc_io: 5.00