FIRST_ROWS和FIRST_ROWS(N)的区别

早就听说FIRST_ROWS和FIRST_ROWS(N)有区别，一直也没有测试出来到底差异在哪里，没想到测试全文索引的时候，发现了二者的差异。

ITPUB个人空间3a*ur[-qk#P q^(b)L

按照Oracle文档的说法，FIRST_ROWS并不完全是CBO模式，还包含了大量的基于规则的优化方式。而9i新增的FIRST_ROWS(N)则完全基于代价的方式。

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30), DOCS VARCHAR2(4000));

表已创建。

SQL> INSERT INTO T ITPUB个人空间i#_!n%G.|#ZY
2 SELECT ROWNUM, OBJECT_NAME, ITPUB个人空间L~ U UZ{)~_!a$P
3 OWNER || ' ' || OBJECT_TYPE || ' ' || OBJECT_NAME ITPUB个人空间+xW;gW4d)@eZ
4 FROM DBA_OBJECTS;

已创建31313行。

SQL> CREATE INDEX IND_T_NAME ON T (NAME);

索引已创建。

SQL> CREATE INDEX IND_T_DOCS ON T (DOCS) INDEXTYPE IS CTXSYS.CONTEXT;

索引已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100')

ITPUB个人空间8j,Y,w){#P#_s+j
PL/SQL 过程已成功完成。

SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(USER, 'IND_T_DOCS')

PL/SQL 过程已成功完成。

SQL> SET AUTOT ON EXPITPUB个人空间7wOopL/d8{
SQL> COL DOCS FORMAT A50
1?8Q.@X+iw0SQL> SELECT * FROM TITPUB个人空间1Bf!lZKz8r
n-L)z
2 WHERE CONTAINS(DOCS, 'SYS', 1) > 0ITPUB个人空间L*vDF0v"g;_ tC
3 AND NAME = 'DUAL'
i'Sty(v0 4 ORDER BY SCORE(1);

ID NAME DOCS
s0a^)YJ%np0O0Y0---------- ------------------------------ ---------------------------------ITPUB个人空间?L+j6r:TA0v
7112 DUAL SYS TABLE DUAL

执行计划
'l)B+B$i?.M2Gs}L0----------------------------------------------------------
0{r!J fQf'rd2G| Z0 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=74)
]oP}x5Q/L
wG@0 1 0 SORT (ORDER BY) (Cost=5 Card=1 Bytes=74)
P.nX3AH0fY9|0 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=74)
$@
ZR;^
{(P {`0 3 2 INDEX (RANGE SCAN) OF 'IND_T_NAME' (NON-UNIQUE) (Cost=1 Card=2)

根据列的数据的分布以及索引的统计信息，Oracle在这里选择了普通索引，而没有选择全文索引。

下面就可以看到两个提示FIRST_ROWS和FIRST_ROWS(N)的区别了：

SQL> SELECT /*+ FIRST_ROWS */ * FROM T
'{ouOOz5s0 2 WHERE CONTAINS(DOCS, 'SYS', 1) > 0
!p!Y:^.p%cv?G)mlw0 3 AND NAME = 'DUAL'
;eh!Xx!W6_0 4 ORDER BY SCORE(1);

ID NAME DOCS
#~{'CUN0---------- ------------------------------ ----------------------------------ITPUB个人空间'V;_T'Q:M3]$h
7112 DUAL SYS TABLE DUAL

执行计划
Ms-b2B@+S:[0----------------------------------------------------------
c\MxK0 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2643 Card=1 Bytes=74)ITPUB个人空间C0m
lw i,}"^qW$q:@
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2640 Card=1 Bytes=74)ITPUB个人空间 }hSAhz
2 1 DOMAIN INDEX OF 'IND_T_DOCS' (Cost=2369 Card=2)

SQL> SELECT /*+ FIRST_ROWS(1) */ * FROM T
8[)n#@'C,xI0 2 WHERE CONTAINS(DOCS, 'SYS', 1) > 0
,ix J k_.lxK0 3 AND NAME = 'DUAL'ITPUB个人空间v-Uo&@.aY G*]
4 ORDER BY SCORE(1);

ID NAME DOCSITPUB个人空间$p2hH#{ QY5X
---------- ------------------------------ ----------------------------------
R`Ix2V-F0 7112 DUAL SYS TABLE DUAL

执行计划
D6l;M3pF0----------------------------------------------------------ITPUB个人空间l`7U"y+[z)Q
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=5 Card=1 Bytes=74)ITPUB个人空间Bb }6M4H)WWO
1 0 SORT (ORDER BY) (Cost=5 Card=1 Bytes=74)ITPUB个人空间 Y6A@~"Vgl*e
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=74)
+mUw3Z\*m-c!Y0 3 2 INDEX (RANGE SCAN) OF 'IND_T_NAME' (NON-UNIQUE) (Cost=1 Card=2)

FIRST_ROWS(N)完全根据统计信息来进行判断，而FIRST_ROWS包含了一些默认的规则在其中，因此，FIRST_ROWS选择了域索引，而没有选择代价更小的普通索引。

可能也是出于上述的考虑，Oracle选择默认的CBO模式时，选择了ALL_ROWS而不是FIRST_ROWS。