关于oracle hint 值得注意的地方

来源：互联网发布：手机plc编程软件下载编辑：程序博客网时间：2024/05/02 01:41

大家都知道oracle hint如何用，但是有些小细节，希望对大家有用

1.first_row(n)

The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that include any blocking operations, such as sorts or groupings

此时不能有分组和排序，意思是只能用于比如详单的查询，否则失效。

2. DYNAMIC_SAMPLING

If there is no single-table predicate (a WHERE clause that evaluates only one table), then the optimizer trusts the existing statistics and ignores this hint.

在没有统计信息的情形下，动态采样是非常的利器，但是单表而没有谓词的情形，失效。

 失效：

SELECT /*+ dynamic_sampling(e 1) */ count(*)   FROM employees e;

有效：

SELECT /*+ dynamic_sampling(employees 1) */ *   FROM employees   WHERE ...

3.Only two hints are used with INSERT statements: The APPEND hint always follows the INSERT keyword, and the PARALLEL hint can follow the INSERT keyword

insert, 只能有append parallel hint可用

4. 一建考试要点

http://wenku.baidu.com/view/e5b0063f580216fc700afd32.html