The persist plan feature can persist hint in the SQL statements by using parameters. Query-Blocker can intercept SQL statements by using parameters. This topic describes recursive query that is supported by AnalyticDB for MySQL clusters of version 3.1.3 and later.
- 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 question marks (?) are used to replace constants in SQL statements,such as constant conditions in the SELECT and WHERE statements and LIMIT m,n.
- You can view and delete the specified persist plan by using system tables.
- Add a persist hint:
/*+hint*/ persist_plan + SQL.
- Delete the SQL statement from a persist plan statement:
persist_plan + SQL.
- View the templates in a persist plan:
select * from information_schema.kepler_meta_persist_plan.
- Intercept SQL statements by Query-Blocker:
/*+query_blocker=true*/ persist_plan SQL.
- 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.
- 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 (NLJ) for the SQL statements that follow the preceding pattern:
/*+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 cluster_name, hints, sign, sql 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;