总结SQL语句中的优化提示

会话级：

ALTER SESSION SET optimizer_goal= rule | first_rows | all_rows | choose ;



Hints提示:

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

- Use ALIASES for the tablenames in the hints.

- Ensure tables are analyzed.

语法: /*+ 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


Access tables in the order of the FROM clause

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)  


Use FTS on tab

CACHE(tab)


If table within treat as if it had the CACHE option set. See . 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)


Use hash scan to access 'tab'

INDEX( tab index )  


Use 'index' to access 'tab'

INDEX_ASC( tab index )


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

INDEX_DESC( tab index )


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 , 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, [, ] )


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