一．和优化器相关的Hint

Oracle 允许在系统级别，会话级别和SQL中（hint）优化器类型：

系统级别：
SQL>alter system set optimizer_mode=all_rows;

会话级别：
SQL>alter system set optimizer_mode=all_rows;

1.1 ALL_ROWS 和FIRST_ROWS（n） -- CBO 模式

对于OLAP系统，这种系统中通常都是运行一些大的查询操作，如统计，报表等任务。这时优化器模式应该选择ALL_ROWS. 对于一些分页显示的业务，就应该用FIRST_ROWS（n）。如果是一个系统上运行这两种业务，那么就需要在SQL 用hint指定优化器模式。

如：

SQL> select /* + all_rows*/ * from t;

SQL> select /* + first_rows(20)*/ * from t;

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 t;

执行计划

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

Plan hash value: 3458767806

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS FULL| T |

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

Note

-----

- rule based optimizer used (consider using cbo) -- 这里提示使用RBO

二．访问路径相关的Hint

这一部分hint 将直接影响SQL 的执行计划，所以在使用时需要特别小心。该类Hint对DBA分析SQL性能非常有帮助，DBA 可以让SQL使用不同的Hint得到不同的执行计划，通过比较不同的执行计划来分析当前SQL性能。

2.1 FULL Hint

该Hint告诉优化器对指定的表通过全表扫描的方式访问数据。

示例：

SQL> select /*+full(t) */ * from t;

要注意，如果表有别名，在hint里也要用别名，这点在前面已经说明。

2.2 INDEX Hint

Index hint 告诉优化器对指定的表通过索引的方式访问数据，当访问索引会导致结果集不完整时，优化器会忽略这个Hint。

示例：

SQL> select /*+index(t index_t) */ * from t where id>1;

谓词里有索引字段，才会用索引。

2.3 NO_INDEX Hint

No_index hint 告诉优化器对指定的表不允许使用索引。

示例：

SQL> select /*+no_index(t index_t) */ * from t where id>1;

2.4 INDEX_DESC Hint

该Hint 告诉优化器对指定的索引使用降序方式访问数据，当使用这个方式会导致结果集不完整时，优化器将忽略这个索引。

示例：

SQL> select /*+index_desc(t index_t) */ * from t where id>1;

2.5 INDEX_COMBINE Hint

该Hint告诉优化器强制选择位图索引，当使用这个方式会导致结果集不完整时，优化器将忽略这个Hint。

示例：

SQL> select /*+ index_combine(t index_bm) */ * from t;

2.6 INDEX_FFS Hint

该hint告诉优化器以INDEX_FFS(INDEX Fast Full Scan)的方式访问数据。当使用这个方式会导致结果集不完整时，优化器将忽略这个Hint。

示例：

SQL> select /*+ index_ffs(t index_t) */ id from t where id>0;

2.7 INDEX_JOIN Hint

索引关联，当谓词中引用的列上都有索引时，可以通过索引关联的方式来访问数据。

示例：

SQL> select /*+ index_join(t index_t index_bm) */ * from t where id>0 and name='安徽安庆';

2.8 INDEX_SS Hint

该Hint强制使用index skip scan 的方式访问索引，从Oracle 9i开始引入这种索引访问方式，当在一个联合索引中，某些谓词条件并不在联合索引的第一列时（或者谓词并不在联合索引的第一列时），可以通过index skip scan 来访问索引获得数据。当联合索引第一列的唯一值很小时，使用这种方式比全表扫描效率要高。当使用这个方式会导致结果集不完整时，优化器将忽略这个Hint。

示例：

SQL> select /*+ index_ss(t index_union) */ * from t where id>0;

三．表关联顺序的Hint
多表连接的三种方式详解 HASH JOIN/ MERGE JOIN /NESTED LOOP

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;

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

多表连接的三种方式详解 HASH JOIN MERGE JOIN NESTED LOOP

这三种关联方式是多表关联中主要使用的关联方式。通常来说，当两个表都比较大时，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。

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;

===========================================================================
如果要在系统里加hint, 动态采样+dbms_xplan.display_cursor是个捷径:

