Help Center> Data Warehouse Service> Developer Guide> Query Performance Optimization> Tuning Queries> Hint-based Tuning> Plan Hint Optimization
View PDF

Plan Hint Optimization

In plan hints, you can specify a join order; join, stream, and scan operations; you can specify 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
;

Parent topic: Hint-based Tuning