USE_MERGE - two inputs are seperately sorted and merged. No sort is required if the table has indexes. waits until both the sorts are performed.
Good when NO INDEXES are in the tables.
When both are almost equal in size. big or small.
USE_NL - for each record in the first table the second will be queried. so its good if there is an index in the second (driven) table. the best selective should be the driver.
When the tables are unequal in size.
When u need the FIRST_ROWS. (FOR OLTP)
USE_HASH - two tables are compared in memory. One is loaded in memory and hash functions are applied. Then the hashing function compares the other.
When a table is small.
For OLTP.
The table listed first in a set of tables of the same level, is the driving table.
For views, Hints can be given as view.tab_alias.table or the system generated alias like @SEL$2 t1 index_name. if u want to generate query block name, use QB_NAME(qb) FULL(@qb e) where e is the table alias.
If a hint specifies an unavailable access path, then the optimizer ignores it.
Three operations sort rows without grouping - order by (SORT ORDER BY), distinct and set operators (SORT UNIQUE NOSORT) and Joins (SORT JOIN)
Note: Union all is a row operation. Others uses set operations.
Grouping functions uses - SORT AGGREGATE and SORT GROUP BY if group by clause is specified.
Merging of query with view can be disabled with NO_MERGE
Oracle tries to combine subqueries with the rest of the query to get more execution options.
Hints:
OPT_PARAM - to set an initialization parameter for the duration of the current query only.
INDEX_SS - suggest skip scan when the leading column of a composite index is not used.
INDEX_ASC, INDEX_DESC - san in specified order.
INDEX_FFS - Fast full scan
FIRST_ROWS, ALL_ROWS - Instruct the optimizer to choose the best approach to give the first or all rows.
ROWID - Table access by index rowid.
AND_EQUAL - merge results of multiple index scans.
DRIVING_SITE- instructs the optimizer to execute the query at a different site than that selected by the database. DRIVING_SITE(tab1), if table is tabl1@r1, query is executed at rq.
CURSOR_SHARING_EXACT Will not replace literals in SQL statements with bind variables.
INDEX_JOIN - instructs the optimizer to use an index join as an access path.
USE_CONCAT - instructs the optimizer to transform combined OR-conditions in the WHERE clause of a query into a compound query using the UNION ALL set operator
REWRITE - instructs the optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration.
PUSH_PRED - forces join perdiacte into a view.
PUSH_SUBQ - forces non merged subqueries to be evaluated asap
FACT - instructs the optimizer that the table specified in tablespec should be considered as a fact table.
INDEX_COMBINE - instructs the optimizer to use a bitmap access path for the table.
MODEL_MIN_ANALYSIS - instructs the optimizer to omit some compile-time optimizations of spreadsheet rules.
MONITOR - forces real-time SQL monitoring for the query, even if the statement is not long running.
NATIVE_FULL_OUTER_JOIN - instructs the optimizer to use native full outer join, which is a native execution method based on a hash join.
NO_EXPAND - instructs the optimizer not to consider OR-expansion for queries having OR conditions or IN-lists in the WHERE clause.
STAR_TRANSFORMATION - instructs the optimizer to use the best plan in which the transformation has been used.
UNNEST - instructs the optimizer to unnest and merge the body of the subquery into the body of the query block that contains it, allowing the optimizer to consider them together when evaluating access paths and joins.
LEADING - instructs the optimizer to use the specified set of tables as the prefix in the execution plan.
ORDERED - instructs Oracle to join tables in the order in which they appear in the FROM clause.
PARALLEL ( tab, no_of_parallel_process, no_of instances) - each instance will span the no_of parallel process.
PARALLEL_INDEX - instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans, full scans, and fast full scans for partitioned indexes.
PQ_DISTRIBUTE - instructs the optimizer how to distribute rows of joined tables among producer and consumer query servers. Such distribution can improve the performance of parallel join operations.
Statistics are stored in:
DBA_TAB_STATISTICS
DBA_TAB_COL_STATISTICS
DBA_TAB_HISTOGRAMS