GaussDB-案例：改写SQL消除子查询

GaussDB-案例：改写SQL消除子查询

现象描述

| ``` 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;

| --------------------------------------------------------------------------------------------------------------------------------------------------------- |

此SQL性能较差，查看发现执行计划中存在SubPlan，具体如下：

![](https://p9-xtjj-sign.byteimg.com/tos-cn-i-73owjymdk6/14cdadfa0233426d8c12467316f3efd9~tplv-73owjymdk6-jj-mark-v1:0:0:0:0:5o6Y6YeR5oqA5pyv56S-5Yy6IEAgamVycnl3YW5nMTk4Mw==:q75.awebp?rk3s=f64ab15b&x-expires=1771400591&x-signature=%2BEIy4Qu1eMuNXGBPhwaMlwlIVUo%3D)

#### 优化说明

此优化的核心就是消除子查询。分析业务场景发现a **.** ca_address_sk不为NULL，那么从SQL语义出发，可以等价改写SQL为：

| ```
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; 
``` |
| --------------------------------------------------------------------------------------------------------------------------------------------------- |

![](https://p9-xtjj-sign.byteimg.com/tos-cn-i-73owjymdk6/99d4a48ee0de4efb9ceebd1fee610b70~tplv-73owjymdk6-jj-mark-v1:0:0:0:0:5o6Y6YeR5oqA5pyv56S-5Yy6IEAgamVycnl3YW5nMTk4Mw==:q75.awebp?rk3s=f64ab15b&x-expires=1771400591&x-signature=o0rVSEYLaxovbWhxZSuE0DrIuLQ%3D)

为了保证改写的等效性，在customer_address_001. ca_address_sk加了not null约束。

更多详情请参考GaussDB 文档中心：<https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/productdesc/qlh_03_0001.html>