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

ALL_ROWS FIRST_ROWS(n) CHOOSE RULE

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'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-232269/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-232269/