hint

其实Oracle的优化器有两种优化方式,

基于规则的优化方式(Rule-Based Optimization，简称为RBO)

基于代价的优化方式(Cost-Based Optimization，简称为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 SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来设定。

    C、语句级别用Hint（/*+ ... */）来设定

为什么表的某个字段明明有索引，但执行计划却不走索引？

   1、优化模式是all_rows的方式

   2、表作过analyze，有统计信息

   3、表很小，Oracle的优化器认为不值得走索引。

提示

   不区分大小写,多个提示用空格分开

  如：select /*+ hint1(tab1) hint2(TAB1 idx1) */ 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 ) */

 

然而，注意如果hints CARDINALITY和 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拒绝对指定表格的指定标签进行fast full-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 C WHERE 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语句里会被忽略,使用分组语句如group by/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 FROM BSEMPMS,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 */ into test1 select * from test4;

 

10. /*+ USE_HASH(TABLE1,table2) */

将指定的表与其它行源通过哈希连接方式连接起来.为较大的结果集提供最佳响应时间.类似于在连接表的结果中遍历每个表上每个结果的嵌套循环, 指定的hash表将被放入内存,所以需要有足够的内存（hash_area_size或pga_aggregate_target）才能保证语句正确执行,否则将在磁盘里进行.

例如:SELECT /*+ USE_HASH(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

 

---------------------------------------------------------------------

 

11. /*+ USE_MERGE(TABLE) */

将指定的表与其它行源通过合并排序连接方式连接起来.特别适合于那种在多个表大量行上进行集合操作的查询,它会将指定表检索到的的所有行排序后再被合并, 与USE_NL刚好相反.

例如:SELECT /*+ USE_MERGE(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.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' AND BSEMPMS.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 WHERE SAL<5000000 AND HIREDATE<SYSDATE;

 

16. /*+ INDEX_JOIN(TABLE INDEX1, INDEX2) */

合并索引, 所有数据都已经包含在这两个索引里, 不会再去访问表,比使用索引并通过rowid去扫描表要快5倍.

例如:SELECT /*+ INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI) */ SAL,HIREDATE FROM BSEMPMS WHERE SAL<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 WHERE BSEMPMS.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 CBO to be used. Therefore, it is good practise to analyze the underlying tables if hints are used (or the query is fully hinted. There should be no schema names in hints. Hints must use aliases if alias names are used for table names. So the following is wrong:

select /*+ index(scott.emp ix_emp) */ from scott.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 why hints should be used. Oracle comes with an optimizer that promises to optimize a query's execution plan. When this optimizer is really doing a good job, no hints should be required at all. Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help. It must also be noted, that Oracle allows to lock the statistics when they look ideal which should make the hints meaningless again.

Hint categories

Hints can be categorized as follows:

Hints for Optimization Approaches and Goals,

Hints for Access Paths, Hints for Query Transformations,

Hints for Join Orders,

Hints for Join Operations,

Hints for Parallel Execution,

Additional Hints

 

Documented Hints

Hints for Optimization Approaches and Goals

ALL_ROWS

One of the hints that 'invokes' the Cost based optimizer

ALL_ROWS is usually used for batch processing or data warehousing systems.

FIRST_ROWS

One of the hints that 'invokes' the Cost based optimizer

FIRST_ROWS is usually used for OLTP systems.

CHOOSE

One of the hints that 'invokes' the Cost based optimizer

This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.

RULE

The RULE hint should be considered deprecated as it is dropped from Oracle9i2.

See also the following initialization parameters: 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 index of one of the tables.

FULL

Performs full table scan.

HASH

Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.

ROWID

Retrieves the row by rowid

INDEX

Specifying that index index_name should be used on table tab_name: /*+ index (tab_name index_name) */

Specifying that the index should be used the 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 starts with 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 an execution plan that uses an access path that merges the scans on several single-column indexes

 

Hints for Query Transformations

FACT

The FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should 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 Join Operations

DRIVING_SITE

HASH_AJ

HASH_SJ

LEADING

MERGE_AJ

MERGE_SJ

NL_AJ

NL_SJ

USE_HASH

USE_MERGE

USE_NL

 

Hints for Parallel Execution

NOPARALLEL

PARALLEL

NOPARALLEL_INDEX

PARALLEL_INDEX

PQ_DISTRIBUTE

 

Additional Hints

ANTIJOIN

APPEND

If a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert 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 to be used, if possible. A star plan has the largest table in the query last in the 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's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.

SWAP_JOIN_INPUTS

USE_ANTI

USE_SEMI

 

Undocumented hints:

BYPASS_RECURSIVE_CHECK

Workaraound for bug 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:http://wenku.baidu.com/view/f511574e767f5acfa1c7cd0d.html