运行完这个SQL语句之后：
select /*+ dynamic_sampling(4) */ t1.x, t2.x from t1,t2
           where t1.x=1 and t1.y=1 and t2.x=t1.x;

我会马上跟一个：
select * from table(dbms_xplan.display_cursor(null, 0 , 'advanced'));
这样就可以得到为了实现这个比较好的计划所需要的所有的Hint
/*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."X"))
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."X" "T1"."Y"))
      LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      USE_HASH(@"SEL$1" "T1"@"SEL$1")
    END_OUTLINE_DATA
*/
这个SQL最后留下这么几个hint就够了：
/*+
      INDEX("T2" ("T2"."X"))
      INDEX("T1" ("T1"."X" "T1"."Y"))
      LEADING( "T2" "T1")
      USE_HASH("T1")
*/
有的SQL一跑就是几天，甚至会影响整个系统，你优化的时候不可能把它跑一遍，所以也不能用dbms_xplan.display_cursor，那你可以考虑这个方法:
alter session set events '10132 trace name context forever, level 1';

set autotrace traceonly exp
select /*+ dynamic_sampling(4) */ t1.x, t2.x from t1,t2
           where t1.x=1 and t1.y=1 and t2.x=t1.x;

于是在trace里你可以看到下面的内容：
sql_id=62dhqxdq49t6a.
Current SQL statement for this session:
EXPLAIN PLAN SET STATEMENT_ID='PLUS1777' FOR select /*+ dynamic_sampling(4) */ t1.x, t2.x from t1,t2
           where t1.x=1 and t1.y=1 and t2.x=t1.x

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id | Operation          | Name    | Rows | Bytes | Cost | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |   110 |           |
| 1   | HASH JOIN         |         | 4897K |   33M |   110 | 00:00:02 |
| 2   |   INDEX RANGE SCAN | T2_X    | 1011 | 2022 |     4 | 00:00:01 |
| 3   |   INDEX RANGE SCAN | T1_XY   | 4963 |   24K |    19 | 00:00:01 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("T2"."X"="T1"."X")
2 - access("T2"."X"=1)
3 - access("T1"."X"=1 AND "T1"."Y"=1)

Content of other_xml column
===========================
db_version     : 10.2.0.1
parse_schema   : HR
dynamic_sampling: yes
plan_hash      : 224376697
Outline Data:
/*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."X"))
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."X" "T1"."Y"))
      LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      USE_HASH(@"SEL$1" "T1"@"SEL$1")
    END_OUTLINE_DATA
*/

附录：

