hints提示总结 zt

http://space.itpub.net/77406/viewspace-510300

http://download.oracle.com/docs/cd/B12037_01/server.101/b10752/hintsref.htm#6174

[@more@]

/*+ ALL_ROWS */
表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id
FROM employees WHERE employee_id = 192;

/*+ APPEND */
直接插入到表的最后,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
insert /*+append */ into emp nologging

/*+ CACHE */
当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name FROM employees hr_emp;

/*+ CLUSTER */
提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

/*+ CURSOR_SHARING_EXACT */
当cursor_sharing=similar或者force的时候使用比较方便
SELECT /*+cursor_sharing_exact*/ COUNT(*)FROM tt WHERE a = 2 AND b = 'kyp'
/*+ DRIVING_SITE */
是分布式查询中另一个数据库成为该查询的驱动者
SELECT /*+ DRIVING_SITE(b) */ *
FROM employees a,departments@rsiteb WHERE a.department_id = b.department_id;

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

/*+ FACT */
the table specified in tablespec should be considered as a fact table
/*+ FIRST_ROWS */
表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
FROM employees WHERE department_id = 20;

/*+ FULL */
表明对表选择全局扫描的方法.
SELECT /*+ FULL(e) */ employee_id, last_name FROM hr.employees e WHERE last_name LIKE :b1;

/*+ HASH */
hash scan to access the specified table. This hint applies only to tables stored in a table cluster.
/*+ INDEX */
表明对表选择索引的扫描方法.
SELECT /*+ INDEX (employees emp_department_ix)*/
employee_id, department_id FROM employees WHERE department_id > 50;

/*+ INDEX_ASC */
表明对表选择索引升序的扫描方法.
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='CCBZZP';

/*+ INDEX_COMBINE */
为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.
index_combine最早是用在bitmap index上的,在9i开始oracle默认可以使用在btree索引上,这是由_b_tree_bitmap_plans参数来控制的.oracle将btree索引中获得的rowid信息通过BITMAP CONVERSION FROM ROWIDS的步骤转换成bitmap进行匹配,然后匹配完成后通过BITMAP CONVERSION TO ROWIDS再转换出rowid获得数据或者回表获得数据.
SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *
FROM employees e WHERE manager_id = 108 OR department_id = 110;

/*+ INDEX_DESC */
表明对表选择索引降序的扫描方法.
SELECT /*+ INDEX_DESC(e emp_name_ix) */ * FROM employees e;

/*+ INDEX_FFS */
对指定的表执行快速全索引扫描,而不是全表扫描的办法.
SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_name FROM employees e;
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';

/*+ INDEX_JOIN */
index_join这个hint的主要功能是通过对表索引的hash_join操作获得所需要的数据,从而避免回表执行查询.针对源表数据较大,而返回结果数据都可以在索引中满足的情况,这个hint比较有效，甚至可以为不带查询条件的检索语句使用index_join的提示.只不过由index range scan变成了index fast full scan.
SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id FROM employees e
WHERE manager_id < 110 AND department_id < 50;

Execution Plan
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 3 (34)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 3 | 21 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN| EMP_DEPARTMENT_IX | 3 | 21 | 2 (50)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| EMP_MANAGER_IX | 3 | 21 | 2 (50)| 00:00:01 |
----------------------------------------------------------------------------------------

/*+ INDEX_SS */
此hint明确地为指定表格选择index skip scan。如果语句使用index range scan，Oracle将以对其索引值的升序排列来检查索引入口。在被分割的索引中，其结果为对每个部分内部的升序排列。
SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name FROM employees e WHERE first_name = 'Steven';

/*+ INDEX_SS_ASC */
此hint明确地为指定表格选择index skip scan。如果语句使用index range scan，Oracle将以对其索引值的升序排列来检查索引入口。在被分割的索引中，其结果为对每个部分内部的升序排列。
SELECT * /*+ INDEX_SS_ASC(e) */FROM emp e WHERE ename = 'Jones';

