SQL语句中的优化提示Hints的总结

转载 2004年07月01日 10:05:00
 
会话级:
ALTER SESSION SET optimizer_goal= rule | first_rows | all_rows | choose ;
修改会话级的优化提示。  
Hints提示:

  - 提示将使语句强制执行基于成本的优化器 (除了提示RULE外)

  - 在hints中使用表名的别名

  - 确保表都被分析过了

语法: /*+ HINT  HINT  ... */ 

  (PLSQL中, '+'hint的第一个字符之间的空格是很重要的
    /*+ ALL_ROWS */
是正确的,但/*+ALL_ROWS */ 是错误的)

1 Optimizer Mode优化模式:

FIRST_ROWS, ALL_ROWS

Force CBO first rows or all rows

RULE

Force Rule if possible

ORDERED

按照在FROM 子句中的先后顺序访问表

ORDERED_PREDICATES

Use in the WHERE clause to apply predicates in the order that they appear.

Does not apply predicate evaluation on index keys

               .

2 子查询或视图:

PUSH_SUBQ

Causes all subqueries in a query block to be executed at the earliest possible time. 

Normally subqueries are executed as the last is applied is outerjoined or remote or joined with a merge join. (>=7.2)

NO_MERGE(v)

Use this hint in a VIEW to PREVENT it being merged into the parent query. (>=7.2) or use NO_MERGE(v) in parent query block to prevent view V being merged

MERGE(v)

Do merge view V

MERGE_AJ(v)   }

Put hint in a NOT IN subquery to perform (>=7.3)

HASH_AJ(v)    }

SMJ anti-join or hash anti-join. (>=7.3)

Eg: SELECT .. WHERE deptno is not null    AND deptno NOT IN(SELECT /*+ HASH_AJ */ deptno ...)

HASH_SJ(v)    }

Transform EXISTS subquery into HASH or MERGE

MERGE_SJ(v)     }

semi-join to access "v"

PUSH_JOIN_PRED(v)      

Push join predicates into view V

NO_PUSH_JOIN_PRED(v)   

Do NOT push join predicates

                

3 读取方式:

FULL(tab)               

  对表实行全表扫描(FTS)

CACHE(tab)

If table within <Parameter:CACHE_SIZE_THRESHOLD> treat as if it had the CACHE option set. See <Parameter:CACHE_SIZE_THRESHOLD>. Only applies if FTS used.

NOCACHE(tab)

Do not cache table even if it has CACHE option set. Only relevant for FTS

ROWID(tab)             

Access tab by ROWID directly

SELECT /*+ ROWID( table ) */ ... FROM tab WHERE ROWID between '&1' and '&2';

CLUSTER(tab)

Use cluster scan to access 'tab'

HASH(tab)

使用hash scan来访问表

INDEX( tab ndex )     

使用索引来访问表

INDEX_ASC( tab ndex ) 

Use 'index' to access 'tab' for range scan.

INDEX_DESC( tab ndex )

Use descending index range scan (Join problems pre 7.3)

INDEX_FFS( tab index)

Index fast full scan - rather than FTS.

INDEX_COMBINE( tab i1.. i5 )

Try to use some boolean combination of bitmap index/s i1,i2 etc

AND_EQUAL(tab i1.. i5 )

Merge scans of 2 to 5 single column indexes.

USE_CONCAT

Use concatenation (Union All) for OR (or IN) statements. (>=7.2). See(7.2 requires <Event:10078>, 7.3 no hint req)

NO_EXPAND

Do not perform OR-expansion (Ie: Do not use Concatenation).

DRIVING_SITE(table)    

Forces query execution to be done at the site where "table" resides

                                                               

  4 连接:

USE_NL(tab)            

        Use table 'tab' as the driving table in a Nested Loops join.  If the driving row source is a combination of tables name one of the tables in the inner join and the NL should drive off the entire row-source. Does not work unless accompanied by an ORDERED hint.

USE_MERGE(tab..)       

        Use 'tab' as the driving table in a sort-merge join. Does not work unless accompanied by an ORDERED hint.

USE_HASH(tab1 tab2)

