Access Path: index (IndexOnly)只访问索引.(因为只用到了索引数据RN) Cost: 2.01
可以看到只访问索引的代价最小.
对于T2表有如下访问方式:(最后会走index (RangeScan))
Access Path: TableScan Cost: 6.37
Access Path: index (RangeScan) Cost: 3.01
然后再是对关联顺序的考虑:
T1关联T2
NL Join(nested loops join) Cost: 2051.15
SM Join(Sort merge join) SM cost: 6.02
HA Join (Hash join) HA cost: 5.52
T2关联T1
NL Join(nested loops join) Cost: 475.12
SM Join(Sort merge join) SM cost: 6.02
HA Join (Hash join) HA cost: 5.52
T1关联T2的CPU代价更小,最后会走T1关联T2.
SINGLE TABLE ACCESS PATH
Table: T1 Alias: T1
Card: Original: 51060 Rounded: 95 Computed: 95.02 Non Adjusted: 95.02
Access Path: TableScan
Cost: 22.86 Resp: 22.86 Degree: 0
Cost_io: 21.00 Cost_cpu: 10824444
Resp_io: 21.00 Resp_cpu: 10824444
Access Path: index (index (FFS))
Index: IND_T1
resc_io: 26.00 resc_cpu: 9484923
ix_sel: 0.0000e+000 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 27.63 Resp: 27.63 Degree: 1
Cost_io: 26.00 Cost_cpu: 9484923
Resp_io: 26.00 Resp_cpu: 9484923
Access Path: index (IndexOnly)
Index: IND_T1
resc_io: 2.00 resc_cpu: 33443
ix_sel: 0.0018609 ix_sel_with_filters: 0.0018609
Cost: 2.01 Resp: 2.01 Degree: 1
Best:: AccessPath: IndexRange Index: IND_T1
Cost: 2.01 Degree: 1 Resp: 2.01 Card: 95.02 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
Table: T2 Alias: T2
Card: Original: 9999 Rounded: 99 Computed: 99.01 Non Adjusted: 99.01
Access Path: TableScan
Cost: 6.37 Resp: 6.37 Degree: 0
Cost_io: 6.00 Cost_cpu: 2151330
Resp_io: 6.00 Resp_cpu: 2151330
Access Path: index (RangeScan)
resc_io: 3.00 resc_cpu: 58364
ix_sel: 0.009902 ix_sel_with_filters: 0.009902
Cost: 3.01 Resp: 3.01 Degree: 1
Best:: AccessPath: IndexRange Index: IND_T2
Cost: 3.01 Degree: 1 Resp: 3.01 Card: 99.01 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]: T1[T1]#0 T2[T2]#1
***************
Now joining: T2[T2]#1
***************
NL Join
Outer table: Card: 95.02 Cost: 2.01 Resp: 2.01 Degree: 1 Bytes: 4
Inner table: T2 Alias: T2
Access Path: TableScan
NL Join: Cost: 475.12 Resp: 475.12 Degree: 0
Cost_io: 440.00 Cost_cpu: 204409816
Resp_io: 440.00 Resp_cpu: 204409816
kkofmx: index filter:"T2"."RN"<100 AND "T1"."RN"<100
Access Path: index (AllEqJoinGuess)
Index: IND_T2
resc_io: 2.00 resc_cpu: 15463
ix_sel: 1.0001e-004 ix_sel_with_filters: 9.9030e-007
NL Join (ordered): Cost: 115.77 Resp: 115.77 Degree: 1
Cost_io: 115.60 Cost_cpu: 950127
Resp_io: 115.60 Resp_cpu: 950127
Best NL cost: 115.77
resc: 115.77 resc_io: 115.60 resc_cpu: 950127
resp: 115.77 resp_io: 115.60 resp_cpu: 950127
Join Card: 94.08 = outer (95.02) * inner (99.01) * sel (0.01)
Join Card - Rounded: 94 Computed: 94.08
SM Join
Outer table:
resc: 2.01 card 95.02 bytes: 4 deg: 1 resp: 2.01
Inner table: T2 Alias: T2
resc: 3.01 card: 99.01 bytes: 6 deg: 1 resp: 3.01
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 R