Case: Rewriting SQL and Deleting Subqueries (Case 1)
Symptom
1 2 3 4 |
select 1, (select count(*) from customer_address_001 a4 where a4.ca_address_sk = a.ca_address_sk) as GZCS from customer_address_001 a; |
The performance of the SQL statement is poor. It is found that the execution plan contains subplans. The operator that references the subplan results may need to be repeatedly invoked to obtain the subplan values. This means the subplan results need to be executed for many times. The details are as follows:
Optimization
The core of this optimization is to eliminate subqueries. Based on the SQL semantics, the SQL statement can be modified as follows:
1 2 3 4 5 6 |
select 1, coalesce(a4.c1, 0) from (select count(*) c1, a4.ca_address_sk from customer_address_001 a4 group by a4.ca_address_sk) a4 right join customer_address_001 a on a4.ca_address_sk = a.ca_address_sk; |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.