Problem
You have a performance issue with a query where you are joining multiple tables, and the Oracle optimizer is not choosing the join order you desire.
Solution
There are two hints—the ORDERED hint, and the LEADING hint—that can be used to influence the join order used within a query.
Using the ORDERED Hint
You are running a query to join two tables, EMP and DEPT, as you want to get the department names for each employee. By placing an ORDERED hint into the query, you can see how the hint alters the execution access path—for example:
Using the LEADING Hint
As with the example using the ORDERED hint, you have the same control to specify the join order of the query. The difference with the LEADING hint is that you specify the join order from within the hint itself, while with the ORDERED hint, it is specified in the FROM clause of the query. Here’s an example:
From the foregoing query, we can see that the table order specified in the FROM clause is irrelevant, as the order specified in the LEADING hint itself specifies the join order for the query.
How It Works
The main purpose of specifying either of these hints is for multi-table joins where the most optimal join order is known. This is usually known from past experience with a given query, based on the makeup of the data and the tables. In these cases, specifying either of these hints will save the optimizer the time of having to process all of the possible join orders in determining the optimal join order. This can improve query performance, especially as the number of tables to join within a query increases.
When using either of these hints, you instruct the optimizer about the join order of the tables. Because of this, it is critically important that you know that the hint will improve the query’s performance. Oracle recommends, where possible, to use the LEADING hint over the ORDERED hint, as the LEADING hint has more versatility built in. When specifying the ORDERED hint, you specify the join order from the list of tables in the FROM clause, while with the LEADING hint, you specify the join order within the hint itself.
You have a performance issue with a query where you are joining multiple tables, and the Oracle optimizer is not choosing the join order you desire.
Solution
There are two hints—the ORDERED hint, and the LEADING hint—that can be used to influence the join order used within a query.
Using the ORDERED Hint
You are running a query to join two tables, EMP and DEPT, as you want to get the department names for each employee. By placing an ORDERED hint into the query, you can see how the hint alters the execution access path—for example:
Using the LEADING Hint
As with the example using the ORDERED hint, you have the same control to specify the join order of the query. The difference with the LEADING hint is that you specify the join order from within the hint itself, while with the ORDERED hint, it is specified in the FROM clause of the query. Here’s an example:
From the foregoing query, we can see that the table order specified in the FROM clause is irrelevant, as the order specified in the LEADING hint itself specifies the join order for the query.
How It Works
The main purpose of specifying either of these hints is for multi-table joins where the most optimal join order is known. This is usually known from past experience with a given query, based on the makeup of the data and the tables. In these cases, specifying either of these hints will save the optimizer the time of having to process all of the possible join orders in determining the optimal join order. This can improve query performance, especially as the number of tables to join within a query increases.
When using either of these hints, you instruct the optimizer about the join order of the tables. Because of this, it is critically important that you know that the hint will improve the query’s performance. Oracle recommends, where possible, to use the LEADING hint over the ORDERED hint, as the LEADING hint has more versatility built in. When specifying the ORDERED hint, you specify the join order from the list of tables in the FROM clause, while with the LEADING hint, you specify the join order within the hint itself.
No comments:
Post a Comment