|
is transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them. |
| NO_EXPAND |
The NO_EXPAND hint prevents the optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it. |
| REWRITE |
The REWRITE hint forces the optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost. |
| NOREWRITE / NO_REWRITE |
In Oracle 10g renamed to NO_REWRITE. The NOREWRITE/NO_REWRITE hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED. |
| MERGE |
The MERGE hint lets you merge views in a query. |
| NO_MERGE |
The NO_MERGE hint causes Oracle not to merge mergeable views. This hint is most often used to reduce the number of possible permutations for a query and make optimization faster. |
| FACT |
The FACT hint indicated that the table should be considered as a fact table. This is used in the context of the star transformation. |
| NO_FACT |
The NO_FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table. |
| STAR_TRANSFORMATION |
The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a query optimization decision to use the best plan generated without the transformation, instead of the best plan for the transformed query. |
| NO_STAR_TRANSFORMATION |
Do not use star transformation (from Oracle 10g) |
| UNNEST |
The UNNEST hint specifies subquery unnesting. |
| NO_UNNEST |
Use of the NO_UNNEST hint turns off unnesting for specific subquery blocks. |
? Hints for Join Orders ? |
| LEADING |
Give this hint to indicate the leading table in a join. This will indicate only 1 table. If you want to specify the whole order of tables, you can use the ORDERED hint. Syntax: LEADING(table) |
| ORDERED |
The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause. If you omit the ORDERED hint from a SQL statement performing a join , the optimizer chooses the order in which to join the tables. You may want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information would allow you to choose an inner and outer table better than the optimizer could. |
? Hints for Join Operations ? |
| USE_NL |
The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table. The syntax of the USE_NL hint is USE_NL(table table) where table is the name or alias of a table to be used as the inner table of a nested loops join. |
| NO_USE_NL |
Do not use nested loop (from Oracle 10g) |
| USE_NL_WITH_INDEX |
Specifies a nested loops join. (from Oracle 10g) |
| USE_MERGE |
The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join. The syntax of the USE_MERGE hint is USE_MERGE(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a sort-merge join. |
| NO_USE_M |