Tips for Oracle Hint and INDEX

Oracle has a wide variety of hint types, which can be roughly divided into the following categories:
Optimization methods and objectives: such as RULE, CHOOSE, FIRST_ROWS, and ALL_ROWS.
Access path: such as INDEX, FULL, CLUSTER, and INDEX_FFS.
Query conversion: such as MERGE, USE_CONCAT, and NO_EXPAND.
Connection sequence: such as LEADING, ORDERED, and STAR.
Connection operations: such as USE_NL, USE_HASH, and USE_MERGE.
Parallel Execution: such as PARALLE, NOPARALLEL, and PARALLEL_INDEX.
Other types, such as APPEND, UNNEST, and CACHE.

INDEX is only a type of Hint.

About LEADING and ORDERED:
/* + ORDERED */prompts to follow the order of the from table.
/* + LEADING (table1 table2...) */The prompt is executed in the specified order.
About USE_NL, USE_HASH, and USE_MERGE:
I tested 10 Gb. The three parameters only need to fill in the table name as the parameter, and there is no sequence required. Oracle should automatically select the driver table.
However, you can use LEADING or ORDERED to specify the order.
(Note) The use of INDEX affects the query of the table itself; USE_NL, USE_HASH, and USE_MERGE affect the connection between tables.
Http://hi.baidu.com/di_wan/modify/blog/618aedca267ab783c8176829

INDEX reference: http://hi.baidu.com/di_wan/blog/item/fd943290b5a66388a877a424.html

 

Hint is a double-edged sword, or even a bomb.

When optimizing SQL statements, database designers, SQL producers, and DBAs have the following efficiency levels:
Append constraints> adjust SQL logic> design INDEX> Add Hint
In addition, the higher the risk, the higher the risk.
Because the data volume, database version, and hardware of the development environment, test environment, and end customer environment will always be different;
While Hint is enforced (the error Hint statement is automatically ignored ),
It will produce an efficient Hint in one place, but it is very resource-consuming in another place.

If it is not a DBA that can be operated in the end customer environment, it is best not to write Hint,
You only need to set the INDEX,
Oracle's automatic optimization will certainly be used.