oracle hint
Hint概述
基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。
此时就需要DBA进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从 而使语句高效的运行。例如，如果我们认为对于一个特定的语句，执行全表扫描要比执行索引扫描更有效，则我们就可以指示优化器使用全表扫描。在Oracle中，是通过为语句添加 Hints(提示)来实现干预优化器优化的目的。
不建议在代码中使用hint，在代码使用hint使得CBO无法根据实际的数据状态选择正确的执行计划。毕竟数据是不断变化的，10g以后的CBO也越来越完善，大多数情况下我们该让Oracle自行决定采用什么执行计划。
Oracle Hints是一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用Oracle Hints来实现：
1) 使用的优化器的类型
2) 基于代价的优化器的优化目标，是all_rows还是first_rows。
3) 表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。
4) 表之间的连接类型
5) 表之间的连接顺序
6) 语句的并行程度
除了”RULE”提示外，一旦使用的别的提示，语句就会自动的改为使用CBO优化器，此时如果你的数据字典中没有统计数据，就会使用缺省的统计数据。所以建议大家如果使用CBO或Hints提示，则最好对表和索引进行定期的分析。
如何使用Hints:
Hints只应用在它们所在sql语句块(statement block，由select、update、delete关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个sql语句，如果只在一个sql语句上有Hints，则该Hints不会影响另一个sql语句。
我们可以使用注释(comment)来为一个语句添加Hints，一个语句块只能有一个注释，而且注释只能放在SELECT, UPDATE, or DELETE关键字的后面
使用Oracle Hints的语法：
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...
注解：
1) DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。
2) “+”号表示该注释是一个Hints，该加号必须立即跟在”/*”的后面，中间不能有空格。
3) hint是下面介绍的具体提示之一，如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。
4) text 是其它说明hint的注释性文本
5)使用表别名。如果在查询中指定了表别名，那么提示必须也使用表别名。例如：select /*+ index(e,dept_idx) */ * from emp e;
6)不要在提示中使用模式名称：如果在提示中指定了模式的所有者，那么提示将被忽略。例如：
select /*+ index(scott.emp,dept_idx) */ * from emp
注意：如果你没有正确的指定Hints，Oracle将忽略该Hints，并且不会给出任何错误。
hint被忽略
如果CBO认为使用hint会导致错误的结果时，hint将被忽略，详见下例
SQL> select /*+ index(t t_ind) */ count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50366 | 57 (2)| 00:00:01 |
-------------------------------------------------------------------
因为我们是对记录求总数，且我们并没有在建立索引时指定不能为空，索引如果CBO选择在索引上进行count时，但索引字段上的值为空时，结果将不准确，故CBO没有选择索引。
SQL> select /*+ index(t t_ind) */ count(id) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 646498162
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 285 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FULL SCAN| T_IND | 50366 | 245K| 285 (1)| 00:00:04 |
--------------------------------------------------------------------------
因为我们只对id进行count，这个动作相当于count索引上的所有id值，这个操作和对表上的id字段进行count是一样的(组函数会忽略null值)
Hint的具体用法
和优化器相关的hint
1. /*+ALL_ROWS*/
表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
2. /*+FIRST_ROWS*/
表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
3. /*+CHOOSE*/
表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;
表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
4. /*+RULE*/
表明对语句块选择基于规则的优化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
5. /*+FULL(TABLE)*/
表明对表选择全局扫描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO=’SCOTT’;
6. /*+ROWID(TABLE)*/
提示明确表明对指定表根据ROWID进行访问.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>=’AAAAAAAAAAAAAA’
AND EMP_NO=’SCOTT’;
7. /*+CLUSTER(TABLE)*/
提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO=’TEC304′ AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
8. /*+INDEX(TABLE INDEX_NAME)*/
表明对表选择索引的扫描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX=’M';
9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明对表选择索引升序的扫描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=’SCOTT’;
10. /*+INDEX_COMBINE*/
为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE
11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
提示明确命令优化器使用索引作为访问路径.
例如:
SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL<60000;
12. /*+INDEX_DESC(TABLE INDEX_NAME)*/
表明对表选择索引降序的扫描方法.
例如:
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';
13. /*+INDEX_FFS(TABLE INDEX_NAME)*/
对指定的表执行快速全索引扫描,而不是全表扫描的办法.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';
14. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
提示明确进行执行规划的选择,将几个单列索引的扫描合起来.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';
15. /*+USE_CONCAT*/
对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.
例如:
SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
16. /*+NO_EXPAND*/
对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.
例如:
SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
17. /*+NOWRITE*/
禁止对查询块的查询重写操作.
18. /*+REWRITE*/
可以将视图作为参数.
19. /*+MERGE(TABLE)*/
能够对视图的各个查询进行相应的合并.
例如:
SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;
20. /*+NO_MERGE(TABLE)*/
对于有可合并的视图不再合并.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
21. /*+ORDERED*/
根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.
例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
22. /*+USE_NL(TABLE)*/
将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.
例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
23. /*+USE_MERGE(TABLE)*/
将指定的表与其他行源通过合并排序连接方式连接起来.
例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
24. /*+USE_HASH(TABLE)*/
将指定的表与其他行源通过哈希连接方式连接起来.
例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
25. /*+DRIVING_SITE(TABLE)*/
强制与ORACLE所选择的位置不同的表进行查询执行.
例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;
26. /*+LEADING(TABLE)*/
将指定的表作为连接次序中的首表.
27. /*+CACHE(TABLE)*/
当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
28. /*+NOCACHE(TABLE)*/
当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
29. /*+APPEND*/
直接插入到表的最后,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
30. /*+NOAPPEND*/
通过在插入语句生存期内停止并行模式来启动常规插入.
insert /*+noappend*/ into test1 select * from test4 ;
10、/*+ INDEX_SS(T T_IND) */
从9i开始，oracle引入了这种索引访问方式。当在一个联合索引中，某些谓词条件并不在联合索引的第一列时，可以通过Index Skip Scan来访问索引获得数据。当联合索引第一列的唯一值个数很少时，使用这种方式比全表扫描效率高。
SQL> create table t as select 1 id,object_name from dba_objects;
Table created.
SQL> insert into t select 2,object_name from dba_objects;
50366 rows created.
SQL> insert into t select 3,object_name from dba_objects;
50366 rows created.
SQL> insert into t select 4,object_name from dba_objects;
50366 rows created.
SQL> commit;
Commit complete.
SQL> create index t_ind on t(id,object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','T',cascade=>true);
PL/SQL procedure successfully completed.
执行全表扫描
SQL> select /*+ full(t) */ * from t where object_name='EMPLOYEES';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 135 | 215 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 5 | 135 | 215 (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='EMPLOYEES')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
942 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
不采用hint
SQL> select * from t where object_name='EMPLOYEES';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2869677071
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 135 | 5 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | T_IND | 5 | 135 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='EMPLOYEES')
filter("OBJECT_NAME"='EMPLOYEES')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17 consistent gets
1 physical reads
0 redo size
538 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
当全表扫描扫描了942个块，联合索引只扫描了17个数据块。可以看到联合索引的第一个字段的值重复率很高时，即使谓词中没有联合索引的第一个字段，依然会使用index_ss方式，效率远远高于全表扫描效率。但当第一个字段的值重复率很低时，使用index_ss的效率要低于全表扫描，读者可以自行实验
和表的关联相关的hint
/*+ leading(table_1,table_2) */
在多表关联查询中，指定哪个表作为驱动表，即告诉优化器首先要访问哪个表上的数据。
select /*+ leading(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ order */
让Oracle根据from后面表的顺序来选择驱动表，oracle建议使用leading，他更为灵活
select /*+ order */ t.* from t,t1 where t.id=t1.id;
/*+ use_nl(table_1,table_2) */
在多表关联查询中，指定使用nest loops方式进行多表关联。
select /*+ use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ use_hash(table_1,table_2) */
在多表关联查询中，指定使用hash join方式进行多表关联。
select /*+ use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ use_merge(table_1,table_2) */
在多表关联查询中，指定使用merge join方式进行多表关联。
select /*+ use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ no_use_nl(table_1,table_2) */
在多表关联查询中，指定不使用nest loops方式进行多表关联。
select /*+ no_use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ no_use_hash(table_1,table_2) */
在多表关联查询中，指定不使用hash join方式进行多表关联。
select /*+ no_use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ no_use_merge(table_1,table_2) */
在多表关联查询中，指定不使用merge join方式进行多表关联。
select /*+ no_use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;
其他常用的hint
/*+ parallel(table_name n) */
在sql中指定执行的并行度，这个值将会覆盖自身的并行度
select /*+ parallel(t 4) */ count(*) from t;
/*+ no_parallel(table_name) */
在sql中指定执行的不使用并行
select /*+ no_parallel(t) */ count(*) from t;
/*+ append */以直接加载的方式将数据加载入库
insert into t /*+ append */ select * from t;
/*+ dynamic_sampling(table_name n) */
设置sql执行时动态采用的级别，这个级别为0~10
select /*+ dynamic_sampling(t 4) */ * from t where id > 1234
/*+ cache(table_name) */
进行全表扫描时将table置于LRU列表的最活跃端，类似于table的cache属性
select /*+ full(employees) cache(employees) */ last_name from employees
附录hint表格
Hint 是Oracle 提供的一种SQL语法，它允许用户在SQL语句中插入相关的语法，从而影响SQL的执行方式。
因为Hint的特殊作用，所以对于开发人员不应该在代码中使用它，Hint 更像是Oracle提供给DBA用来分析问题的工具 。在SQL代码中使用Hint，可能导致非常严重的后果，因为数据库的数据是变化的，在某一时刻使用这个执行计划是最优的，在另一个时刻，却可能很差，这也是CBO 取代RBO的原因之一，规则是死的，而数据是时刻变化的，为了获得最正确的执行计划，只有知道表中数据的实际情况，通过计算各种执行计划的成本，则其最优，才是最科学的，这也是CBO的工作机制。 在SQL代码中加入Hint，特别是性能相关的Hint是很危险的做法。
Hints are comments in a SQL statement that pass instructions to the Oracle Database optimizer. The optimizer uses these hints to choose an execution plan for the statement, unless some condition exists that prevents the optimizer from doing so.
Hints were introduced in Oracle7, when users had little recourse if the optimizer generated suboptimal plans. Now Oracle provides a number of tools, including the SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to help you address performance problems that are not solved by the optimizer. Oracle strongly recommends that you use those tools rather than hints. The tools are far superior to hints, because when used on an ongoing basis, they provide fresh solutions as your data and database environment change.
Hints should be used sparingly, and only after you have collected statistics on the relevant tables and evaluated the optimizer plan without hints using the EXPLAIN PLAN statement. Changing database conditions as well as query performance enhancements in subsequent releases can have significant impact on how hints in your code affect performance.
The remainder of this section provides information on some commonly used hints. If you decide to use hints rather than the more advanced tuning tools, be aware that any short-term benefit resulting from the use of hints may not continue to result in improved performance over the long term.
Oracle 联机文档对Hint的说明：
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements006.htm#SQLRF50705
之前整理的一篇文章：
常见Oracle HINT的用法
http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4969702.aspx
在使用Hint时需要注意的一点是，并非任何时刻Hint都起作用。 导致HINT 失效的原因有如下2点：
（1） 如果CBO 认为使用Hint 会导致错误的结果时，Hint将被忽略。
如索引中的记录因为空值而和表的记录不一致时，结果就是错误的，会忽略hint。
（2） 如果表中指定了别名，那么Hint中也必须使用别名，否则Hint也会忽略。
Select /*+full(a)*/ * from t a; -- 使用hint
Select /*+full(t) */ * from t a; --不使用hint
根据hint的功能，可以分成如下几类：
| Hint | Hint 语法 |
| 优化器模式提示 | ALL_ROWS Hint |
| FIRST_ROWS Hint | |
| RULE Hint |
| 访问路径提示 | CLUSTER Hint |
| FULL Hint | |
| HASH Hint | |
| INDEX Hint | |
| NO_INDEX Hint | |
| INDEX_ASC Hint | |
| INDEX_DESC Hint | |
| INDEX_COMBINE Hint | |
| INDEX_FFS Hint | |
| INDEX_SS Hint | |
| INDEX_SS_ASC Hint | |
| INDEX_SS_DESC Hint | |
| NO_INDEX_FFS Hint | |
| NO_INDEX_SS Hint | |
| ORDERED Hint | |
| LEADING Hint | |
| USE_HASH Hint | |
| NO_USE_HASH Hint | |
| 表连接顺序提示 | USE_MERGE Hint |
| NO_USE_MERGE Hint | |
| USE_NL Hint | |
| USE_NL_WITH_INDEX Hint | |
| NO_USE_NL Hint | |
| 表关联方式提示 | PARALLEL Hint |
| NO_PARALLEL Hint | |
| PARALLEL_INDEX Hint | |
| NO_PARALLEL_INDEX Hint | |
| PQ_DISTRIBUTE Hint | |
| 并行执行提示 | FACT Hint |
| NO_FACT Hint | |
| MERGE Hint | |
| NO_MERGE Hint | |
| NO_EXPAND Hint | |
| USE_CONCAT Hint | |
| 查询转换提示 | REWRITE Hint |
| NO_REWRITE Hint | |
| UNNEST Hint | |
| NO_UNNEST Hint | |
| STAR_TRANSFORMATION Hint | |
| NO_STAR_TRANSFORMATION Hint | |
| NO_QUERY_TRANSFORMATION Hint | |
| APPEND Hint | |
| NOAPPEND Hint | |
| CACHE Hint | |
| NOCACHE Hint | |
| CURSOR_SHARING_EXACT Hint | |
| 其他Hint | DRIVING_SITE Hint |
| DYNAMIC_SAMPLING Hint | |
| PUSH_PRED Hint | |
| NO_PUSH_PRED Hint | |
| PUSH_SUBQ Hint | |
| NO_PUSH_SUBQ Hint | |
| PX_JOIN_FILTER Hint | |
| NO_PX_JOIN_FILTER Hint | |
| NO_XML_QUERY_REWRITE Hint | |
| QB_NAME Hint | |
| MODEL_MIN_ANALYSIS Hint |
一． 和优化器相关的Hint
Oracle 允许在系统级别，会话级别和SQL中（hint）优化器类型：
系统级别：
1: SQL>alter system set optimizer_mode=all_rows;
会话级别：
SQL>alter system set optimizer_mode=all_rows;
关于优化器，参考：
Oracle Optimizer CBO RBO
http://blog.csdn.net/tianlesoftware/archive/2010/08/19/5824886.aspx
1.1 ALL_ROWS 和FIRST_ROWS（n） -- CBO 模式
对于OLAP系统，这种系统中通常都是运行一些大的查询操作，如统计，报表等任务。 这时优化器模式应该选择ALL_ROWS. 对于一些分页显示的业务，就应该用FIRST_ROWS（n）。 如果是一个系统上运行这两种业务，那么就需要在SQL 用hint指定优化器模式。
如：
SQL> select /* + all_rows*/ * from dave;
SQL> select /* + first_rows(20)*/ * from dave;
1.2 RULE Hint -- RBO 模式
尽管Oracle 10g已经弃用了RBO，但是仍然保留了这个hint。 它允许在CBO 模式下使用RBO 对SQL 进行解析。
如：
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL> set autot trace exp;
SQL> select /*+rule */ * from dave;
执行计划
----------------------------------------------------------
Plan hash value: 3458767806
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| DAVE |
----------------------------------
Note
-----
- rule based optimizer used (consider using cbo) -- 这里提示使用RBO
SQL>
二． 访问路径相关的Hint
这一部分hint 将直接影响SQL 的执行计划，所以在使用时需要特别小心。 该类Hint对DBA分析SQL性能非常有帮助，DBA 可以让SQL使用不同的Hint得到不同的执行计划，通过比较不同的执行计划来分析当前SQL性能。
2.1 FULL Hint
该Hint告诉优化器对指定的表通过全表扫描的方式访问数据。
示例：
SQL> select /*+full(dave) */ * from dave;
要注意，如果表有别名，在hint里也要用别名， 这点在前面已经说明。
2.2 INDEX Hint
Index hint 告诉优化器对指定的表通过索引的方式访问数据，当访问索引会导致结果集不完整时，优化器会忽略这个Hint。
示例：
SQL> select /*+index(dave index_dave) */ * from dave where id>1;
谓词里有索引字段，才会用索引。
2.3 NO_INDEX Hint
No_index hint 告诉优化器对指定的表不允许使用索引。
示例：
SQL> select /*+no_index(dave index_dave) */ * from dave where id>1;
2.4 INDEX_DESC Hint
该Hint 告诉优化器对指定的索引使用降序方式访问数据，当使用这个方式会导致结果集不完整时，优化器将忽略这个索引。
示例：
SQL> select /*+index_desc(dave index_dave) */ * from dave where id>1;
2.5 INDEX_COMBINE Hint
该Hint告诉优化器强制选择位图索引，当使用这个方式会导致结果集不完整时，优化器将忽略这个Hint。
示例：
SQL> select /*+ index_combine(dave index_bm) */ * from dave;
2.6 INDEX_FFS Hint
该hint告诉优化器以INDEX_FFS(INDEX Fast Full Scan)的方式访问数据。当使用这个方式会导致结果集不完整时，优化器将忽略这个Hint。
示例：
SQL> select /*+ index_ffs(dave index_dave) */ id from dave where id>0;
2.7 INDEX_JOIN Hint
索引关联，当谓词中引用的列上都有索引时，可以通过索引关联的方式来访问数据。
示例：
SQL> select /*+ index_join(dave index_dave index_bm) */ * from dave where id>0 and name='安徽安庆';
2.8 INDEX_SS Hint
该Hint强制使用index skip scan 的方式访问索引，从Oracle 9i开始引入这种索引访问方式，当在一个联合索引中，某些谓词条件并不在联合索引的第一列时（或者谓词并不在联合索引的第一列时），可以通过index skip scan 来访问索引获得数据。 当联合索引第一列的唯一值很小时，使用这种方式比全表扫描效率要高。当使用这个方式会导致结果集不完整时，优化器将忽略这个Hint。
示例：
SQL> select /*+ index_ss(dave index_union) */ * from dave where id>0;
三． 表关联顺序的Hint
表之间的连接方式有三种。 具体参考blog：
多表连接的三种方式详解 HASH JOIN MERGE JOIN NESTED LOOP
http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5826546.aspx
3.1 LEADING hint
在一个多表关联的查询中，该Hint指定由哪个表作为驱动表，告诉优化器首先要访问哪个表上的数据。
示例：
SQL> select /*+leading(t1,t) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;
SQL> select /*+leading(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00
--------------------------------------------------------------------------------
3.2 ORDERED Hint
该hint 告诉Oracle 按照From后面的表的顺序来选择驱动表，Oracle 建议在选择驱动表上使用Leading，它更灵活一些。
SQL> select /*+ordered */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;
四． 表关联操作的Hint
4.1 USE_HASH,USE_NL,USE_MERGE hint
表之间的连接方式有三种。 具体参考blog：
多表连接的三种方式详解 HASH JOIN MERGE JOIN NESTED LOOP
http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5826546.aspx
这三种关联方式是多表关联中主要使用的关联方式。 通常来说，当两个表都比较大时，Hash Join的效率要高于嵌套循环（nested loops）的关联方式。
Hash join的工作方式是将一个表（通常是小一点的那个表）做hash运算，将列数据存储到hash列表中，从另一个表中抽取记录，做hash运算，到hash 列表中找到相应的值，做匹配。
Nested loops 工作方式是从一张表中读取数据，访问另一张表（通常是索引）来做匹配，nested loops适用的场合是当一个关联表比较小的时候，效率会更高。
Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配，因为merge join需要做更多的排序，所以消耗的资源更多。 通常来讲，能够使用merge join的地方，hash join都可以发挥更好的性能。
USE_HASH,USE_NL,USE_MERGE 这三种hint 就是告诉优化器使用哪种关联方式。
示例如下：
SQL> select /*+use_hash(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;
SQL> select /*+use_nl(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;
SQL> select /*+use_merge(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;
4.2 NO_USE_HASH,NO_USE_NL,NO_USE_MERGE HINT
分别禁用对应的关联方式。
示例：
SQL> select /*+no_use_merge(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;
SQL> select /*+no_use_nl(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;
SQL> select /*+no_use_hash(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;
五． 并行执行相关的Hint
5.1 PARALLEL HINT
指定SQL 执行的并行度，这个值会覆盖表自身设定的并行度，如果这个值为default，CBO使用系统参数值。
示例：
SQL> select /*+parallel(t 4) */ * from scott.dept t;
关于表的并行度，我们在创建表的时候可以指定，如：
SQL> CREATE TABLE Anqing
2 (
3 name VARCHAR2 (10)
4 )
5 PARALLEL 2;
表已创建。
SQL> select degree from all_tables where table_name = 'ANQING'; -- 查看表的并行度
DEGREE
--------------------
2
SQL> alter table anqing parallel(degree 3); -- 修改表的并行度
表已更改。
SQL> select degree from all_tables where table_name = 'ANQING';
DEGREE
--------------------
3
SQL> alter table anqing noparallel; -- 取消表的并行度
表已更改。
SQL> select degree from all_tables where table_name = 'ANQING';
DEGREE
--------------------
1
5.2 NO_PARALLEL HINT
在SQL中禁止使用并行。
示例：
SQL> select /*+ no_parallel(t) */ * from scott.dept t;
六． 其他方面的一些Hint
6.1 APPEND HINT
提示数据库以直接加载的方式（direct load）将数据加载入库。
示例：
Insert /*+append */ into t as select * from all_objects;
这个hint 用的比较多。 尤其在插入大量的数据，一般都会用此hint。
Oracle 插入大量数据
http://blog.csdn.net/tianlesoftware/archive/2009/10/30/4745144.aspx
6.2 DYNAMIC_SAMPLING HINT
提示SQL 执行时动态采样的级别。 这个级别从0-10，它将覆盖系统默认的动态采样级别。
示例：
SQL> select /*+ dynamic_sampling(t 2) */ * from scott.emp t where t.empno>0;
6.3 DRIVING_SITE HINT
这个提示在分布式数据库操作中比较有用，比如我们需要关联本地的一张表和远程的表：
Select /* + driving_site(departmetns) */ * from employees,departments@dblink where
employees .department_id = departments.department_id;
如果没有这个提示，Oracle 会在远端机器上执行departments 表查询，将结果送回本地，再和employees表关联。 如果使用driving_site(departments), Oracle将查询本地表employees，将结果送到远端，在远端将数据库上的表与departments关联，然后将查询的结果返回本地。
如果departments查询结果很大，或者employees查询结果很小，并且两张表关联之后的结果集很小，那么就可以考虑把本地的结果集发送到远端。 在远端执行完后，在将较小的最终结果返回本地。
6.4 CACHE HINT
在全表扫描操作中，如果使用这个提示，Oracle 会将扫描的到的数据块放到LRU（least recently Used： 最近很少被使用列表，是Oracle 判断内存中数据块活跃程度的一个算法）列表的最被使用端（数据块最活跃端），这样数据块就可以更长时间地驻留在内存当中。 如果有一个经常被访问的小表，这个设置会提高查询的性能；同时CACHE也是表的一个属性，如果设置了表的cache属性，它的作用和hint一样，在一次全表扫描之后，数据块保留在LRU列表的最活跃端。
示例：
SQL> select /*+full(t) cache (t) */ * from scott.emp;
小结：
对于DBA来讲，掌握一些Hint操作，在实际性能优化中有很大的好处，比如我们发现一条SQL的执行效率很低，首先我们应当查看当前SQL的执行计划，然后通过hint的方式来改变SQL的执行计划，比较这两条SQL 的效率，作出哪种执行计划更优，如果当前执行计划不是最优的，那么就需要考虑为什么CBO 选择了错误的执行计划。当CBO 选择错误的执行计划，我们需要考虑表的分析是否是最新的，是否对相关的列做了直方图，是否对分区表做了全局或者分区分析等因素。
--------------------------------------------------------------------------------------------------------------------------
其实Oracle的优化器有两种优化方式,
基于规则的优化方式(Rule-BasedOptimization，简称为RBO)10G以后将被废弃......
基于代价的优化方式(Cost-BasedOptimization，简称为CBO)
所以hint也不例外，除了/*+rule*/其他的都是CBO优化方式
RBO方式：
优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。
CBO方式：
它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因此应及时更新这些信息。
优化模式包括Rule、Choose、First rows、All rows四种方式：
Rule：基于规则的方式。
Choolse：默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式。
First Rows：它与Choose方式是类似的，所不同的是当一个表有统计信息时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时间。
All Rows:也就是我们所说的Cost的方式，当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走RBO的方式
Oracle在那配置默认的优化规则
A、Instance级别我们可以通过在initSID.ora文件中设定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS如果没设定OPTIMIZER_MODE参数则默认用的是Choose方式。
B、Sessions级别通过ALTER SESSION SETOPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来设定。
C、语句级别用Hint（/*+ ... */）来设定
为什么表的某个字段明明有索引，但执行计划却不走索引？
1、优化模式是all_rows的方式
2、表作过analyze，有统计信息
3、表很小，Oracle的优化器认为不值得走索引。
提示
不区分大小写,多个提示用空格分开
如：select /*+ hint1(tab1) hint2(TAB1idx1) */ col1, col2 from tab1 where col1='xxx';
如果表使用了别名,那么提示里也必须使用别名
如：select /*+hint1(t1) */ col1, col2 from tab1 t1 where col1='xxx';
如果使用同一个表的多个用,号分开
如： select /*+index(t1.A,t1.B) */ col1, col2
from tab1 t1
where col1='xxx';
oracle 10g hints知识,
10g数据库可以使用更多新的optimizer hints来控制优化行为。现在让我们快速解析一下这些强大的新hints：
1、spread_min_analysis
使用这一hint，你可以忽略一些关于如详细的关系依赖图分析等电子表格的编译时间优化规则。其他的一些优化，如创建过滤以有选择性的定位电子表格访问结构并限制修订规则等，得到了继续使用。
由于在规则数非常大的情况下，电子表格分析会很长。这一提示可以帮助我们减少由此产生的数以百小时计的编译时间。
例：
SELECT /*+ SPREAD_MIN_ANALYSIS */ ...
2、spread_no_analysis
通过这一hint，可以使无电子表格分析成为可能。同样，使用这一hint可以忽略修订规则和过滤产生。如果存在一个电子表格分析，编译时间可以被减少到最低程度。
例：
SELECT /*+ SPREAD_NO_ANALYSIS */ ...
3、use_nl_with_index
这项hint使CBO通过嵌套循环把特定的表格加入到另一原始行。只有在以下情况中，它才使用特定表格作为内部表格：如果没有指定标签，CBO必须可以使用一些标签，且这些标签至少有一个作为索引键值加入判断；反之，CBO必须能够使用至少有一个作为索引键值加入判断的标签。
例：
SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ ...
4、CARDINALITY
此hint定义了对由查询或查询部分返回的基数的评价。注意如果没有定义表格，基数是由整个查询所返回的总行数。
例：
SELECT /*+ CARDINALITY ( [tablespec] card ) */
5、SELECTIVITY
此hint定义了对查询或查询部分选择性的评价。如果只定义了一个表格，选择性是在所定义表格里满足所有单一表格判断的行部分。如果定义了一系列表格，选择性是指在合并以任何顺序满足所有可用判断的全部表格后，所得结果中的行部分。
例：
SELECT /*+ SELECTIVITY ( [tablespec] sel ) */
然而，注意如果hintsCARDINALITY和 SELECTIVITY都定义在同样的一批表格，二者都会被忽略。
6、no_use_nl
Hint no_use_nl使CBO执行循环嵌套，通过把指定表格作为内部表格，把每个指定表格连接到另一原始行。通过这一hint，只有hash join和sort-merge joins会为指定表格所考虑。
例：
SELECT /*+ NO_USE_NL ( employees ) */ ...
7、no_use_merge
此hint使CBO通过把指定表格作为内部表格的方式，拒绝sort-merge把每个指定表格加入到另一原始行。
例：
SELECT /*+ NO_USE_MERGE ( employees dept ) */ ...
8、no_use_hash
此hint使CBO通过把指定表格作为内部表格的方式，拒绝hash joins把每个指定表格加入到另一原始行。
例：
SELECT /*+ NO_USE_HASH ( employees dept ) */ ...
9、no_index_ffs
此hint使CBO拒绝对指定表格的指定标签进行fastfull-index scan。
Syntax: /*+ NO_INDEX_FFS (tablespecindexspec ) */
在SQL优化过程中常见HINT的用法(前10个比较常用,前3个最常用)：
1. /*+ INDEX */和 /*+INDEX(TABLE INDEX1, index2) */和 /*+ INDEX(tab1.col1 tab2.col2) */和 /*+ NO_INDEX */ 和 /*+ NO_INDEX(TABLE INDEX1, index2) */
表明对表选择索引的扫描方法.第一种不指定索引名是让oracle对表中可用索引比较并选择某个最佳索引;第二种是指定索引名且可指定多个索引;第三种是10g开始有的,指定列名,且表名可不用别名;第四种即全表扫描;第五种表示禁用某个索引,特别适合于准备删除某个索引前的评估操作.如果同时使用了INDEX和NO_INDEX则两个提示都会被忽略掉.
例如:SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */FROM BSEMPMS WHERE SEX='M';
2. /*+ ORDERED*/
FROM子句中默认最后一个表是驱动表,ORDERED将from子句中第一个表作为驱动表.特别适合于多表连接非常慢时尝试.
例如:SELECT /*+ORDERED */ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 CWHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
3. /*+PARALLEL(table1,DEGREE) */和 /*+ NO_PARALLEL(table1) */
该提示会将需要执行全表扫描的查询分成多个部分（并行度）执行,然后在不同的操作系统进程中处理每个部分.该提示还可用于DML语句.如果SQL里还有排序操作,进程数会翻倍,此外还有一个一个负责组合这些部分的进程,如下面的例子会产生9个进程.如果在提示中没有指定DEGREE,那么就会使用创建表时的默认值.该提示在默认情况下会使用APPEND提示. NO_PARALLEL是禁止并行操作,否则语句会使用由于定义了并行对象而产生的并行处理.
例如:select /*+PARALLEL(tab_test,4) */ col1, col2 from tab_test order by col2;
4. /*+FIRST_ROWS */ 和 /*+FIRST_ROWS(n) */
表示用最快速度获得第1/n行,获得最佳响应时间,使资源消耗最小化.
在update和delete语句里会被忽略,使用分组语句如groupby/distinct/intersect/minus/union时也会被忽略.
例如:SELECT /*+FIRST_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
5. /*+ RULE */
表明对语句块选择基于规则的优化方法.
例如:SELECT /*+RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
6. /*+FULL(TABLE) */
表明对表选择全局扫描的方法.
例如:SELECT /*+FULL(A) */ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';
7. /*+LEADING(TABLE) */
类似于ORDERED提示,将指定的表作为连接次序中的驱动表.
8. /*+USE_NL(TABLE1,TABLE2) */
将指定表与嵌套的连接的行源进行连接,以最快速度返回第一行再连接,与USE_MERGE刚好相反.
例如:SELECT /*+ORDERED USE_NL(BSEMPMS) */ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROMBSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
9. /*+ APPEND*/ 和 /*+ NOAPPEND*/
直接插入到表的最后,该提示不会检查当前是否有插入操作所需的块空间而是直接添加到新块中,所以可以提高速度.当然也会浪费些空间,因为它不会使用那些做了delete操作的块空间. NOAPPEND提示则相反,所以会取消PARALLEL提示的默认APPEND提示.
例如:insert /*+append */ into test1 select * from test4;
insert /*+ parallel(test1) noappend */ intotest1 select * from test4;
10. /*+USE_HASH(TABLE1,table2) */
将指定的表与其它行源通过哈希连接方式连接起来.为较大的结果集提供最佳响应时间.类似于在连接表的结果中遍历每个表上每个结果的嵌套循环,指定的hash表将被放入内存,所以需要有足够的内存（hash_area_size或pga_aggregate_target）才能保证语句正确执行,否则将在磁盘里进行.
例如:SELECT /*+USE_HASH(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
---------------------------------------------------------------------
11. /*+USE_MERGE(TABLE) */
将指定的表与其它行源通过合并排序连接方式连接起来.特别适合于那种在多个表大量行上进行集合操作的查询,它会将指定表检索到的的所有行排序后再被合并,与USE_NL刚好相反.
例如:SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
12. /*+ALL_ROWS */
表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.可能会限制某些索引的使用.
例如:SELECT /*+ALL+_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
13. /*+CLUSTER(TABLE) */
提示明确表明对指定表选择簇扫描的访问方法.如果经常访问连接表但很少修改它,那就使用集群提示.
例如:SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' ANDBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
14. /*+INDEX_ASC(TABLE INDEX1, INDEX2) */
表明对表选择索引升序的扫描方法.从8i开始,这个提示和INDEX提示功能一样,因为默认oracle就是按照升序扫描索引的,除非未来oracle还推出降序扫描索引.
例如:SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';
15. /*+INDEX_COMBINE(TABLE INDEX1, INDEX2) */
指定多个位图索引,对于B树索引则使用INDEX这个提示,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.
例如:SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */ * FROM BSEMPMS WHERESAL<5000000 AND HIREDATE<SYSDATE;
16. /*+INDEX_JOIN(TABLE INDEX1, INDEX2) */
合并索引, 所有数据都已经包含在这两个索引里, 不会再去访问表,比使用索引并通过rowid去扫描表要快5倍.
例如:SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI) */ SAL,HIREDATE FROM BSEMPMS WHERESAL<60000;
17. /*+INDEX_DESC(TABLE INDEX1, INDEX2) */
表明对表选择索引降序的扫描方法.
例如:SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';
18. /*+INDEX_FFS(TABLE INDEX_NAME) */
对指定的表执行快速全索引扫描,而不是全表扫描的办法.要求要检索的列都在索引里,如果表有很多列时特别适用该提示.
例如:SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM) */ * FROM BSEMPMS WHERE DPT_NO='TEC305';
19. /*+NO_EXPAND */
对于WHERE后面的OR或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展,缩短解析时间.
例如:SELECT /*+NO_EXPAND */ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
20. /*+DRIVING_SITE(TABLE) */
强制与ORACLE所选择的位置不同的表进行查询执行.特别适用于通过dblink连接的远程表.
例如:SELECT /*+DRIVING_SITE(DEPT) */ * FROM BSEMPMS,DEPT@BSDPTMS DEPT WHEREBSEMPMS.DPT_NO=DEPT.DPT_NO;
21. /*+CACHE(TABLE) */和 /*+NOCACHE(TABLE) */
当进行全表扫描时,CACHE提示能够将表全部缓存到内存中,这样访问同一个表的用户可直接在内存中查找数据.比较适合数据量小但常被访问的表,也可以建表时指定cache选项这样在第一次访问时就可以对其缓存. NOCACHE则表示对已经指定了CACHE选项的表不进行缓存.
例如:SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
22. /*+PUSH_SUBQ */
当SQL里用到了子查询且返回相对少的行时,该提示可以尽可能早对子查询进行评估从而改善性能,不适用于合并连接或带远程表的连接.
例如:select /*+PUSH_SUBQ */ emp.empno, emp.ename, itemno from emp, orders where emp.empno =orders.empno and emp.deptno = (select deptno from dept where loc='XXX');
远程连接其他数据库，注意判断数据库是否启动，或者是否有需要的表，否则会出错
23. /*+INDEX_SS(TABLE INDEX1,INDEX2) */
指示对特定表的索引使用跳跃扫描,即当组合索引的第一列不在where子句中时,让其使用该索引
参考资料
Oracle SQL hints
/*+hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */
All hints except /*+ rule */ cause the CBOto be used. Therefore, it is good practise to analyze the underlying tables ifhints are used (or the query is fully hinted. There should be no schema namesin hints. Hints must use aliases if alias names are used for table names. Sothe following is wrong:
select /*+ index(scott.emp ix_emp) */ fromscott.emp emp_alias
better:
select /*+ index(emp_alias ix_emp) */ ...from scott.emp emp_alias
Why using hints
It is a perfect valid question to ask whyhints should be used. Oracle comes with an optimizer that promises to optimizea query's execution plan. When this optimizer is really doing a good job, nohints should be required at all. Sometimes, however, the characteristics of thedata in the database are changing rapidly, so that the optimizer (or moreaccuratly, its statistics) are out of date. In this case, a hint could help. Itmust also be noted, that Oracle allows to lock the statistics when they lookideal which should make the hints meaningless again.
Hint categories
Hints can be categorized as follows:
Hints for Optimization Approaches andGoals,
Hints for Access Paths, Hints for QueryTransformations,
Hints for Join Orders,
Hints for Join Operations,
Hints for Parallel Execution,
Additional Hints
DocumentedHints
Hints for Optimization Approaches and Goals
ALL_ROWS
One of the hints that 'invokes' the Costbased optimizer
ALL_ROWS is usually used for batchprocessing or data warehousing systems.
FIRST_ROWS
One of thehints that 'invokes' the Cost based optimizer
FIRST_ROWS is usually used for OLTPsystems.
CHOOSE
One of thehints that 'invokes' the Cost based optimizer
This hint lets the server choose (betweenALL_ROWS and FIRST_ROWS, based on statistics gathered.
RULE
The RULE hint should be considereddeprecated as it is dropped from Oracle9i2.
See also the following initializationparameters: optimizer_mode, optimizer_max_permutations, optimizer_index_cost_adj,optimizer_index_caching and
Hints for Access Paths
CLUSTER
Performs a nested loop by the cluster indexof one of the tables.
FULL
Performs full table scan.
HASH
Hashes one table (full scan) and creates ahash index for that table. Then hashes other table and uses hash index to findcorresponding records. Therefore not suitable for < or > join conditions.
ROWID
Retrieves therow by rowid
INDEX
Specifying that index index_name should beused on table tab_name: /*+ index (tab_name index_name) */
Specifying that the index should be usedthe the CBO thinks is most suitable. (Not always a good choice).
Starting with Oracle 10g, the index hint can be described: /*+index(my_tab my_tab(col_1, col_2)) */. Using the index on my_tab that startswith the columns col_1 and col_2.
INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
INDEX_JOIN
NO_INDEX
AND_EQUAL
The AND_EQUAL hint explicitly chooses anexecution plan that uses an access path that merges the scans on severalsingle-column indexes
Hints for QueryTransformations
FACT
The FACT hint is used in the context of thestar transformation to indicate to the transformation that the hinted tableshould be considered as a fact table.
MERGE
NO_EXPAND
NO_EXPAND_GSET_TO_UNION
NO_FACT
NO_MERGE
NOREWRITE
REWRITE
STAR_TRANSFORMATION
USE_CONCAT
Hints for JoinOperations
DRIVING_SITE
HASH_AJ
HASH_SJ
LEADING
MERGE_AJ
MERGE_SJ
NL_AJ
NL_SJ
USE_HASH
USE_MERGE
USE_NL
Hints forParallel Execution
NOPARALLEL
PARALLEL
NOPARALLEL_INDEX
PARALLEL_INDEX
PQ_DISTRIBUTE
AdditionalHints
ANTIJOIN
APPEND
If a table or an index is specified withnologging, this hint applied with an insert statement produces a direct pathinsert which reduces generation of redo.
BITMAP
BUFFER
CACHE
CARDINALITY
CPU_COSTING
DYNAMIC_SAMPLING
INLINE
MATERIALIZE
NO_ACCESS
NO_BUFFER
NO_MONITORING
NO_PUSH_PRED
NO_PUSH_SUBQ
NO_QKN_BUFF
NO_SEMIJOIN
NOAPPEND
NOCACHE
OR_EXPAND
ORDERED
ORDERED_PREDICATES
PUSH_PRED
PUSH_SUBQ
QB_NAME
RESULT_CACHE (Oracle 11g)
SELECTIVITY
SEMIJOIN
SEMIJOIN_DRIVER
STAR
The STAR hint forces a star query plan tobe used, if possible. A star plan has the largest table in the query last inthe join order and joins it with a nested loops join on a concatenated index.The STAR hint applies when there are at least three tables, the large table'sconcatenated index has at least three columns, and there are no conflictingaccess or join method hints. The optimizer also considers differentpermutations of the small tables.
SWAP_JOIN_INPUTS
USE_ANTI
USE_SEMI
Undocumented hints:
BYPASS_RECURSIVE_CHECK
Workaraound forbug 1816154
BYPASS_UJVC
CACHE_CB
CACHE_TEMP_TABLE
CIV_GB
COLLECTIONS_GET_REFS
CUBE_GB
CURSOR_SHARING_EXACT
DEREF_NO_REWRITE
DML_UPDATE
DOMAIN_INDEX_NO_SORT
DOMAIN_INDEX_SORT
DYNAMIC_SAMPLING
DYNAMIC_SAMPLING_EST_CDN
EXPAND_GSET_TO_UNION
FORCE_SAMPLE_BLOCK
GBY_CONC_ROLLUP
GLOBAL_TABLE_HINTS
HWM_BROKERED
IGNORE_ON_CLAUSE
IGNORE_WHERE_CLAUSE
INDEX_RRS
INDEX_SS
INDEX_SS_ASC
INDEX_SS_DESC
LIKE_EXPAND
LOCAL_INDEXES
MV_MERGE
NESTED_TABLE_GET_REFS
NESTED_TABLE_SET_REFS
NESTED_TABLE_SET_SETID
NO_FILTERING
NO_ORDER_ROLLUPS
NO_PRUNE_GSETS
NO_STATS_GSETS
NO_UNNEST
NOCPU_COSTING
OVERFLOW_NOMOVE
PIV_GB
PIV_SSF
PQ_MAP
PQ_NOMAP
REMOTE_MAPPED
RESTORE_AS_INTERVALS
SAVE_AS_INTERVALS
SCN_ASCENDING
SKIP_EXT_OPTIMIZER
SQLLDR
SYS_DL_CURSOR
SYS_PARALLEL_TXN
SYS_RID_ORDER
TIV_GB
TIV_SSF
UNNEST
USE_TTT_FOR_GSETS
from:
https://blog.csdn.net/it_man/article/details/8185381
