hint--all_rows和fist_rows

来源：互联网发布：mac如何更改账户编辑：程序博客网时间：2024/05/17 02:08

在日常工作中经常被是该用all_rows还是fist_rows所迷惑，今天静下心来研究了一下，大致上明白了他们的区别。

all_rows的功能是指示优化器在优化语句时，以消耗最少资源的最佳吞吐量为优化目标。

fist_rows指示优化器在优化语句时，以最高效地返回前面指定数量的记录为目标。

SQL> SET AUTOTRACE ON
SQL> select /*+ all_rows */* from t where rownum<=10;

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 508354683

-----------------------------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time         |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |   280 |    56   (2)| 00:00:01        |
|* 1 | COUNT STOPKEY       |      |         |           |                 |                         |
|   2 |   TABLE ACCESS FULL| T    | 50375 | 1377K|    56   (2)| 00:00:01 |    可以看出all_rows是一次性获取所有结果集
-----------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=10)
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          5 consistent gets
          0 physical reads
          0 redo size
        804 bytes sent via SQL*Net to client
        469 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         10 rows processed

SQL> select * from t where rownum<=10;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 508354683

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |   280 |     2   (0)| 00:00:01 |
|* 1 | COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T    |    10 |   280 |     2   (0)| 00:00:01 | ---只获得想要的结果
---------------------------------------------------------------------------

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

1 - filter(ROWNUM<=10)

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          5 consistent gets
          0 physical reads
          0 redo size
        804 bytes sent via SQL*Net to client
        469 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         10 rows processed

最终的结论是，在OLAP环境下需要一次返回所有记录时用all_rows较好，返回部分记录时用first_rows较好

0 0