FIRST_ROWS & ROWNUM

最新推荐文章于 2021-04-02 16:40:42 发布

cuiquan1055

最新推荐文章于 2021-04-02 16:40:42 发布

阅读量137

点赞数

上周对first_rows和rownum<2做了测试，见如下的结果。从结果可以看出相应的差距。分有索引和无索引两种情况，每种情况下有7种写法。

[@more@]

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)

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)

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)

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)