/*+ INDEX_SS_DESC */
此hint明确为指定表格选择index skip scan。如果语句使用index range scan，Oracle将以对其索引值的降序排列来检查索引入口。在被分割的索引中，其结果为对每个部分内部的降序排列。
SELECT /*+ INDEX_SS_DESC(e emp_name_ix) */ last_name FROM employees e WHERE first_name = 'Steven';

/*+ LEADING */
将指定的表作为连接次序中的首表.
SELECT /*+ LEADING(e j) */ * FROM employees e, departments d, job_history j
WHERE e.department_id = d.department_idAND e.hire_date = j.start_date;
SELECT /*+ leading(t_max) use_hash(t_max t) */
*
FROM t_max,
t,
t_min
WHERE t.object_id = t_max.object_id
AND t_max.object_id = t_min.object_id;
/*+ MERGE */
能够对视图的各个查询进行相应的合并.
SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary
FROM employees e1,
(SELECT department_id, avg(salary) avg_salary FROM employees e2 GROUP BY department_id) v
WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;

/*+ MODEL_MIN_ANALYSIS */
The MODEL_MIN_ANALYSIS hint instructs the optimizer to omit some compile-time optimizations of spreadsheet rules—primarily detailed dependency graph analysis. Other spreadsheet optimizations, such as creating filters to selectively populate spreadsheet access structures and limited rule pruning, are still used by the optimizer.

This hint reduces compilation time because spreadsheet analysis can be lengthy if the number of spreadsheet rules is more than several hundreds.
/*+ MONITOR */ --11g
/*+ NOAPPEND */
通过在插入语句生存期内停止并行模式来启动常规插入.
insert /*+noappend*/ into test1 select * from test4 ;

/*+ NOCACHE */
当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
SELECT /*+ FULL(hr_emp) NOCACHE(hr_emp) */ last_name FROM employees hr_emp;

/*+ NO_EXPAND */
对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.
对于inlist查询，Oracle通常会进行改写，将形如select ..... from ....... where ....in (..........)的sql语句，改写为union all的形式来执行，这个改写通常是潜在的。
然而这一改写可能存在问题，如果inlist中的值比较多的话，CBO花在分析执行路径上的时间和成本都会相当大，此时我们通常需要阻止Oracle的这一展开操作.
我们可以通过NO_EXPAND提示来阻止Oracle进行这样的改写。
使用了NO_EXPAND提示后会使用"inlist iterator"方式来执行SQL，这样可以用到index。
SELECT /*+ NO_EXPAND */ *
FROM employees e, departments d WHERE e.manager_id = 108 OR d.department_id = 110;

/*+ NO_FACT */
The NO_FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table.
/*+ NO_INDEX */
SELECT /*+ NO_INDEX(employees emp_empid) */ employee_id FROM employees WHERE employee_id > 200;

/*+ NO_INDEX_FFS */
此hint使CBO拒绝对指定表格的指定标签进行fast full-index scan。
SELECT /*+ NO_INDEX_FFS(items item_order_ix) */ order_id FROM order_items items;

/*+ NO_INDEX_SS */
此hint使CBO拒绝对指定表格的指定标签进行skip scan。
/*+ NO_INDEX_SS (tablespecindexspec ) */
/*+ NO_MERGE */
对于有可合并的视图不再合并.
SELECT /*+NO_MERGE(seattle_dept)*/ e1.last_name, seattle_dept.department_name
FROM employees e1,
(SELECT location_id, department_id, department_name
FROM departments WHERE location_id = 1700) seattle_dept
WHERE e1.department_id = seattle_dept.department_id;

/*+ NO_MONITOR */ --11g
/*+ NO_PARALLEL */
建议不执行并行查询
SELECT /*+ NO_PARALLEL(hr_emp) */ last_name FROM employees hr_emp;

/*+ NO_PARALLEL_INDEX */
建议不并行化索引范围扫描
SELECT * /*+ NO_PARALLEL_INDEX */
FROM emp
WHERE empno BETWEEN 1001 AND 2002;

