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=0x905f2620
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条记录的执行计划.