lboka/doc-exports
1
0
Fork 0
forked from docs/doc-exports
Code Pull Requests Activity
doc-exports/docs/dws/dev/dws_04_0486.html
Lu, Huayi a24ca60074 DWS DEVELOPER 811 version 
Reviewed-by: Hasko, Vladimir <vladimir.hasko@t-systems.com>
Co-authored-by: Lu, Huayi <luhuayi@huawei.com>
Co-committed-by: Lu, Huayi <luhuayi@huawei.com>
2023-01-19 13:37:49 +00:00

4.9 KiB
Raw Permalink Blame History

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;

This SQL performance is poor. SubPlan exists in the execution plan as follows:

Optimization

The core of this optimization is to eliminate subqueries. Based on the service scenario analysis, a.ca_address_sk is not null. In terms of SQL syntax, you can rewrite the SQL statement as follows:

1
2
3
4
5
select 
count(*) 
from customer_address_001 a4, customer_address_001 a
where a4.ca_address_sk = a.ca_address_sk
group by  a.ca_address_sk;

To ensure that the modified statements have the same functions, not null is added to customer_address_001. ca_address_sk.

Parent topic: Optimization Cases