Oracle technology: How to use the ordered hint to change the SQL execution plan

Ordered prompts to force Oracle to join tables in the order in which they appear in the FROM clause.

By ordered hints, you can avoid table connection evaluations during the CBO SQL parsing process, thus avoiding Oracle generating incorrect execution plans or forcing Oracle to execute in the way we specify.

In many cases, when we have a clear understanding of data structures and distributions, we can improve SQL performance with ordered hints.

Let's illustrate the role of ordered hints with the following examples.

1. SQL execution plan without hints

We can trace this SQL parsing through the 10053 event:

Looking at the trace file you can see that Oracle needs to be 3! (6) Evaluation of the Order of the secondary table connection:

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

2. When we use the ordered prompt

The execution plan for SQL is as follows (the order of the tables after the FROM clause is adjusted):

Look at the trace trace file for 10053 again:

Oracle only needs to be connected according to the order in which the table appears in the FROM clause to parse or execute according to our intent.

This is the basic role of the ordered hint, and this example is just a demonstration that the latter's implementation plan makes the cost surge, and we certainly do not want to see such growth in practical applications.

Author: 51cto Blog Oracle Little Bastard