Common hint (i) ____oracle in Oracle

Hint in Oracle can be used to adjust SQL's execution plan and improve SQL execution efficiency. The following categories describe the common hint in Oracle databases. It is described here that the hint in each version of the common Hint,oracle database in ORACLE11GR2 are different, so the hint described here may not be applicable to earlier versions of Oracle.

Hint associated with the optimizer pattern

1, All_rows

The all_rows is hint for the entire target SQL, meaning that the optimizer enables the CBO and chooses those throughput best execution paths when it gets the execution plan for the target SQL. The "Best throughput" here is the smallest amount of resource consumption (i.e., consumption of hardware resources, such as I/O, CPUs), that is, when all_rows hint is in effect, the optimizer enables the CBO and calculates their respective costs based on the resource consumption of each execution path.

The format of the all_rows hint is as follows:

/*+ all_rows * *

Use Example:

1 2 3

Select/*+ all_rows * * empno,ename,sal,job from EMP where empno=7396;

Starting with oracle10g, All_rows is the default optimizer mode, and the CBO is enabled.

1 2 3 4 5

Scott@test>show parameter Optimizer_mode NAME TYPE VALUE-----------------------                   ----------------------------------------------------------------------------Optimizer_mode string All_rows

The optimizer prioritizes all_rows if there are other hint associated with execution paths and table joins in the target SQL, in addition to All_rows.

2, First_rows (n)

First_rows (n) is the hint of the entire target SQL, meaning that the optimizer enables the CBO mode, and selects the execution path that returns the first N records in the quickest response time when the execution plan for the target SQL is obtained, that is, in First_rows (n) In the case of hint, the optimizer enables the CBO and determines the execution plan of the target SQL based on the response time of the first N records returned.

The First_rows (n) format is as follows:

/*+ first_rows (N) * *

Usage examples

1 2