first_rows_n和all_rows都是oracle optimizer_mode的选项,他们有什么区别呢,会对优化器产生怎么样的影响呢?让我们一起来解开迷题.
all_rows模式:
all_rows是oracle优化器默认的模式,它将选择一种在最短时间内返回所有数据的执行计划,它将基于整体成本的考虑.
first_rows_n模式:
first_rows_n是从9i开始引入来代替以前的first_rows模式,虽然first_rows模式仍然存在,但是oracle已经不推荐使用.因为它基本上是基于oracle可执行文件硬编码的很多规则实现,比如它会尝试彻底去避免hash join或者merge join除非nest loop的非驱动表会进行全表扫描,first_rows也会偏向于使用索引而不是全表扫描,这在某些情况下也会带来反面的效果.所以oracle引入first_rows_n来代替first_rows,first_rows_n是根据成本而不是基于硬编码的规则来选择执行计划.n可以是1,10,100,1000或者直接用first_rows(n) hint指定任意正数.这里的n是我们想获取结果集的前n条记录,举个例子,如果n为1,那么oracle会选择一个最快速度返回结果集第一条记录的执行计划而不管是否它获取结果集的所有记录的执行成本是不是最优.这种需求在很多分页语句的需求中会碰到.
那么oracle是怎么判断first_rows_n的成本并作出选择的呢,10053跟踪事件能给我们答案
create table t as select * from dba_objects;
create table t1 as select * from t;
create index ind_object_id on t(object_id) compute statistics;
create index ind_t1_object_id on t1(object_id) compute statistics;
analyze table t compute statistics for table for all columns;
analyze table t1 compute statistics for table for all columns;
准备好测试表和索引后来看看测试脚本
all_rows模式:
alter session set events’10053 trace name context forever,level 1′;
alter session set optimizer_mode=all_rows;
select t.owner from t,t1 where t.object_id = t1.object_id;
alter session set events’10053 trace name context off’;
first_rows_1模式:
alter session set events’10053 trace name context forever,level 1′;
alter session set optimizer_mode=first_rows_1;
select t.owner from t,t1 where t.object_id = t1.object_id;
alter session set events’10053 trace name context off’;
first_rows_10模式:
alter session set events’10053 trace name context forever,level 1′;
alter session set optimizer_mode=first_rows_10;
select t.owner from t,t1 where t.object_id = t1.object_id;
alter session set events’10053 trace name context off’;
first_rows_100模式:
alter session set events’10053 trace name context forever,level 1′;
alter session set optimizer_mode=first_rows_100;
select t.owner from t,t1 where t.object_id = t1.object_id;
alter session set events’10053 trace name context off’;
由于篇幅太长,所以把10053的trace文件简化了一下,只留下join这一部分的内容,并把merge join的部分去除了
测试环境是10g r2
all_rows:
**************************
GENERAL PLANS
**************************
Considering cardinality-based initial join order.
***********************
Join order[1]: T[T]#0 T1[T1]#1
***************
Now joining: T1[T1]#1
***************
NL Join
Outer table: Card: 51986.00 Cost: 164.59 Resp: 164.59 Degree: 1 Bytes: 9
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 8493121.71 Resp: 8493121.71 Degree: 0
Cost_io: 8358538.00 Cost_cpu: 839658589661
Resp_io: 8358538.00 Resp_cpu: 839658589661
Access Path: index (index (FFS))
Index: IND_T1_OBJECT_ID
resc_io: 25.16 resc_cpu: 7056806
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Inner table: T1 Alias: T1
Access Path: index (FFS)
NL Join: Cost: 1366740.53 Resp: 1366740.53 Degree: 0
Cost_io: 1307937.00 Cost_cpu: 366871247240
Resp_io: 1307937.00 Resp_cpu: 366871247240
Access Path: index (AllEqJoinGuess)
Index: IND_T1_OBJECT_ID
resc_io: 1.00 resc_cpu: 8371
ix_sel: 1.9239e-05 ix_sel_with_filters: 1.9239e-05
NL Join: Cost: 52220.34 Resp: 52220.34 Degree: 1
Cost_io: 52148.00 Cost_cpu: 451348998
Resp_io: 52148.00 Resp_cpu: 451348998
Best NL cost: 52220.34
resc: 52220.34 resc_io: 52148.00 resc_cpu: 451348998
resp: 52220.34 resp_io: 52148.00 resp_cpu: 451348998
Join Card: 51982.00 = outer (51986.00) * inner (51986.00) * sel (1.9234e-05)
Join Card - Rounded: 51982 Computed: 51982.00
HA Join
Outer table:
resc: 164.59 card 51986.00 bytes: 9 deg: 1 resp: 164.59
Inner table: T1 Alias: T1
resc: 28.13 card: 51986.00 bytes: 4 deg: 1 resp: 28.13
using dmeth: 2 #groups: 1
Cost per ptn: 2.58 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 195.30 Resp: 195.30 [multiMatchCost=0.00]
HA Join (swap)
Outer table:
resc: 28.13 card 51986.00 bytes: 4 deg: 1 resp: 28.13
Inner table: T Alias: T
resc: 164.59 card: 51986.00 bytes: 9 deg: 1 resp: 164.59
using dmeth: 2 #groups: 1
Cost per ptn: 2.58 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 195.30 Resp: 195.30 [multiMatchCost=0.00]
HA cost: 195.30
resc: 195.30 resc_io: 189.00 resc_cpu: 39324090
resp: 195.30 resp_io: 189.00 resp_cpu: 39324090
Best:: JoinMethod: Hash
Cost: 195.30 Degree: 1 Resp: 195.30 Card: 51982.00 Bytes: 13
***********************
Best so far: Table#: 0 cost: 164.5888 card: 51986.0000 bytes: 467874
Table#: 1 cost: 195.3030 card: 51982.0000 bytes: 675766
计算第一种join顺序的成本值,T做驱动表,T1做内部表,
Best:: JoinMethod: Hash
Cost: 195.30 Degree: 1 Resp: 195.30 Card: 51982.00 Bytes: 13
在这里可以看到最优join方式是hash join,
最终的成本是195.30,返回结果集记录数是51982
***********************
Join order[2]: T1[T1]#1 T[T]#0
***************
Now joining: T[T]#0
***************
NL Join
Outer table: Card: 51986.00 Cost: 28.13 Resp: 28.13 Degree: 1 Bytes: 4
Inner table: T Alias: T
Access Path: TableScan
NL Join: Cost: 8492985.25 Resp: 8492985.25 Degree: 0
Cost_io: 8358403.00 Cost_cpu: 839649495148
Resp_io: 8358403.00 Resp_cpu: 839649495148
Access Path: index (AllEqJoinGuess)
Index: IND_OBJECT_ID
resc_io: 2.00 resc_cpu: 15913
ix_sel: 1.9239e-05 ix_sel_with_filters: 1.9239e-05
NL Join (ordered): Cost: 104132.73 Resp: 104132.73 Degree: 1
Cost_io: 103999.00 Cost_cpu: 834303785
Resp_io: 103999.00 Resp_cpu: 834303785
Best NL cost: 104132.73
resc: 104132.73 resc_io: 103999.00 resc_cpu: 834303785
resp: 104132.73 resp_io: 103999.00 resp_cpu: 834303785
Join Card: 51982.00 = outer (51986.00) * inner (51986.00) * sel (1.9234e-05)
Join Card - Rounded: 51982 Computed: 51982.00
HA Join
Outer table:
resc: 28.13 card 51986.00 bytes: 4 deg: 1 resp: 28.13
Inner table: T Alias: T
resc: 164.59 card: 51986.00 bytes: 9 deg: 1 resp: 164.59
using dmeth: 2 #groups: 1
Cost per ptn: 2.58 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 195.30 Resp: 195.30 [multiMatchCost=0.00]
HA cost: 195.30
resc: 195.30 resc_io: 189.00 resc_cpu: 39324090
resp: 195.30 resp_io: 189.00 resp_cpu: 39324090
Join order aborted: cost > best plan cost
计算第二种join顺序的成本值,T1做驱动表,T做内部表,
Join order aborted: cost > best plan cost
第二种join顺序被放弃,因为成本大于已经第一种join顺序的最优成本
***********************
(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: 195.3030 Degree: 1 Card: 51982.0000 Bytes: 675766
Resc: 195.3030 Resc_io: 189.0000 Resc_cpu: 39324090
Resp: 195.3030 Resp_io: 189.0000 Resc_cpu: 39324090
在All Rows模式下最终优化器选择了Best join order: 1,Cost: 195.3030,
尝试了2种join 顺序(Number of join permutations tried: 2)
first_rows_1模式:
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]: T[T]#0 T1[T1]#1
***************
Now joining: T1[T1]#1
***************
NL Join
Outer table: Card: 51986.00 Cost: 164.59 Resp: 164.59 Degree: 1 Bytes: 9
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 8493121.71 Resp: 8493121.71 Degree: 0
Cost_io: 8358538.00 Cost_cpu: 839658589661
Resp_io: 8358538.00 Resp_cpu: 839658589661
Access Path: index (index (FFS))
Index: IND_T1_OBJECT_ID
resc_io: 25.16 resc_cpu: 7056806
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Inner table: T1 Alias: T1
Access Path: index (FFS)
NL Join: Cost: 1366740.53 Resp: 1366740.53 Degree: 0
Cost_io: 1307937.00 Cost_cpu: 366871247240
Resp_io: 1307937.00 Resp_cpu: 366871247240
Access Path: index (AllEqJoinGuess)
Index: IND_T1_OBJECT_ID
resc_io: 1.00 resc_cpu: 8371
ix_sel: 1.9239e-05 ix_sel_with_filters: 1.9239e-05
NL Join: Cost: 52220.34 Resp: 52220.34 Degree: 1
Cost_io: 52148.00 Cost_cpu: 451348998
Resp_io: 52148.00 Resp_cpu: 451348998
Best NL cost: 52220.34
resc: 52220.34 resc_io: 52148.00 resc_cpu: 451348998
resp: 52220.34 resp_io: 52148.00 resp_cpu: 451348998
Join Card: 51982.00 = outer (51986.00) * inner (51986.00) * sel (1.9234e-05)
Join Card - Rounded: 51982 Computed: 51982.00
HA Join
Outer table:
resc: 164.59 card 51986.00 bytes: 9 deg: 1 resp: 164.59
Inner table: T1 Alias: T1
resc: 28.13 card: 51986.00 bytes: 4 deg: 1 resp: 28.13
using dmeth: 2 #groups: 1
Cost per ptn: 2.58 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 195.30 Resp: 195.30 [multiMatchCost=0.00]
HA Join (swap)
Outer table:
resc: 28.13 card 51986.00 bytes: 4 deg: 1 resp: 28.13
Inner table: T Alias: T
resc: 164.59 card: 51986.00 bytes: 9 deg: 1 resp: 164.59
using dmeth: 2 #groups: 1
Cost per ptn: 2.58 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 195.30 Resp: 195.30 [multiMatchCost=0.00]
HA cost: 195.30
resc: 195.30 resc_io: 189.00 resc_cpu: 39324090
resp: 195.30 resp_io: 189.00 resp_cpu: 39324090
Best:: JoinMethod: Hash
Cost: 195.30 Degree: 1 Resp: 195.30 Card: 51982.00 Bytes: 13
***********************
Best so far: Table#: 0 cost: 164.5888 card: 51986.0000 bytes: 467874
Table#: 1 cost: 195.3030 card: 51982.0000 bytes: 675766
*********************************
Number of join permutations tried: 1
*********************************
(newjo-save) [1 0 ]
Final - All Rows Plan: Best join order: 1
Cost: 195.3030 Degree: 1 Card: 51982.0000 Bytes: 675766
Resc: 195.3030 Resc_io: 189.0000 Resc_cpu: 39324090
Resp: 195.3030 Resp_io: 189.0000 Resc_cpu: 39324090
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT /*+ NO_STAR_TRANSFORMATION NO_EXPAND */ “T”.”OWNER” “OWNER” FROM “TEST”.”T” “T”,”TEST”.”T1″ “T1″ WHERE “T”.”OBJECT_ID”=”T1″.”OBJECT_ID”
kkoqbc-end
: call(in-use=32712, alloc=49112), compile(in-use=35284, alloc=36696)
First K Rows: K/N ratio = 0.000019237428341, qbc=0×905f2620
First K Rows: Setup end
***********************
在FIRST_ROWS_1模式下,oracle会先按ALL_ROWS模式计算一种join顺序(Number of join permutations tried: 1)
,得到返回结果集的大小,
从而计算出FIRST_ROWS_1中的1条记录和所有结果集记录的一个比率值,
Join Card - Rounded: 51982 Computed: 51982.00
First K Rows: K/N ratio = 1/51982=0.000019237428341
通过这个K/N ratio,oracle会重新计算join cost
SINGLE TABLE ACCESS PATH (First K Rows)
Table: T Alias: T
Card: Original: 2 Rounded: 2 Computed: 2.00 Non Adjusted: 2.00
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 7541
Resp_io: 2.00 Resp_cpu: 7541
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 2.00 Bytes: 9
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
Table: T1 Alias: T1
Card: Original: 25996 Rounded: 25996 Computed: 25996.00 Non Adjusted: 25996.00
Access Path: TableScan
Cost: 83.30 Resp: 83.30 Degree: 0
Cost_io: 82.00 Cost_cpu: 8079850
Resp_io: 82.00 Resp_cpu: 8079850
Access Path: index (index (FFS))
Index: IND_T1_OBJECT_ID
resc_io: 14.00 resc_cpu: 3532204
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 14.57 Resp: 14.57 Degree: 1
Cost_io: 14.00 Cost_cpu: 3532204
Resp_io: 14.00 Resp_cpu: 3532204
Access Path: index (FullScan)
Index: IND_T1_OBJECT_ID
resc_io: 59.00 resc_cpu: 5618765
ix_sel: 1 ix_sel_with_filters: 1
Cost: 59.90 Resp: 59.90 Degree: 1
Best:: AccessPath: IndexFFS Index: IND_T1_OBJECT_ID
Cost: 14.57 Degree: 1 Resp: 14.57 Card: 25996.00 Bytes: 4
First K Rows: unchanged join prefix len = 1
***********************
Join order[1]: T[T]#0 T1[T1]#1
***************
Now joining: T1[T1]#1
***************
NL Join
Outer table: Card: 2.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 9
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 166.59 Resp: 166.59 Degree: 0
Cost_io: 164.00 Cost_cpu: 16167241
Resp_io: 164.00 Resp_cpu: 16167241
Access Path: index (index (FFS))
Index: IND_T1_OBJECT_ID
resc_io: 13.50 resc_cpu: 3532204
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Inner table: T1 Alias: T1
Access Path: index (FFS)
NL Join: Cost: 30.13 Resp: 30.13 Degree: 0
Cost_io: 29.00 Cost_cpu: 7071948
Resp_io: 29.00 Resp_cpu: 7071948
Access Path: index (AllEqJoinGuess)
Index: IND_T1_OBJECT_ID
resc_io: 1.00 resc_cpu: 8371
ix_sel: 3.8475e-05 ix_sel_with_filters: 3.8475e-05
NL Join: Cost: 4.00 Resp: 4.00 Degree: 1
Cost_io: 4.00 Cost_cpu: 24284
Resp_io: 4.00 Resp_cpu: 24284
Best NL cost: 4.00
resc: 4.00 resc_io: 4.00 resc_cpu: 24284
resp: 4.00 resp_io: 4.00 resp_cpu: 24284
Join Card: 1.00 = outer (2.00) * inner (25996.00) * sel (1.9234e-05)
Join Card - Rounded: 1 Computed: 1.00
HA Join
Outer table:
resc: 164.59 card 51986.00 bytes: 9 deg: 1 resp: 164.59
Inner table: T1 Alias: T1
resc: 14.57 card: 25996.00 bytes: 4 deg: 1 resp: 14.57
using dmeth: 2 #groups: 1
Cost per ptn: 2.17 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 181.32 Resp: 181.32 [multiMatchCost=0.00]
HA Join (swap)
Outer table:
resc: 28.13 card 51986.00 bytes: 4 deg: 1 resp: 28.13
Inner table: T Alias: T
resc: 2.00 card: 2.00 bytes: 9 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
Cost per ptn: 1.75 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 31.88 Resp: 31.88 [multiMatchCost=0.00]
HA cost: 31.88
resc: 31.88 resc_io: 29.00 resc_cpu: 17981913
resp: 31.88 resp_io: 29.00 resp_cpu: 17981913
Best:: JoinMethod: NestedLoop
Cost: 4.00 Degree: 1 Resp: 4.00 Card: 1.00 Bytes: 13
***********************
Best so far: Table#: 0 cost: 2.0012 card: 2.0000 bytes: 18
Table#: 1 cost: 4.0039 card: 1.0000 bytes: 13
***********************
经过重新计算后,
计算第一种join顺序的成本值,T做驱动表,T1做内部表,
Best:: JoinMethod: NestedLoop
Cost: 4.00 Degree: 1 Resp: 4.00 Card: 1.00 Bytes: 13
在这里可以看到最优join方式是nest loop,这和ALL_ROWS下选择有了区别
最终的成本是4.00,返回结果集记录数是1(Join Card - Rounded: 1)
***********************
Join order[2]: T1[T1]#1 T[T]#0
***************
Now joining: T[T]#0
***************
NL Join
Outer table: Card: 2.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 4
Inner table: T Alias: T
Access Path: TableScan
NL Join: Cost: 166.59 Resp: 166.59 Degree: 0
Cost_io: 164.00 Cost_cpu: 16167061
Resp_io: 164.00 Resp_cpu: 16167061
Access Path: index (AllEqJoinGuess)
Index: IND_OBJECT_ID
resc_io: 2.00 resc_cpu: 15913
ix_sel: 3.8475e-05 ix_sel_with_filters: 3.8475e-05
NL Join (ordered): Cost: 5.01 Resp: 5.01 Degree: 1
Cost_io: 5.00 Cost_cpu: 31647
Resp_io: 5.00 Resp_cpu: 31647
Best NL cost: 5.01
resc: 5.01 resc_io: 5.00 resc_cpu: 31647
resp: 5.01 resp_io: 5.00 resp_cpu: 31647
Join Card: 1.00 = outer (2.00) * inner (25996.00) * sel (1.9234e-05)
Join Card - Rounded: 1 Computed: 1.00
HA Join
Outer table:
resc: 28.13 card 51986.00 bytes: 4 deg: 1 resp: 28.13
Inner table: T Alias: T
resc: 83.30 card: 25996.00 bytes: 9 deg: 1 resp: 83.30
using dmeth: 2 #groups: 1
Cost per ptn: 2.17 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 113.59 Resp: 113.59 [multiMatchCost=0.00]
HA Join (swap)
Outer table:
resc: 164.59 card 51986.00 bytes: 9 deg: 1 resp: 164.59
Inner table: T1 Alias: T1
resc: 2.00 card: 2.00 bytes: 4 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
Cost per ptn: 1.75 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 168.34 Resp: 168.34 [multiMatchCost=0.00]
HA cost: 168.34
resc: 168.34 resc_io: 164.00 resc_cpu: 27076246
resp: 168.34 resp_io: 164.00 resp_cpu: 27076246
Join order aborted: cost > best plan cost
计算第二种join顺序的成本值,T1做驱动表,T做内部表,
Join order aborted: cost > best plan cost
第二种join顺序被放弃,因为成本大于已经第一种join顺序的最优成本
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:1000
*********************************
Number of join permutations tried: 2
*********************************
(newjo-save) [1 0 ]
Final - First K Rows Plan: Best join order: 1
Cost: 4.0039 Degree: 1 Card: 1.0000 Bytes: 13
Resc: 4.0039 Resc_io: 4.0000 Resc_cpu: 24284
Resp: 4.0039 Resp_io: 4.0000 Resc_cpu: 24284
kkoipt: Query block SEL$1 (#0)
在FIRST_Rows_1模式下最终优化器选择了Best join order: 1,Cost: 4.0039,
尝试了2种join 顺序(Number of join permutations tried: 2)
实际上是3种,包括了一次在ALL_ROWS模式下的计算
另外再看一下
FIRST_Rows_10
FIRST_Rows_100
最终的执行计划选择和成本计算
FIRST_Rows_10:
Final - First K Rows Plan: Best join order: 1
Cost: 13.0163 Degree: 1 Card: 10.0000 Bytes: 130
Resc: 13.0163 Resc_io: 13.0000 Resc_cpu: 101517
Resp: 13.0163 Resp_io: 13.0000 Resc_cpu: 101517
FIRST_Rows_100:
Final - First K Rows Plan: Best join order: 1
Cost: 31.8883 Degree: 1 Card: 51982.0000 Bytes: 1143604
Resc: 31.8883 Resc_io: 29.0000 Resc_cpu: 18019724
Resp: 31.8883 Resp_io: 29.0000 Resc_cpu: 18019724
值得注意,FIRST_Rows_100选择了hash
再看一下执行计划
ALL_ROWS:
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
——————————————————————————-
| 0 | SELECT STATEMENT | | 51982 | 659K| 195 (4)|
|* 1 | HASH JOIN | | 51982 | 659K| 195 (4)|
| 2 | INDEX FAST FULL SCAN| IND_T1_OBJECT_ID | 51986 | 203K| 28 (4)|
| 3 | TABLE ACCESS FULL | T | 51986 | 456K| 165 (2)|
——————————————————————————-
FIRST_Rows_1:
—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
—————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)|
| 1 | NESTED LOOPS | | 1 | 13 | 4 (0)|
| 2 | TABLE ACCESS FULL| T | 25996 | 228K| 2 (0)|
|* 3 | INDEX RANGE SCAN | IND_T1_OBJECT_ID | 1 | 4 | 1 (0)|
—————————————————————————-
FIRST_Rows_10:
—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
—————————————————————————-
| 0 | SELECT STATEMENT | | 10 | 130 | 13 (0)|
| 1 | NESTED LOOPS | | 10 | 130 | 13 (0)|
| 2 | TABLE ACCESS FULL| T | 47264 | 415K| 2 (0)|
|* 3 | INDEX RANGE SCAN | IND_T1_OBJECT_ID | 1 | 4 | 1 (0)|
—————————————————————————-
FIRST_Rows_100:
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
——————————————————————————-
| 0 | SELECT STATEMENT | | 51982 | 1116K| 32 (10)|
|* 1 | HASH JOIN | | 51982 | 1116K| 32 (10)|
| 2 | INDEX FAST FULL SCAN| IND_T1_OBJECT_ID | 51986 | 203K| 28 (4)|
| 3 | TABLE ACCESS FULL | T | 51986 | 456K| 2 (0)|
——————————————————————————-
总结来说,first_rows_n基于成本计算,根据优先返回行数N重新计算各个对象的访问成本,
从而生成最快返回前N条记录的执行计划.
沙发?
板凳？