Cte Scan
Description
Common table expression (CTE) is a temporary expression. CTE scan is used to scan temporary tables generated by CTE expressions.
In GaussDB, you can use the WITH keyword to specify one or more CTEs, which can be used multiple times in subsequent queries.
Typical Scenarios
When a query result set needs to be used for multiple times in subsequent queries, you can use CTE to avoid multiple calculations.
Examples
Example: CTEs with the WITH statement cannot be rewritten as subqueries.
-- Prepare data. gaussdb=# CREATE TABLE employees(deptid integer, salary number); CREATE TABLE gaussdb=# CREATE TABLE managers(deptid integer); CREATE TABLE -- Execution result. gaussdb=# EXPLAIN WITH table_b AS (SELECT deptid,sum(salary) dept_salary FROM employees GROUP BY deptid) SELECT m.deptid, b.dept_salary FROM managers m,table_b b,table_b c WHERE m.deptid = b.deptid AND m.deptid = c.deptid; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Hash Join (cost=1.15..1.81 rows=20 width=36) Hash Cond: (c.deptid = m.deptid) CTE table_b -> Data Node Scan on employees "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=20 width=36) Node/s: All datanodes -> CTE Scan on table_b c (cost=0.00..0.40 rows=20 width=4) -> Hash (cost=0.90..0.90 rows=20 width=40) -> Hash Join (cost=0.25..0.90 rows=20 width=40) Hash Cond: (b.deptid = m.deptid) -> CTE Scan on table_b b (cost=0.00..0.40 rows=20 width=36) -> Hash (cost=0.00..0.00 rows=20 width=4) -> Data Node Scan on managers "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=20 width=4) Node/s: All datanodes (13 rows) -- Drop the table. gaussdb=# DROP TABLE employees, managers;
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot