oracle+rownum优先级,/*+first row*/与rownum<2,为什么差距怎么大？

纠正一下，不是first_row(first row),而是first_rows(first_rows(n),first_rows_n)

对first_rows和rownum<2做了测试，见如下的结果。从结果可以看出相应的差距。

分有索引和无索引两种情况，每种情况下有7种写法。

SQL> desc temp

Name           Type          Nullable Default Comments

-------------- ------------- -------- ------- --------

ID             NUMBER(15)

REQUESTID      VARCHAR2(20)  Y

MESSAGETYPE    VARCHAR2(1)

REQUESTTIME    DATE          Y

HREF           VARCHAR2(250) Y

SENDER         VARCHAR2(20)  Y

RECEIVER       VARCHAR2(15)

MESSAGECONTENT VARCHAR2(500) Y

MESSAGESUBJECT VARCHAR2(200) Y

SENDHOSTIP     VARCHAR2(20)

REQUESTIP      VARCHAR2(15)  Y

MMSZIPID       VARCHAR2(30)  Y

SQL> select count(*) from temp;

COUNT(*)

----------

161123

Executed in 0.344 seconds

SQL> select count(distinct id) from temp;

COUNT(DISTINCTID)

-----------------

161123

Executed in 1.109 seconds

SQL>

SQL> set autotrace traceonly

SQL> select * from temp where id= 1 and rownum<2;

已用时间:  00: 00: 06.64

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   COUNT (STOPKEY)

2    1     TABLE ACCESS (FULL) OF 'TEMP'

统计信息

----------------------------------------------------------

177  recursive calls

0  db block gets

43  consistent gets

0  physical reads

0  redo size

642  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

2  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>

SQL> select /*+ FIRST_ROWS */ * from temp where id=1;

已用时间:  00: 00: 07.68

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=208 Card=1

753 Bytes=1009728)

1    0   TABLE ACCESS (FULL) OF 'TEMP' (Cost=208 Card=1753 Bytes=10

09728)

统计信息

----------------------------------------------------------

260  recursive calls

0  db block gets

2182  consistent gets

2145  physical reads

0  redo size

643  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

2  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ FIRST_ROWS(1) */ * from temp where id=1;

已用时间:  00: 00: 07.06

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=1 B

ytes=576)

1    0   TABLE ACCESS (FULL) OF 'TEMP' (Cost=2 Card=1 Bytes=576)

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

2155  consistent gets

0  physical reads

0  redo size

643  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ FIRST_ROWS_1 */ * from temp where id=1;

已用时间:  00: 00: 06.95

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   TABLE ACCESS (FULL) OF 'TEMP'

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

2153  consistent gets

0  physical reads

0  redo size

643  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ FIRST_ROWS */ * from temp where id=1 and rownum<2;

已用时间:  00: 00: 06.51

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=208 Card=1

Bytes=576)

1    0   COUNT (STOPKEY)

2    1     TABLE ACCESS (FULL) OF 'TEMP' (Cost=208 Card=1753 Bytes=

1009728)

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

5  consistent gets

0  physical reads

0  redo size

643  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ FIRST_ROWS(1) */ * from temp where id=1 and rownum<2;

已用时间:  00: 00: 06.56

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=1 B

ytes=576)

1    0   COUNT (STOPKEY)

2    1     TABLE ACCESS (FULL) OF 'TEMP' (Cost=2 Card=1 Bytes=576)

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

6  consistent gets

0  physical reads

0  redo size

643  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ FIRST_ROWS_1 */ * from temp where id=1 and rownum<2;

已用时间:  00: 00: 06.56

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   COUNT (STOPKEY)

2    1     TABLE ACCESS (FULL) OF 'TEMP'

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

4  consistent gets

0  physical reads

0  redo size

643  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>

SQL>

SQL> create index indx_temp on temp(id);

索引已创建。

已用时间:  00: 00: 01.65

SQL>

SQL> select * from temp where id=1 and rownum<2;

已用时间:  00: 00: 06.53

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   COUNT (STOPKEY)

2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEMP'

3    2       INDEX (RANGE SCAN) OF 'INDX_TEMP' (NON-UNIQUE)

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

3  consistent gets

1  physical reads

0  redo size

642  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ FIRST_ROWS */ * from temp where id=1;

已用时间:  00: 00: 06.50

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=175

3 Bytes=1009728)

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEMP' (Cost=2 Card=1753

Bytes=1009728)

2    1     INDEX (RANGE SCAN) OF 'INDX_TEMP' (NON-UNIQUE) (Cost=1 C

ard=701)

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

4  consistent gets

0  physical reads

0  redo size

643  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ FIRST_ROWS(1) */ * from temp where id=1;

已用时间:  00: 00: 06.58

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=1 B

ytes=576)

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEMP' (Cost=2 Card=1 Byt

es=576)

2    1     INDEX (RANGE SCAN) OF 'INDX_TEMP' (NON-UNIQUE) (Cost=1 C

ard=1753)

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

4  consistent gets

0  physical reads

0  redo size

643  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ FIRST_ROWS_1 */ * from temp where id=1;

已用时间:  00: 00: 06.54

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEMP'

2    1     INDEX (RANGE SCAN) OF 'INDX_TEMP' (NON-UNIQUE)

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

4  consistent gets

0  physical reads

0  redo size

643  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ FIRST_ROWS */ * from temp where id=1 and rownum<2;

已用时间:  00: 00: 06.59

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=1 B

ytes=576)

1    0   COUNT (STOPKEY)

2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEMP' (Cost=2 Card=175

3 Bytes=1009728)

3    2       INDEX (RANGE SCAN) OF 'INDX_TEMP' (NON-UNIQUE) (Cost=1

Card=701)

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

643  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ FIRST_ROWS(1) */ * from temp where id=1 and rownum<2;

已用时间:  00: 00: 06.51

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=1 B

ytes=576)

1    0   COUNT (STOPKEY)

2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEMP' (Cost=2 Card=1 B

ytes=576)

3    2       INDEX (RANGE SCAN) OF 'INDX_TEMP' (NON-UNIQUE) (Cost=1

Card=1753)

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

643  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ FIRST_ROWS_1 */ * from temp where id=1 and rownum<2;

已用时间:  00: 00: 06.54

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   COUNT (STOPKEY)

2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEMP'

3    2       INDEX (RANGE SCAN) OF 'INDX_TEMP' (NON-UNIQUE)

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

643  bytes sent via SQL*Net to client

236  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>

SQL> spool off