Join each specified table with another row source with a hash join. 'tab1' is joined to previous row source using a hash join. (>=7.3)

STAR

Force a star query plan 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 3 tables and the large table's concatenated index has at least 3 columns and there are no conflicting access or join method hints.   (>=7.3)

STAR_TRANSFORMATION

Use best plan containing a STAR transformation(if there is one)

                                                                

5 并行查询选项:

PARALLEL ( table, <degree> [, <instances>] )

Use parallel degree / instances as specified

PARALLEL_INDEX(table, [ index, [ degree [,instances] ] ]  )

Parallel range scan for partitioned index

PQ_DISTRIBUTE(tab,out,in)

How to distribute rows from tab in a PQ(out/in may be HASH/NONE/BROADCAST/PARTITION)

NOPARALLEL(table)

No parallel on "table"

NOPARALLEL_INDEX(table [,index])

 

 

6 Miscellaneous

APPEND

Only valid for INSERT .. SELECT. Allows INSERT to work like direct load or to perform parallel insert.

NOAPPEND

Do not use INSERT APPEND functionality

REWRITE(v1[,v2])

8.1+ With a view list use eligible materialized view Without view list use any eligible MV

NOREWRITE

8.1+ Do not rewrite the query

 

Oracle Hints详解

在向大家详细介绍Oracle Hints之前,首先让大家了解下Oracle Hints是什么,然后全面介绍Oracle Hints,希望对大家有用。基于代价的优化器是很聪明的,在绝大多数情况下它会选择...
  • wb96a1007
  • wb96a1007
  • 2011年12月23日 20:30
  • 24251

Oracle的优化器(Optimizer) (CBO优化) 分享

Oracle的优化器(Optimizer) (CBO优化)   Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行 计划去执行。分析语句的...
  • zhifeiyu2008
  • zhifeiyu2008
  • 2012年12月22日 21:18
  • 932

Oracle--optimizer_mode

Oracle使用Optimizer_mode参数来控制优化器的偏好,9i常用的几个参数有:first_rows,all_rows,first_rows_N,rule ,choose等。而10g少了ru...
  • waterxcfg304
  • waterxcfg304
  • 2013年04月02日 13:43
  • 5159

SQL语句中的优化提示Hints的总结

 会话级:ALTER SESSION SET optimizer_goal= rule | first_rows | all_rows | choose ;修改会话级的优化提示。  Hints提示: ...
  • godblessu
  • godblessu
  • 2004年07月01日 10:05
  • 1582

oracle hints (摘录)

oracle hints      今天是2013-10-08,收藏一下oracle hints应用。呵呵 在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQL优化过程中...
  • xiaohai20102010
  • xiaohai20102010
  • 2014年01月24日 14:31
  • 689

SQL语句中的优化提示Hints的总结

会话级:ALTER SESSION SET optimizer_goal= rule | first_rows | all_rows | choose ;修改会话级的优化提示。 Hints提示: - ...
  • gloomyboyo
  • gloomyboyo
  • 2008年06月21日 17:17
  • 524

SQL语句中的NOT IN 的优化

SQL语句中的IN和NOT IN子查询理解起来很直观,和实际的业务也很匹配,所有经常被开发人员使用,数据量不大的表还好,如果数据量太大将导致性能问题。 原SQL: SELECT COUNT(DISTI...
  • zhouree
  • zhouree
  • 2015年04月22日 10:07
  • 1498

oracle sql 语句暗示 hints

  • 2008年11月26日 22:02
  • 166KB
  • 下载

oracle hint语句

转载:http://www.cnblogs.com/sopost/archive/2010/10/11/2190076.html 在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQ...
  • Linzhongyilisha
  • Linzhongyilisha
  • 2016年08月19日 14:34
  • 308

SQL语句中不等号(!=,<>)

转自:http://www.cnblogs.com/yongtaiyu/archive/2013/01/16/2863118.html 关于SQL命令中不等号(!=, 比较两个表达...
  • amberinheart
  • amberinheart
  • 2016年06月03日 11:08
  • 4400
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL语句中的优化提示Hints的总结
举报原因:
原因补充:

(最多只允许输入30个字)