oracle全表扫rownum来取记录的时候，不用扫整个表吗？

fan_zhen_hua

OH。SORRY。。。版主要的是这个信息吧。
ops$oracle/PHPFORUM@PAY>set autotrace off
ops$oracle/PHPFORUM@PAY>col name for a30
ops$oracle/PHPFORUM@PAY>col value for 999999
ops$oracle/PHPFORUM@PAY>SELECT sn.name, ss.value
  2    FROM v$statname sn, v$sesstat ss
  3    WHERE sn.statistic# = ss.statistic#
  4      AND sn.name IN ('table fetch continued row')
  5    AND ss.sid = (select sid from v$mystat where rownum<=1);

NAME                             VALUE
------------------------------ -------
table fetch continued row            0

1 row selected.

Elapsed: 00:00:00.01
ops$oracle/PHPFORUM@PAY>set autotrace traceonly
ops$oracle/PHPFORUM@PAY>  select *
  2      from tbf_user t3
  3     where user_id in
  4           (select t2.user_id
  5              from (select T1.*, rownum linenum
  6                      from (select USER_ID from TBF_USER order by USER_ID) T1
  7                     where rownum <= 25020000) T2
  8             where linenum >= 25010000);

10001 rows selected.

Elapsed: 00:02:35.45

Execution Plan
----------------------------------------------------------
Plan hash value: 1662795728

--------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    25M|  2171M|       |   503K  (1)| 01:40:38 |
|*  1 |  HASH JOIN RIGHT SEMI|             |    25M|  2171M|   715M|   503K  (1)| 01:40:38 |
|   2 |   VIEW               | VW_NSO_1    |    25M|   429M|       |   196K  (1)| 00:39:17 |
|*  3 |    VIEW              |             |    25M|   691M|       |   196K  (1)| 00:39:17 |
|*  4 |     COUNT STOPKEY    |             |       |       |       |            |          |
|   5 |      VIEW            |             |    43M|   670M|       |   196K  (1)| 00:39:17 |
|   6 |       INDEX FULL SCAN| TBF_USER_PK |    43M|   670M|       |   196K  (1)| 00:39:17 |
|   7 |   TABLE ACCESS FULL  | TBF_USER    |    43M|  3058M|       | 94137   (2)| 00:18:50 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("USER_ID"="$nso_col_1")
   3 - filter("LINENUM">=25010000)
   4 - filter(ROWNUM<=25020000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     620686  consistent gets
     620548  physical reads
          0  redo size
     586239  bytes sent via SQL*Net to client
       1592  bytes received via SQL*Net from client
        102  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10001  rows processed

ops$oracle/PHPFORUM@PAY>set autotrace off
col name for a30
col value for 999999
SELECT sn.name, ss.value
ops$oracle/PHPFORUM@PAY>ops$oracle/PHPFORUM@PAY>ops$oracle/PHPFORUM@PAY>  2    FROM v$statname sn, v$sesstat ss
  3    WHERE sn.statistic# = ss.statistic#
  4      AND sn.name IN ('table fetch continued row')
  5    AND ss.sid = (select sid from v$mystat where rownum<=1);

NAME                             VALUE
------------------------------ -------
table fetch continued row           15

1 row selected.


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


ops$oracle/PHPFORUM@PAY>  set autotrace traceonly
ops$oracle/PHPFORUM@PAY>  select * from tbf_user where rownum < 25020000;

25019999 rows selected.

Elapsed: 00:08:09.62

Execution Plan
----------------------------------------------------------
Plan hash value: 3678947139

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    25M|  1741M| 53608   (2)| 00:10:44 |
|*  1 |  COUNT STOPKEY     |          |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TBF_USER |    25M|  1741M| 53608   (2)| 00:10:44 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<25020000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     480172  consistent gets
     229148  physical reads
          0  redo size
1106747637  bytes sent via SQL*Net to client
    2752681  bytes received via SQL*Net from client
     250201  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
   25019999  rows processed

ops$oracle/PHPFORUM@PAY>  set autotrace off
ops$oracle/PHPFORUM@PAY>col name for a30
ops$oracle/PHPFORUM@PAY>col value for 999999
ops$oracle/PHPFORUM@PAY>SELECT sn.name, ss.value
  2    FROM v$statname sn, v$sesstat ss
  3    WHERE sn.statistic# = ss.statistic#
  4      AND sn.name IN ('table fetch continued row')
  5    AND ss.sid = (select sid from v$mystat where rownum<=1);

NAME                             VALUE
------------------------------ -------
table fetch continued row            0

1 row selected.

Elapsed: 00:00:00.01

Yong Huang

Did you change table TBF_USER between the two tests? I don't understand why the second row count is 25M on line 2 (TABLE ACCESS FULL)? It should still be 43M.

A simpler case to test our theory is:

create table testlong (x int, y long);
--my db_block_size is 4, so 5000 bytes is enough to cause a row to chain
insert into testlong values (1, rpad('x', 5000));
insert into testlong values (2, rpad('x', 5000));
insert into testlong values (3, rpad('x', 5000));
commit;
--confirm chained_rows
analyze table testlong list chained rows;
select * from chained_rows;
--In my 10gR2, 'table fetch continued row' statistic# is 252
--Get base value
select value from v$mystat where statistic# = 252;
select * from testlong where rownum < 2;
--Should increase by 1
select value from v$mystat where statistic# = 252;
--Run the two above queries a few times

--Add rownum stop key
select count(*) from select * from testlong where rownum < 2);
--stat value should not increase because of this rownum optimization; you can change 2 to 3 and it won't change either
select value from v$mystat where statistic# = 252;

If you check v$mystat, there's no need to join with v$sesstat.

Yong Huang

[ 本帖最后由 Yong Huang 于 2010-3-17 15:45 编辑 ]