Help Center > > Developer Guide> Query Performance Optimization> Tuning Queries> Hint-based Tuning> Plan Hint Optimization

Plan Hint Optimization

Updated at: Jul 15, 2020 GMT+08:00

In plan hints, you can specify a join order; join, stream, and scan operations; the number of rows in a result; and redistribution skew information to tune an execution plan, improving query performance.

Function

The hint syntax must follow immediately after a SELECT keyword and is written in the following format:

1
/*+ <plan hint>*/

You can specify multiple hints for a query plan and separate them by spaces. A hint specified for a query plan does not apply to its subquery plans. To specify a hint for a subquery, add the hint following the SELECT of this subquery.

For example:

1
select /*+ <plan_hint1> <plan_hint2> */ * from t1, (select /*+ <plan_hint3> */ from t2) where 1=1;

In the preceding command, <plan_hint1> and <plan_hint2> are the hints of a query, and <plan_hint3> is the hint of its subquery.

If a hint is specified in the CREATE VIEW statement, the hint will be applied each time this view is used.

If the random plan function is enabled (plan_mode_seed is set to a value other than 0), the specified hint will not be used.

Supported Hints

Currently, the following hints are supported:

  • Join order hints (leading)
  • Join operation hints, excluding the semi join, anti join, and unique plan hints
  • Rows hints
  • Stream operation hints
  • Scan operation hints, supporting only tablescan, indexscan, and indexonlyscan
  • Sublink name hints
  • Skew hints, supporting only the skew in the redistribution involving Join or HashAgg

Precautions

  • Hints do not support Agg, Sort, Setop, or Subplan.
  • Hints do not support SMP or Node Groups.

Examples

The following is the original plan and is used for comparing with the optimized ones:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
explain
select i_product_name product_name
,i_item_sk item_sk
,s_store_name store_name
,s_zip store_zip
,ad2.ca_street_number c_street_number
,ad2.ca_street_name c_street_name
,ad2.ca_city c_city
,ad2.ca_zip c_zip
,count(*) cnt
,sum(ss_wholesale_cost) s1
,sum(ss_list_price) s2
,sum(ss_coupon_amt) s3
FROM   store_sales
,store_returns
,store
,customer
,promotion
,customer_address ad2
,item
WHERE  ss_store_sk = s_store_sk AND
ss_customer_sk = c_customer_sk AND
ss_item_sk = i_item_sk and
ss_item_sk = sr_item_sk and
ss_ticket_number = sr_ticket_number and
c_current_addr_sk = ad2.ca_address_sk and
ss_promo_sk = p_promo_sk and
i_color in ('maroon','burnished','dim','steel','navajo','chocolate') and
i_current_price between 35 and 35 + 10 and
i_current_price between 35 + 1 and 35 + 15
group by i_product_name
,i_item_sk
,s_store_name
,s_zip
,ad2.ca_street_number
,ad2.ca_street_name
,ad2.ca_city
,ad2.ca_zip
;

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel