Oracle FIRST_ROWS语法

最近，我观察到FIRST_ROWS提示的用法写为

FIRST_ROWS EXPMA 851.120.01

我知道FIRST_ROWS提示的一般语法应该有一个

(n)

值，表示要排序的行数。

谁能告诉我语法FIRST_ROWS EXPMA 851.120.01是否正确，“EXPMA” 和 “851.120.01” 的含义可能是什么？

谢谢，

FIRST_ROWS是FIRST_ROWS(n) 的历史版本。而且一般不应该使用。

FIRST_ROWS(n) 是 “使用所有正常成本计算算法对前n行进行优化”，而

FIRST_ROWS是 “使用预定义的规则集的第一行的优化器，这些规则将强制使用索引，嵌套循环等。”

额外的文本只是注释，这不会影响之前发生的提示。

SQL> create table t as select * from dba_Objects;

Table created.

SQL> create index ix on t ( owner );

Index created.

SQL> exec dbms_stats.gather_table_stats('','T',method_opt=>'for all columns size 200');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 51685 |  6662K|   427   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 51685 |  6662K|   427   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OWNER"='SYS')

SQL> select /*+ first_rows(1) */ * from t where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 3947747388

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |     1 |   132 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |     1 |   132 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IX   |       |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("OWNER"='SYS')

SQL> select /*+ first_rows */ * from t where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 3947747388

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      | 51685 |  6662K|  1522   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    | 51685 |  6662K|  1522   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IX   | 51685 |       |   119   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("OWNER"='SYS')

SQL> select /*+ first_rows other comments */ * from t where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 3947747388

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      | 51685 |  6662K|  1522   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    | 51685 |  6662K|  1522   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IX   | 51685 |       |   119   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("OWNER"='SYS')

SQL>
SQL>
SQL>