循序渐进---hints－Hints for Optimization Approaches and Goals

1. ALL_ROWS

SQL>  SELECT /*+ ALL_ROWS */ count(*) from t;
 
  COUNT(*)
----------
       500
 
Elapsed: 00:00:00.09
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=HINT: ALL_ROWS (Cost=98 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=98 Card=500)

2.first rows(n)

SQL> select /*+ first_rows(10) */ * from test1 where rownum<1;
 
no rows selected
 
Elapsed: 00:00:00.08
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=2 Card=1 B
          ytes=177)
 
   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (FULL) OF 'TEST1' (Cost=2 Card=11 Bytes=194
          7)

3.choose

SQL> select /*+ choose */ * from test1 where rownum<1;
 
no rows selected
 
Elapsed: 00:00:00.03
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=HINT: CHOOSE
   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (FULL) OF 'TEST1'

4.rule

SQL> select /* rule */ * from test1 where rownum<1;
 
no rows selected
 
Elapsed: 00:00:00.00
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (FULL) OF 'TEST1'