This topic describes recursive query that is supported by AnalyticDB for MySQL clusters of version 3.1.3 and later. The persist plan feature can persist hint in the SQL statements by using parameters. Query-Blocker can intercept SQL statements by using parameters.

Overview

  • You can use persist plan to set a hint in an SQL statement. This way, the hint takes effect in SQL statements that share the same pattern. When you configure a hint in an SQL statement, this SQL statement is not executed. The same pattern indicates that constants in SQL statements are replaced with the question mark (?) such as constant conditions in select and where, limit m, and n.
  • You can view and delete the specified persist plan by using system tables.

Syntax

  • Add the persist hint statement: /*+hint*/ persist_plan + SQL
  • Delete the SQL statement from the persist plan statement: persist_plan + SQL.
  • View the templates in the persist plan: select * from information_schema.kepler_meta_persist_plan.
  • Intercept SQL(Query-Blocker):/*+query_blocker=true*/ persist_plan SQL.

Scenarios

  • Some advanced optimization features can be affected at the cluster level. If you want to add a hint in a scenario, you can configure a hint for this type of SQL statement without modifying the SQL statement.
  • If you want to intercept a type of BAD SQL statements, you can use the /*+query_blocker=true*/ hint + persist plan syntax to configure the interception feature.

Examples

Add a persist hint
SELECT  t1.c1
FROM
  T1 INNER JOIN T2 ON T1.C1 = T2.C1
WHERE T1.C2 < ? ;

The following code provides an example on how to use nested loop join for the preceding SQL pattern statements:

/*+nested_loop_join=true */
persist_plan
SELECT
  t1.c1
FROM
  T1
  INNER JOIN T2 ON T1.C1 = T2.C1
WHERE
  T1.C2 < 2;
Check whether a persist plan takes effect
select * from information_schema.kepler_meta_persist_plan
The following table describes the command output.
cluster_name hints sign sql
502683816_am-xxxxx_xxxxx_xxxxx nested_loop_join=true 79c37d4bbc162fb2b90090fcd185b7da SELECT T1.c1FROM T1 T1INNER JOIN T2 T2 ON T1.C1 = T2.C1WHERE T1.C2 < ?
Delete a persist plan
persist_plan
SELECT
  t1.c1
FROM
  T1 INNER JOIN T2 ON T1.C1 = T2.C1
WHERE T1.C2 < 2;