都是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
,UNION
ALL
) GROUP
BY
clauseFOR
UPDATE
clause- Aggregate functions
DISTINCT
operatorORDER
BY
clauses, 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)发现提示也起作用!
[@more@]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
,UNION
ALL
) GROUP
BY
clauseFOR
UPDATE
clause- Aggregate functions
DISTINCT
operatorORDER
BY
clauses, 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.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1003114/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/19602/viewspace-1003114/