Recently, I observed a usage of FIRST_ROWS hint written as
FIRST_ROWS EXPMA 851.120.01
I know the general syntax of FIRST_ROWS hint which should have a
(n)
value which means the number of rows to be ordered.
Can anyone tell me if the syntax FIRST_ROWS EXPMA 851.120.01 is correct and what might be the meaning of "EXPMA" and "851.120.01"?
Thank you,
FIRST_ROWS is the historical version of FIRST_ROWS(n). And generally it should not be used.
FIRST_ROWS(n) is "optimize for the first n rows using all the normal costing algorithms", whereas
FIRST_ROWS is "optimizer for the first rows using a pre-defined set of rules that will force index use, nested loops, etc."
The extra text is just comments and this does not impact hints that occur before it.
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>