发表于: 2008.04.28 15:02
分类: 性能优化
出处: http://warehouse.itpub.net/post/777/460759
---------------------------------------------------------------
都是doc上的原话，记录一下！
FIRST_ROWS(n)提示告诉优化器要执行的sql语句的优化目标是获得最快的响应时间，但是下面这些情况该提示不起作用：
The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following syntax:
- Set operators (
UNION,INTERSECT,MINUS,UNIONALL) GROUPBYclauseFORUPDATEclause- Aggregate functions
DISTINCToperatorORDERBYclauses, when there is no index on the ordering columns
These statements cannot be optimized for best response time, because Oracle must retrieve all rows accessed by the statement before returning the first row. If you specify this hint in any of these statements, then the optimizer uses the cost-based approach and optimizes for best throughput.
这是9i性能优化向导上的内容，在11g下测试count操作（Aggregate functions）发现提示也起作用！
The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following syntax:
- Set operators (
UNION,INTERSECT,MINUS,UNIONALL) GROUPBYclauseFORUPDATEclause- Aggregate functions
DISTINCToperatorORDERBYclauses, when there is no index on the ordering columns
These statements cannot be optimized for best response time, because Oracle must retrieve all rows accessed by the statement before returning the first row. If you specify this hint in any of these statements, then the optimizer uses the cost-based approach and optimizes for best throughput.
If you specify either the ALL_ROWS or the FIRST_ROWS hint in a SQL statement, and if the data dictionary does not have statistics about tables accessed by the statement, then the optimizer uses default statistical values (such as allocated storage for such tables) to estimate the missing statistics and, subsequently, to choose an execution plan.
These estimates might not be as accurate as those gathered by the DBMS_STATS package. Therefore, use the DBMS_STATS package to gather statistics. If you specify hints for access paths or join operations along with either the ALL_ROWS or FIRST_ROWS hint, then the optimizer gives precedence to the access paths and join operations specified by the hints.
