Session level:
Alter session set optimizer_goal = rule | first_ROWS | All_Rows | choose
Modify the session level optimization prompt.
Hints Tip:
- Tips will enable statements to enforce the cost-based optimizer (except for the reminder)
- Use the name of the table name in Hints
- Make sure the table is analyzed
Syntax: / * hint hint ... * /
(In PLSQL, the space between ' ' between the first characters of Hint is important / * all_rows * / is correct, but / * all_rows * / is wrong)
1 Optimizer Mode optimization mode:
First_ROWS, ALL_ROWS
Force CBO First Rows OR All Rows
Rule
Force Rule if Possible
ORDERED
Access table order ordered_predicates in the order in the start of the FROM clause
Use in the where clause to apply the what appear.
Does Not Apply Predicate Evaluation On Index Keys
.
2 subquery or view:
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 Join. (> = 7.2)
NO_MERGE (V)
Use this hint in a view to prevent it being merge 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 Reading method:
Full (tab)
Topic Scan (FTS) Cache (Tab)
IF Table within
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 table index (Tab Ndex)
Use an index to access table 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 in) statements. (> = 7.2). See (7.2 Requires
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 connection:
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 order of hint.
Use_merge (tab ..)
Use 'Tab' As the Driving Table in A Sort-Merge Join. Does Not Work Unless Ac.comPanied 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 Column 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 query options:
Parallel (Table,
Use Parallel Degree / Instances As Specified
Parallel_index (Table, [Index, [Degree [, Instances]]])
Parallel Range Conal 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 Uses 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