/*+ NO_PUSH_PRED */

SELECT /*+ NO_MERGE(v) NO_PUSH_PRED(v) */ *
FROM employees e,(SELECT manager_id FROM employees ) v
WHERE e.manager_id = v.manager_id(+)
AND e.employee_id = 100;

/*+ NO_PUSH_SUBQ */
在尽可能最晚的时间计算子查询
SELECT e.ename,
d.dname /*+ NO_PUSH_SUBQ */
FROM emp e,
(SELECT * FROM dept WHERE deptno = 10) d
WHERE e.deptno = d.deptno;

/*+ NO_PX_JOIN_FILTER */
阻止优化器使用并行联合位图过滤器
/*+ NO_QUERY_TRANSFORMATION */
SELECT /*+ NO_QUERY_TRANSFORMATION */ employee_id, last_name
FROM (SELECT * FROM employees e) v WHERE v.last_name = 'Smith';

/*+ NO_RESULT_CACHE */ --11g
/*+ NO_REWRITE */
禁止对查询块的查询重写操作.
SELECT /*+ NO_REWRITE */ sum(s.amount_sold) AS dollars
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

/*+ NO_STAR_TRANSFORMATION */
此hint使CBO忽略star 询问信息。
/*+ NO_STAR_TRANSFORMATION */
/*+ NO_UNNEST */
关闭特定子查询块
SELECT /*+ NO_UNNEST */
COUNT(*)
FROM horses
WHERE horse_name LIKE 'M%'
AND horse_name NOT IN
(SELECT horse_name FROM horse_owners WHERE owner LIKE '%Lombardo%');

/*+ NO_USE_HASH */
此hint使CBO通过把指定表格作为内部表格的方式，拒绝hash joins把每个指定表格加入到另一原始行
SELECT /*+ NO_USE_HASH(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id;

/*+ NO_USE_MERGE */
此hint使CBO通过把指定表格作为内部表格的方式，拒绝sort-merge把每个指定表格加入到另一原始行。
SELECT /*+ NO_USE_MERGE(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id
ORDER BY d.department_id;

/*+ NO_USE_NL */
Hint no_use_nl使CBO执行循环嵌套，通过把指定表格作为内部表格，把每个指定表格连接到另一原始行。通过这一hint，只有hash join和sort-merge joins会为指定表格所考虑。
SELECT /*+ NO_USE_NL(l h) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 3500;
When this hint is specified, only hash join and

/*+ NO_XMLINDEX_REWRITE */ --11g
SELECT /*+NO_XMLINDEX_REWRITE*/ count(*) FROM table WHERE existsNode(OBJECT_VALUE, '/*') = 1;

/*+ NO_XML_QUERY_REWRITE */
The NO_XML_QUERY_REWRITE hint instructs the optimizer to prohibit the rewriting of XPath expressions in SQL statements
SELECT /*+NO_XML_QUERY_REWRITE*/ XMLQUERY('') FROM dual;

/*+ OPT_PARAM */ --11g
SELECT /*+ OPT_PARAM('star_transformation_enabled' 'true') */ * FROM ... ;

/*+ ORDERED */
根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.
SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity
FROM customers c, order_items l, orders o
WHERE c.cust_last_name = :b1
AND o.customer_id = c.customer_id
AND o.order_id = l.order_id;
select /*+ ordered use_hash(t_max t) */ * from t,t_max where t.object_id = t_max.object_id ;

/*+ PARALLEL */
指定并行度
SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 5) */ last_name FROM employees hr_emp;
使用初始化参数中的缺省并行度
SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT) */ last_name FROM employees hr_emp;

/*+ PARALLEL_INDEX */
为分区索引指定并行索引扫描的数量
SELECT /*+ PARALLEL_INDEX(table1, index1, 3) */......