Hints for Optimization Approaches and Goals
ALL_ROWS	The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
FIRST_ROWS	The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row). In newer Oracle version you should give a parameter with this hint: FIRST_ROWS(n) means that the optimizer will determine an executionplan to give a fast response for returning the first n rows.
CHOOSE	The CHOOSE hint causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement
RULE	The RULE hint explicitly chooses rule-based optimization for a statement block. This hint also causes the optimizer to ignore any other hints specified for the statement block. The RULE hint does not work any more in Oracle 10g.
Hints for Access Paths
FULL	The FULL hint explicitly chooses a full table scan for the specified table. The syntax of the FULL hint is FULL(table) where table specifies the alias of the table (or table name if alias does not exist) on which the full table scan is to be performed.
ROWID	The ROWID hint explicitly chooses a table scan by ROWID for the specified table. The syntax of the ROWID hint is ROWID(table) where table specifies the name or alias of the table on which the table access by ROWID is to be performed. (This hint depricated in Oracle 10g)
CLUSTER	The CLUSTER hint explicitly chooses a cluster scan to access the specified table. The syntax of the CLUSTER hint is CLUSTER(table) where table specifies the name or alias of the table to be accessed by a cluster scan.
HASH	The HASH hint explicitly chooses a hash scan to access the specified table. The syntax of the HASH hint is HASH(table) where table specifies the name or alias of the table to be accessed by a hash scan.
HASH_AJ	The HASH_AJ hint transforms a NOT IN subquery into a hash anti-join to access the specified table. The syntax of the HASH_AJ hint is HASH_AJ(table) where table specifies the name or alias of the table to be accessed.(depricated in Oracle 10g)
INDEX	The INDEX hint explicitly chooses an index scan for the specified table. The syntax of the INDEX hint is INDEX(table index) where:table specifies the name or alias of the table associated with the index to be scanned and index specifies an index on which an index scan is to be performed. This hint may optionally specify one or more indexes:
NO_INDEX	The NO_INDEX hint explicitly disallows a set of indexes for the specified table. The syntax of the NO_INDEX hint is NO_INDEX(table index)
INDEX_ASC	The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values.
INDEX_COMBINE	If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer will use on the table whatever boolean combination of bitmap indexes has the best cost estimate. If certain indexes are given as arguments, the optimizer will try to use some boolean combination of those particular bitmap indexes. The syntax of INDEX_COMBINE is INDEX_COMBINE(table index).
INDEX_JOIN	Explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.
INDEX_DESC	The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in descending order of their indexed values.
INDEX_FFS	This hint causes a fast full index scan to be performed rather than a full table.
NO_INDEX_FFS	Do not use fast full index scan (from Oracle 10g)
INDEX_SS	Exclude range scan from query plan (from Oracle 10g)
INDEX_SS_ASC	Exclude range scan from query plan (from Oracle 10g)
INDEX_SS_DESC	Exclude range scan from query plan (from Oracle 10g)
NO_INDEX_SS	The NO_INDEX_SS hint causes the optimizer to exclude a skip scan of the specified indexes on the specified table. (from Oracle 10g)
Hints for Query Transformations
NO_QUERY_TRANSFORMATION	Prevents the optimizer performing query transformations. (from Oracle 10g)
USE_CONCAT	The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Normally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.
NO_EXPAND	The NO_EXPAND hint prevents the optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.
REWRITE	The REWRITE hint forces the optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost.
NOREWRITE / NO_REWRITE	In Oracle 10g renamed to NO_REWRITE. The NOREWRITE/NO_REWRITE hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED.
MERGE	The MERGE hint lets you merge views in a query.
NO_MERGE	The NO_MERGE hint causes Oracle not to merge mergeable views. This hint is most often used to reduce the number of possible permutations for a query and make optimization faster.
FACT	The FACT hint indicated that the table should be considered as a fact table. This is used in the context of the star transformation.
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.
STAR_TRANSFORMATION	The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a query optimization decision to use the best plan generated without the transformation, instead of the best plan for the transformed query.
NO_STAR_TRANSFORMATION	Do not use star transformation (from Oracle 10g)
UNNEST	The UNNEST hint specifies subquery unnesting.
NO_UNNEST	Use of the NO_UNNEST hint turns off unnesting for specific subquery blocks.
Hints for Join Orders
LEADING	Give this hint to indicate the leading table in a join. This will indicate only 1 table. If you want to specify the whole order of tables, you can use the ORDERED hint. Syntax: LEADING(table)
ORDERED	The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause. If you omit the ORDERED hint from a SQL statement performing a join , the optimizer chooses the order in which to join the tables. You may want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information would allow you to choose an inner and outer table better than the optimizer could.
Hints for Join Operations
USE_NL	The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table. The syntax of the USE_NL hint is USE_NL(table table) where table is the name or alias of a table to be used as the inner table of a nested loops join.
NO_USE_NL	Do not use nested loop (from Oracle 10g)
USE_NL_WITH_INDEX	Specifies a nested loops join. (from Oracle 10g)
USE_MERGE	The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join. The syntax of the USE_MERGE hint is USE_MERGE(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a sort-merge join.
NO_USE_MERGE	Do not use merge (from Oracle 10g)
USE_HASH	The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join. The syntax of the USE_HASH hint is USE_HASH(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join.
NO_USE_HASH	Do not use hash (from Oracle 10g)
Hints for Parallel Execution
PARALLEL	The PARALLEL hint allows you to specify the desired number of concurrent query servers that can be used for the query. The syntax is PARALLEL(table number number). The PARALLEL hint must use the table alias if an alias is specified in the query. The PARALLEL hint can then take two values separated by commas after the table name. The first value specifies the degree of parallelism for the given table, the second value specifies how the table is to be split among the instances of a parallel server. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters (described in a later section) to determine the default degree of parallelism.
NOPARALLEL / NO_PARALLEL	The NOPARALLEL hint allows you to disable parallel scanning of a table, even if the table was created with a PARALLEL clause. In Oracle 10g this hint was renamed to NO_PARALLEL.
PQ_DISTRIBUTE	The PQ_DISTRIBUTE hint improves the performance of parallel join operations. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make.
NO_PARALLEL_INDEX	The NO_PARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation.
Additional Hints
APPEND	When the APPEND hint is used with the INSERT statement, data is appended to the table. Existing free space in the block is not used. 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.
NOAPPEND	Overrides the append mode.
CACHE	The CACHE hint specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. In the following example, the CACHE hint overrides the table default caching specification.
NOCACHE	The NOCACHE hint specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.
PUSH_PRED	The PUSH_PRED hint forces pushing of a join predicate into the view.
NO_PUSH_PRED	The NO_PUSH_PRED hint prevents pushing of a join predicate into the view.
PUSH_SUBQ	The PUSH_SUBQ hint causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan.
NO_PUSH_SUBQ	The NO_PUSH_SUBQ hint causes non-merged subqueries to be evaluated as the last step in the execution plan.
QB_NAME	Specifies a name for a query block. (from Oracle 10g)
CURSOR_SHARING_EXACT	Oracle can replace literals in SQL statements with bind variables, if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint causes this behavior to be switched off. In other words, Oracle executes the SQL statement without any attempt to replace literals by bind variables.
DRIVING_SITE	The DRIVING_SITE hint forces query execution to be done for the table at a different site than that selected by Oracle
DYNAMIC_SAMPLING	The DYNAMIC_SAMPLING hint lets you control dynamic sampling to improve server performance by determining more accurate predicate selectivity and statistics for tables and indexes. You can set the value of DYNAMIC_SAMPLING to a value from 0 to 10. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied. Sampling defaults to cursor level unless you specify a table.
SPREAD_MIN_ANALYSIS	This hint omits some of the compile time optimizations of the rules, mainly detailed dependency graph analysis, on spreadsheets. Some optimizations such as creating filters to selectively populate spreadsheet access structures and limited rule pruning are still used. (from Oracle 10g)
Hints with unknown status
MERGE_AJ	The MERGE_AJ hint transforms a NOT IN subquery into a merge anti-join to access the specified table. The syntax of the MERGE_AJ hint is MERGE_AJ(table) where table specifies the name or alias of the table to be accessed.(depricated in Oracle 10g)
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. The syntax of the AND_EQUAL hint is AND_EQUAL(table index index) where table specifies the name or alias of the table associated with the indexes to be merged. and index specifies an index on which an index scan is to be performed. You must specify at least two indexes. You cannot specify more than five. (depricated in Oracle 10g)
STAR	The STAR hint forces the large table to be joined last using a nested loops join on the index. The optimizer will consider different permutations of the small tables. (depricated in Oracle 10g)
BITMAP	Usage: BITMAP(table_name index_name) Uses a bitmap index to access the table. (depricated ?)
HASH_SJ	Use a Hash Anti-Join to evaluate a NOT IN sub-query. Use this hint in the sub-query, not in the main query. Use this when your high volume NOT IN sub-query is using a FILTER or NESTED LOOPS join. Try MERGE_AJ if HASH_AJ refuses to work.(depricated in Oracle 10g)
NL_SJ	Use a Nested Loop in a sub-query. (depricated in Oracle 10g)
NL_AJ	Use an anti-join in a sub-query. (depricated in Oracle 10g)
ORDERED_PREDICATES	(depricated in Oracle 10g)
EXPAND_GSET_TO_UNION	(depricated in Oracle 10g)

ITPub博客

常用hint

最新文章