When you use a SELECT statement, you can use the WITH
clause to create a common table expression (CTE). A CTE is a temporary result set that you can reference within a SELECT
statement. A CTE can be used to flatten nested queries or simplify subqueries. This way, a subquery is executed only once in the SELECT
statement, which improves query performance. This topic describes how to use the WITH
clause in a SELECT
statement.
Usage notes
You can specify SQL statements or other CTEs in the wake of a CTE. You can specify only a single
WITH
clause in a query. You must separate multiple CTEs with commas (,). Otherwise, the CTEs are invalid.Paging is not supported in a CTE.
How to use WITH
Use WITH in an alternative statement of SELECT
SELECT a, b FROM (SELECT a, MAX(b) AS b FROM t GROUP BY a) AS x;
WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a) SELECT a, b FROM x;
Use WITH to contain multiple subqueries
WITH t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a), t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a) SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.a = t2.a;
Reference a subquery for other subqueries that are in the wake of the subquery in the same WITH clause
WITH x AS (SELECT a FROM t), y AS (SELECT a AS b FROM x), z AS (SELECT b AS c FROM y) SELECT c FROM z;
CTE execution optimization
The CTE execution optimization feature is supported for AnalyticDB for MySQL clusters of V3.1.9.3 and later. By default, this feature is disabled. You can specify the cte_execution_mode parameter to enable this feature. If a CTE subquery is referenced for multiple times after this feature is enabled, the subquery can be executed only once to improve the performance of specific queries.
The CTE execution optimization feature may cause degraded performance for specific queries. If you find a significant degradation in query performance, we recommend that you disable this feature.
Enable CTE execution optimization
Enable CTE execution optimization for a specific query
Add a hint before a specific query statement to enable CTE execution optimization for the query. Example with the built-in dataset:
/*cte_execution_mode=shared*/ WITH shared AS (SELECT L_ORDERKEY, L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY) SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;
Enable CTE execution optimization for all queries
Execute the SET statement to enable CTE execution optimization for all queries. Example:
SET adb_config cte_execution_mode=shared;
Disable CTE execution optimization
Disable CTE execution optimization for a specific query
Add a hint before a specific query statement to disable CTE execution optimization for the query. Example with the built-in dataset:
/*cte_execution_mode=inline*/ WITH shared AS (SELECT L_ORDERKEY, L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY) SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;
Disable CTE execution optimization for all queries
Execute the SET statement to disable CTE execution optimization for all queries. Example:
SET adb_config cte_execution_mode=inline;