由于生产数据较为敏感模拟生产场景如下：

业务侧需要统计最近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 原始SQL

SELECT

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_amount

FROM customers c

WHERE

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

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 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_amount

FROM customers c

JOIN 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子句在聚合阶段直接过滤，减少数据传输。

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 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中显著提升查询性能。实际优化中需结合执行计划分析和业务需求，选择最合适的技术组合。

ITPub博客

Oracle 19C SQL优化案例通过SQL改写解决性能问题

1. 测试环境准备

2. 原始低效SQL及问题分析

2. 原始低效SQL及问题分析

3. SQL优化改写步骤

4. 优化后性能对比

5. 关键优化技术总结

6. 进一步优化建议

最新文章