SORT MERGE JOIN(SM Join)
HASH JOIN(HA Join)
T1关联T
ENSTED LOOPS JOIN(NL Join)
SORT MERGE JOIN(SM Join)
HASH JOIN(HA Join)
以下为这一部分的trace信息：
第一部分T关联T1
[sql] view plaincopy
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]: T[T]#0 T1[T1]#1
***************
Now joining: T1[T1]#1
***************
NL Join
Outer table: Card: 82.03 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 4
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 392.60 Resp: 392.60 Degree: 1
Cost_io: 381.00 Cost_cpu: 174104090
Resp_io: 381.00 Resp_cpu: 174104090
kkofmx: index filter:"T1"."X"<100 AND "T"."X"<100
Access Path: index (AllEqJoinGuess)
Index: IND_T1
resc_io: 2.00 resc_cpu: 15463
ix_sel: 1.0001e-04 ix_sel_with_filters: 9.9030e-07
NL Join (ordered): Cost: 100.24 Resp: 100.24 Degree: 1
Cost_io: 100.19 Cost_cpu: 823013
Best NL cost: 100.24 ------------>ENSTED LOOPS JOIN 方式最好的代价是100.24
resc: 100.24 resc_io: 100.19 resc_cpu: 823013
resp: 100.24 resp_io: 100.19 resp_cpu: 823013
Join Card: 81.22 = outer (82.03) * inner (99.01) * sel (0.01)
Join Card - Rounded: 81 Computed: 81.22
SM Join
Outer table:
resc: 2.00 card 82.03 bytes: 4 deg: 1 resp: 2.00
Inner table: T1 Alias: T1
resc: 3.00 card: 99.01 bytes: 6 deg: 1 resp: 3.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: 17 Total Rows: 99
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 15041951
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: 15131158
resp: 6.01 resp_io: 5.00 resp_cpu: 15131158
HA Join
Outer table:
resc: 2.00 card 82.03 bytes: 4 deg: 1 resp: 2.00
Inner table: T1 Alias: T1
resc: 3.00 card: 99.01 bytes: 6 deg: 1 resp: 3.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: 7617598 ------------>并行执行cpu代价7617598
resp: 5.51 resp_io: 5.00 resp_cpu: 7617598
Best:: JoinMethod: Hash ------------>这里计算出最好的代价表连接是Hash
Cost: 5.51 Degree: 1 Resp: 5.51 Card: 81.22 Bytes: 10
***********************
Best so far: Table#: 0 cost: 2.0021 card: 82.0291 bytes: 328
Table#: 1 cost: 5.5074 card: 81.2170 bytes: 810