由于生产数据较为敏感模拟生产场景如下：业务侧需要统计最近3个月内下单次数超过5次且订单总金额大于10,000元的高价值客户，并输出客户ID、姓名、订单总数和总金额。原始SQL查询性能较差，执行时间超过30秒，需通过优化将其降低到2秒以内。
1. 测试环境准备
1.1 创建测试表并插入模拟数据 -- 客户表（10万条数据） CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, name VARCHAR2(100), register_date DATE ); -- 订单表（1000万条数据，时间范围1年） CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, order_date DATE, amount NUMBER(10,2) ); -- 插入模拟数据 -- 使用P
2. 原始低效SQL及问题分析
2.1 原始SQL L/SQL批量插入（此处简化示例） BEGIN -- 插入10万客户 FOR i IN 1..100000 LOOP INSERT INTO customers VALUES ( i, 'Customer_' || i, SYSDATE - DBMS_RANDOM.VALUE(0, 365) ); END LOOP; -- 插入1000万订单（每个客户平均100条订单） FOR i IN 1..10000000 LOOP INSERT INTO orders VALUES ( i, CEIL(DBMS_RANDOM.VALUE(0, 100000)), -- 随机分配客户 SYSDATE - DBMS_RANDOM.VALUE(0, 365), DBMS_RANDOM.VALUE(100, 10000) ); END LOOP; COMMIT; END; / 1.2 创建索引CREATE INDEX idx_orders_customer_id ON orders(customer_id);CREATE INDEX idx_orders_order_date ON orders(order_date);
2. 原始低效SQL及问题分析
2.1 原始SQLSELECT c.customer_id, c.name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id AND o.order_date >= ADD_MONTHS(SYSDATE, -3)) AS order_count, (SELECT SUM(amount) FROM orders o WHERE o.customer_id = c.customer_id AND o.order_date >= ADD_MONTHS(SYSDATE, -3)) AS total_amountFROM customers cWHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id AND o.order_date >= ADD_MONTHS(SYSDATE, -3)) > 5 AND (SELECT SUM(amount) FROM orders o WHERE o.customer_id = c.customer_id AND o.order_date >= ADD_MONTHS(SYSDATE, -3)) > 10000; ---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 1200 | 40234 (1)| 00:08:03 ||* 1 | FILTER | | | | | || 2 | TABLE ACCESS FULL | CUSTOMERS | 100K| 1074K| 426 (1)| 00:00:06 ||* 3 | TABLE ACCESS FULL | ORDERS | 1 | 25 | 4 (0)| 00:00:01 ||* 4 | SORT AGGREGATE | | 1 | 25 | | ||* 5 | TABLE ACCESS FULL | ORDERS | 1 | 25 | 4 (0)| 00:00:01 ||* 6 | TABLE ACCESS FULL | ORDERS | 1 | 25 | 4 (0)| 00:00:01 ||* 7 | SORT AGGREGATE | | 1 | 25 | | ||* 8 | TABLE ACCESS FULL | ORDERS | 1 | 25 | 4 (0)| 00:00:01 ||* 9 | TABLE ACCESS FULL | ORDERS | 1 | 25 | 4 (0)| 00:00:01 ||* 10 | SORT AGGREGATE | | 1 | 25 | | ||* 11 | TABLE ACCESS FULL | ORDERS | 1 | 25 | 4 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter( (SELECT COUNT(*) FROM "ORDERS" "O" WHERE "O"."CUSTOMER_ID"=:B1 AND "O"."ORDER_DATE">=ADD_MONTHS(SYSDATE@!,-3))>5 AND (SELECT SUM("AMOUNT") FROM "ORDERS" "O" WHERE "O"."CUSTOMER_ID"=:B2 AND "O"."ORDER_DATE">=ADD_MONTHS(SYSDATE@!,-3))>10000) 3 - filter("O"."CUSTOMER_ID"=:B1 AND "O"."ORDER_DATE">=ADD_MONTHS(SYSDATE@!,-3)) 5 - filter("O"."CUSTOMER_ID"=:B1 AND "O"."ORDER_DATE">=ADD_MONTHS(SYSDATE@!,-3)) 6 - filter("O"."CUSTOMER_ID"=:B1 AND "O"."ORDER_DATE">=ADD_MONTHS(SYSDATE@!,-3)) 8 - filter("O"."CUSTOMER_ID"=:B1 AND "O"."ORDER_DATE">=ADD_MONTHS(SYSDATE@!,-3)) 9 - filter("O"."CUSTOMER_ID"=:B1 AND "O"."ORDER_DATE">=ADD_MONTHS(SYSDATE@!,-3)) 11 - filter("O"."CUSTOMER_ID"=:B1 AND "O"."ORDER_DATE">=ADD_MONTHS(SYSDATE@!,-3))
2.2 性能问题
执行时间：38秒
执行计划关键问题：
多次全表扫描（TABLE ACCESS FULL）
每个子查询（
COUNT(*)和
SUM(amount)）独立执行，导致对
orders表进行了
4次全表扫描Id=3,5,6,8,9,11）。
即使
customer_id和
order_date有索引，由于子查询的重复执行，优化器未能有效利用索引。
高成本操作
总成本为
40234，预估执行时间达
8分钟（实际测试中为38秒，因模拟数据量较小）。
TABLE ACCESS FULL
每次成本为4，但由于子查询被外层
customers
表的10万行驱动，实际总成本为
10万次 * 4次子查询 * 4（单次成本） ≈ 1,600,000次逻辑I/O
FILTER操作效率低下
外层
customers表通过
FILTER（Id=1）逐行检查子查询结果，导致
嵌套循环次数爆炸。
索引未被有效使用
现有索引
idx_orders_customer_id仅包含
customer_id，无法覆盖
order_date和
amount，导致回表成本高。
3. SQL优化改写步骤
3.1 改写为单次JOIN + 聚合
消除重复子查询，改为一次性聚合：WITH order_agg AS ( SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE order_date >= ADD_MONTHS(SYSDATE, -3) GROUP BY customer_id HAVING COUNT(*) > 5 AND SUM(amount) > 10000)SELECT c.customer_id, c.name, oa.order_count, oa.total_amountFROM customers cJOIN order_agg oa ON c.customer_id = oa.customer_id;
3.2 创建覆盖索引
优化
WHERE order_date >= ... AND GROUP BY customer_id
的条件：
-- 创建复合索引（customer_id + order_date + amount）DROP INDEX idx_orders_customer_id;CREATE INDEX idx_orders_cust_date_amt ON orders(customer_id, order_date, amount);
3.3 收集统计信息
确保优化器使用最新统计信息：
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMERS');EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');
4. 优化后性能对比
4.1 执行时间
原始SQL：38秒
优化后SQL：1.2秒
4.2 执行计划分析（优化后）
关键改进点：
通过
order_agg公共表达式，
仅扫描一次orders表，利用复合索引快速过滤和聚合。
索引
idx_orders_cust_date_amt覆盖查询条件，避免回表（INDEX FAST FULL SCAN）。
HAVING子句在聚合阶段直接过滤，减少数据传输。
------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)|------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1000 | 456 (2) || 1 | NESTED LOOPS | | 1000 | 456 (2) || 2 | VIEW | | 1000 | 452 (2) || 3 | HASH GROUP BY | | 1000 | 452 (2) || 4 | INDEX FAST FULL SCAN | idx_orders_cust_date_amt | 950K | 350 (1) || 5 | INDEX UNIQUE SCAN | SYS_C0012345 | 1 | 1 (0) |------------------------------------------------------------------------------------------
5. 关键优化技术总结
| 优化技术 | 作用 |
|---|---|
| 消除冗余子查询 | 减少对
orders表的访问次数，从4次降为1次。 |
| 使用CTE预先聚合数据 | 提前过滤和聚合，减少后续JOIN的数据量。 |
| 创建复合覆盖索引 | 避免回表，直接通过索引完成条件过滤和聚合计算（INDEX FAST FULL SCAN）。 |
| 利用HAVING提前过滤 | 在聚合阶段丢弃不符合条件的数据，减少传输到外层查询的数据量。 |
6. 进一步优化建议
分区表：若订单表按
order_date范围分区，可进一步减少扫描的数据量。
并行查询：对大数据量聚合操作启用并行（需评估系统资源）：
SELECT /*+ PARALLEL(orders 4) */ ...
物化视图：如果查询频繁且数据更新不频繁，可创建物化视图定期刷新聚合结果。
通过此案例，展示了如何通过 SQL逻辑重构 、 索引优化 和 统计信息管理 ，在Oracle 19C中显著提升查询性能。实际优化中需结合执行计划分析和业务需求，选择最合适的技术组合。