/*+ PQ_DISTRIBUTE */
选择在一个PQ中分发某个表的方法(out/in 的取值范围: HASH/NONE/BROADCAST/PARTITION)
HASH, HASH
BROADCAST, NONE
NONE, BROADCAST
PARTITION, NONE
NONE, PARTITION
NONE, NONE
SELECT /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/ column_list FROM r,s WHERE r.c=s.c;
SELECT /*+ORDERED PQ_DISTRIBUTE(s BROADCAST, NONE) USE_HASH (s) */ column_list FROM r,s WHERE r.c=s.c;

/*+ PUSH_PRED */
SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *
FROM employees e,(SELECT manager_id FROM employees ) v
WHERE e.manager_id = v.manager_id(+) AND e.employee_id = 100;

/*+ PUSH_SUBQ */
在尽可能早的时间计算子查询
SELECT e.ename,
d.dname /*+ NO_PUSH_SUBQ */
FROM emp e,
(SELECT * FROM dept WHERE deptno = 10) d
WHERE e.deptno = d.deptno;

/*+ PX_JOIN_FILTER */
This hint forces the optimizer to use parallel join bitmap filtering.
/*+ QB_NAME */
DELETE /*+ push_subq (@qb) full (@qb) index (x) */
FROM wl_client_sus x
WHERE 1 = 1
AND x.status = 'S'
AND ROWID IN (SELECT /*+ qb_name (qb) */
ROWID
FROM (SELECT ROWID,
rank() over(PARTITION BY client_no, watchlist_type, matched_name, watchlist, field_desc, status ORDER BY ROWID ASC) AS rnk
FROM wl_client_sus
WHERE status = 'S') s
WHERE rnk > 1);

SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name FROM employees e WHERE last_name = 'Smith';

/*+ RESULT_CACHE */ --11g
/*+ REWRITE */
可以将视图作为参数.

/*+ STAR_TRANSFORMATION */
种语句被自动转换的方式，一般是用在星形查询当中，即一个事实表，多个维表的关联。其间，也许会用到bitmap index,也许不会。其他的语句自动转换方式还有: merge （涉及到sub query）, rewrite (有mv)。
SELECT /*+ STAR_TRANSFORMATION */ *
FROM sales s, times t, products p, channels c
WHERE s.time_id = t.time_id
AND s.prod_id = p.product_id
AND s.channel_id = c.channel_id
AND p.product_status = 'obsolete';

/*+ UNNEST */
告诉优化器将子查询转化为连接的方式
SELECT t1.id,
t1.object_name
FROM t1
WHERE object_name IN (SELECT /*+ UNNEST */
t2.table_name
FROM t2);

/*+ USE_CONCAT */
对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.USE_CONCAT和NO_EXPAND成了互为"反函数"。
SELECT /*+ USE_CONCAT */ * FROM employees e WHERE manager_id = 108 OR department_id = 110;

/*+ USE_HASH */
将指定的表与其他行源通过哈希连接方式连接起来.
SELECT /*+ USE_HASH(l h) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 3500;

/*+ USE_MERGE */
将指定的表与其他行源通过合并排序连接方式连接起来.
SELECT /*+ USE_MERGE(employees departments) */ *
FROM employees, departments
WHERE employees.department_id = departments.department_id;

/*+ USE_NL */
将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.
SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
FROM orders h ,order_items l
WHERE l.order_id = h.order_id;

/*+ USE_NL_WITH_INDEX */
这项hint使CBO通过嵌套循环把特定的表格加入到另一原始行。只有在以下情况中，它才使用特定表格作为内部表格:如果没有指定标签，CBO必须可以使用一些标签，且这些标签至少有一个作为索引键值加入判断;反之，CBO必须能够使用至少有一个作为索引键值加入判断的标签。
SELECT /*+ USE_NL_WITH_INDEX(l item_product_ix) */ *
FROM Orders h, Order_Items l
WHERE l.Order_Id = h.Order_Id
AND l.Order_Id > 3500;

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

转载于:http://blog.itpub.net/82387/viewspace-1024813/