Estadisticas

Oracle Hint


Meaning

+


Must be immediately after comment indicator, tells Oracle this is a list of hints.

ALL_ROWS


Use the cost based approach for best throughput.

CHOOSE


Default, if statistics are available will use cost, if not, rule.

FIRST_ROWS


Use the cost based approach for best response time.

RULE


Use rules based approach; this cancels anyother hints specified for this statement.

Access Method Oracle Hints:




CLUSTER(table)


This tells Oracle to do a cluster scan to access the table.

FULL(table)


This tells the optimizer to do a full scan of the specified table.

HASH(table)


Tells Oracle to explicitly choose the hash access method for the table.

HASH_AJ(table)


Transforms a NOT IN subquery toa hash anti-join.

ROWID(table)


Forces a rowid scan of the specified table.

INDEX(table [index])


Forces an index scan of the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.

INDEX_ASC(table [index])


Same as INDEX only performs an ascending search of the index chosen, this is functionally identical to the INDEX statement.

INDEX_DESC(table [index])


Same as INDEX except performs a descending search. If more than one table is accessed, this is ignored.

INDEX_COMBINE(table index)


Combines the bitmapped indexes on the table if the cost shows that to do so wouldgive better performance.

INDEX_FFS(table index)


Perform a fast full index scan rather than a table scan.

MERGE_AJ (table)


Transforms a NOT IN subquery into a merge anti-join.

AND_EQUAL(table index index [index index index])


This hint causes a merge on several single column indexes. Two must be specified, five can be.

NL_AJ


Transforms a NOT IN subquery into a NLanti-join (nested loop).

HASH_SJ(t1, t2)


Inserted into the EXISTS subquery; This converts the subquery into a special type of hash join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

MERGE_SJ (t1, t2)


Inserted into the EXISTS subquery; This convertsthe subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

NL_SJ


Inserted into the EXISTS subquery; This converts the subquery into a special type of nested loop join between t1 and t2 that preserves the semantics of thesubquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

Oracle Hints for join orders and transformations:




ORDERED


This hint forces tables to be joined in the order specified. If you know table X has fewer rows, then ordering it first may speed execution in a join.

STAR


Forces the largest table to be joinedlast using a nested loops join on the index.

STAR_TRANSFORMATION


Makes the optimizer use the best plan in which a start transformation is used.

FACT(table)


When performing a star transformation use the specified table as a fact table.

NO_FACT(table)


When performing a star transformation do not use the specified table as a fact table.

PUSH_SUBQ


This causes nonmergedsubqueries to be evaluated at the earliest possible point in the execution plan.

REWRITE(mview)


If possible forces the query to use the specified materialized view, if no materialized view is specified, the system chooses what it calculates is the appropriate view.

NOREWRITE


Turns off query rewrite for the statement, use it for when data returned must be concurrent and can't...