Oracle SQL优化：快速查询购过红色车辆的客户车型聚合结果

在Oracle SQL Developer中执行查询时遭遇性能瓶颈：数据库数据量庞大，现有SQL需对各列单独排序、关联表，执行耗时长达2小时，且结果与单客户测试结果存在差异。

需求 #

从Purchased表（存储客户购车记录）中查询所有曾购买过红色车辆的客户，按客户维度聚合为一行，输出Customer_ID、Last_Name、First_Name及Car、SUV、Truck、4WD的持有情况（是/否），不受交易时间限制。

示例数据 #

Purchased表：

期望输出 #

当前SQL语句 #

WITH
sorted_ as (
select
distinct (colour) ,
customer_id ,
last_name ,
car ,
SUV ,
truck ,
4wd
from purchased)
select
e. customer_id ,
e. last_name ,
nvl (e. car , 'No') car ,
nvl (e. SUV , 'No') SUV ,
nvl (e. truck , 'No') truck ,
nvl (e. 4wd , 'No') 4wd
from (
select
a. customer_id ,
a. last_name ,
a. car ,
b. SUV ,
c. truck ,
d. 4wd
from (
select
distinct(car),
customer_id
from sorted_
where colour = 'Red'
and car = 'Yes') a
left join (
select
distinct(SUV),
customer_id
from sorted_
where colour = 'Red'
and SUV = 'Yes') b
ON a. customer_id = b. customer_id
left join (
select
distinct(truck),
customer_id
from sorted_
where colour = 'Red'
and truck = 'Yes') c
ON a. customer_id = c. customer_id
left join (
select
distinct(4wd),
customer_id
from sorted_
where colour = 'Red'
and 4wd = 'Yes') d
ON a. customer_id = d. customer_id
) e

SELECT
    Customer_ID,
    MAX(Last_Name) AS Last_Name,
    MAX(First_Name) AS First_Name,
    CASE WHEN MAX(CASE WHEN Colour = 'Red' AND Car = 'Yes' THEN 'Yes' END) IS NOT NULL THEN 'Yes' ELSE 'No' END AS Car,
    CASE WHEN MAX(CASE WHEN Colour = 'Red' AND SUV = 'Yes' THEN 'Yes' END) IS NOT NULL THEN 'Yes' ELSE 'No' END AS SUV,
    CASE WHEN MAX(CASE WHEN Colour = 'Red' AND Truck = 'Yes' THEN 'Yes' END) IS NOT NULL THEN 'Yes' ELSE 'No' END AS Truck,
    CASE WHEN MAX(CASE WHEN Colour = 'Red' AND 4WD = 'Yes' THEN 'Yes' END) IS NOT NULL THEN 'Yes' ELSE 'No' END AS 4WD
FROM Purchased
WHERE EXISTS (
    SELECT 1 
    FROM Purchased p_sub 
    WHERE p_sub.Customer_ID = Purchased.Customer_ID 
      AND p_sub.Colour = 'Red'
)
GROUP BY Customer_ID
ORDER BY Customer_ID;

优化说明 #

• 砍掉多表关联和重复排序：原SQL拆分多个子查询做关联，每个DISTINCT都会触发排序，数据量大时开销极高。优化后仅需一次表扫描，通过条件聚合直接计算每个客户的持有情况，彻底省去多表关联和重复排序的消耗。
• 先筛目标客户再聚合：用EXISTS子句先把买过红色车辆的客户筛选出来，减少后续聚合处理的数据量，避免对全表无效数据做计算。
• 逻辑直接准确：嵌套CASE+MAX的组合，只要客户有过对应红色车型的购买记录就标记为Yes，否则为No，逻辑简洁且结果精准，解决了原查询结果与单客户测试不一致的问题。
• 去掉冗余的DISTINCT：原SQL中的DISTINCT属于多余操作，聚合函数本身就能完成去重判断，省去了额外的CPU和内存消耗。

索引建议 #

为进一步提升性能，建议在Purchased表上创建复合索引：

CREATE INDEX idx_purchased_cust_colour ON Purchased(Customer_ID, Colour);

该索引可快速定位购买过红色车辆的客户，高效支持EXISTS子句查询，同时优化分组聚合时的性能。

内容的提问来源于stack exchange，提问作者Lizzie