Oracle 复杂查询优化

复杂查询概述

复杂查询是指包含多个表连接、子查询、聚合函数、排序等操作的 SQL 查询，通常用于数据仓库、报表生成和数据分析等场景。复杂查询的性能优化是 Oracle 数据库管理中的一个重要挑战，需要 DBA 和开发人员具备深厚的 SQL 知识和性能调优经验。

复杂查询的特点包括：

包含多个表连接（通常 3 个以上）
使用子查询、嵌套查询或递归查询
包含聚合函数和分组操作
需要大量的排序和连接操作
处理大量数据（通常百万行以上）

复杂查询分析方法

1. 执行计划分析

执行计划是分析复杂查询性能的基础，能够帮助 DBA 了解查询的执行路径，识别性能瓶颈。

示例：

sql

-- 生成执行计划
EXPLAIN PLAN FOR
SELECT
    d.department_name,
    j.job_title,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS avg_salary
FROM
    departments d
    JOIN employees e ON d.department_id = e.department_id
    JOIN jobs j ON e.job_id = j.job_id
GROUP BY
    d.department_name,
    j.job_title
ORDER BY
    d.department_name,
    avg_salary DESC;

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

2. 统计信息收集

准确的统计信息对于优化器生成正确的执行计划至关重要。

示例：

sql

-- 收集表和索引的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMPLOYEES',
    cascade => TRUE,
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    degree => DBMS_STATS.AUTO_DEGREE
);

3. SQL 监控

对于长时间运行的复杂查询，可以使用 SQL Monitor 进行实时监控。

示例：

sql

-- 启用 SQL 监控
ALTER SESSION SET sql_monitor = TRUE;

-- 执行复杂查询
SELECT /*+ MONITOR */
    d.department_name,
    j.job_title,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS avg_salary
FROM
    departments d
    JOIN employees e ON d.department_id = e.department_id
    JOIN jobs j ON e.job_id = j.job_id
GROUP BY
    d.department_name,
    j.job_title
ORDER BY
    d.department_name,
    avg_salary DESC;

-- 查看 SQL 监控报告
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR FROM DUAL;

复杂查询优化技巧

1. 优化表连接

连接顺序优化

将返回结果集较小的表放在前面
对于多个表连接，使用 ORDERED 提示指定连接顺序
考虑使用 LEADING 提示指定驱动表

示例：

sql

-- 使用 ORDERED 提示指定连接顺序
SELECT /*+ ORDERED */
    d.department_name,
    j.job_title,
    COUNT(e.employee_id) AS employee_count
FROM
    departments d
    JOIN employees e ON d.department_id = e.department_id
    JOIN jobs j ON e.job_id = j.job_id
GROUP BY
    d.department_name,
    j.job_title;

-- 使用 LEADING 提示指定驱动表
SELECT /*+ LEADING(d) */
    d.department_name,
    j.job_title,
    COUNT(e.employee_id) AS employee_count
FROM
    departments d
    JOIN employees e ON d.department_id = e.department_id
    JOIN jobs j ON e.job_id = j.job_id
GROUP BY
    d.department_name,
    j.job_title;

连接类型选择

嵌套循环连接：适用于小结果集，驱动表返回行数较少的情况
哈希连接：适用于大结果集，等值连接的情况
排序合并连接：适用于有序数据，范围连接的情况

示例：

sql

-- 使用嵌套循环连接
SELECT /*+ USE_NL(e j) */
    d.department_name,
    j.job_title,
    COUNT(e.employee_id) AS employee_count
FROM
    departments d
    JOIN employees e ON d.department_id = e.department_id
    JOIN jobs j ON e.job_id = j.job_id
GROUP BY
    d.department_name,
    j.job_title;

-- 使用哈希连接
SELECT /*+ USE_HASH(e j) */
    d.department_name,
    j.job_title,
    COUNT(e.employee_id) AS employee_count
FROM
    departments d
    JOIN employees e ON d.department_id = e.department_id
    JOIN jobs j ON e.job_id = j.job_id
GROUP BY
    d.department_name,
    j.job_title;

2. 优化子查询

使用 WITH 子句

WITH 子句（也称为公共表表达式 CTE）能够提高复杂查询的可读性和性能，特别是对于多次引用相同子查询的情况。

示例：

sql

-- 使用 WITH 子句优化复杂查询
WITH dept_emp AS (
    SELECT
        d.department_id,
        d.department_name,
        e.employee_id,
        e.job_id,
        e.salary
    FROM
        departments d
        JOIN employees e ON d.department_id = e.department_id
),
dept_job_sal AS (
    SELECT
        de.department_name,
        j.job_title,
        de.salary
    FROM
        dept_emp de
        JOIN jobs j ON de.job_id = j.job_id
)
SELECT
    department_name,
    job_title,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM
    dept_job_sal
GROUP BY
    department_name,
    job_title
ORDER BY
    department_name,
    avg_salary DESC;

子查询物化

使用 MATERIALIZE 提示强制物化子查询，避免重复执行。

示例：

sql

-- 使用 MATERIALIZE 提示物化子查询
WITH dept_emp AS (
    SELECT /*+ MATERIALIZE */
        d.department_id,
        d.department_name,
        e.employee_id,
        e.job_id,
        e.salary
    FROM
        departments d
        JOIN employees e ON d.department_id = e.department_id
)
SELECT
    de.department_name,
    j.job_title,
    COUNT(de.employee_id) AS employee_count,
    AVG(de.salary) AS avg_salary
FROM
    dept_emp de
    JOIN jobs j ON de.job_id = j.job_id
GROUP BY
    de.department_name,
    j.job_title
ORDER BY
    de.department_name,
    avg_salary DESC;

3. 优化聚合操作

使用 ROLLUP/CUBE 进行多级汇总

对于需要多级汇总的查询，使用 ROLLUP 或 CUBE 替代多个 GROUP BY 查询。

示例：

sql

-- 使用 ROLLUP 进行多级汇总
SELECT
    d.department_name,
    j.job_title,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS avg_salary
FROM
    departments d
    JOIN employees e ON d.department_id = e.department_id
    JOIN jobs j ON e.job_id = j.job_id
GROUP BY ROLLUP (d.department_name, j.job_title)
ORDER BY
    d.department_name,
    j.job_title;

使用窗口函数优化分析查询

对于复杂的分析查询，使用窗口函数替代子查询，提高性能。

示例：

sql

-- 使用窗口函数计算累计和
SELECT
    department_id,
    employee_id,
    salary,
    SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS cumulative_salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM
    employees;

4. 优化排序操作

避免不必要的排序

确保 ORDER BY 子句中的列与索引顺序一致
使用索引排序，避免内存排序
限制排序数据量

示例：

sql

-- 推荐：使用索引排序
SELECT employee_id, first_name, last_name FROM employees ORDER BY employee_id;

-- 不推荐：不使用索引排序
SELECT employee_id, first_name, last_name FROM employees ORDER BY hire_date;

使用并行排序

对于大型查询，使用并行排序提高性能。

示例：

sql

-- 使用并行排序
SELECT /*+ PARALLEL(4) */
    department_id,
    job_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM
    employees
GROUP BY
    department_id,
    job_id
ORDER BY
    department_id,
    avg_salary DESC;

19c 和 21c 复杂查询优化新特性

Oracle 19c 新特性

SQL Plan Management 增强：改进了 SQL 计划基线管理，提高了复杂查询的执行计划稳定性
自动索引：自动为复杂查询创建合适的索引
实时统计信息：支持实时收集统计信息，提高了优化器的准确性
并行查询增强：改进了并行查询算法，提高了复杂查询的并行执行性能

Oracle 21c 新特性

SQL Macros：使用 SQL Macros 简化复杂查询，提高查询性能和可读性
智能优化器：引入机器学习算法，能够自动选择最优的执行计划
批量数据处理增强：改进了批量数据处理算法，提高了复杂查询的性能
新的连接算法：引入了新的连接算法，提高了复杂连接查询的性能
轻量级连接：支持轻量级连接，减少了连接开销

生产环境最佳实践

1. 复杂查询设计

分解复杂查询：将复杂查询分解为多个简单查询，特别是对于数据仓库查询
使用 WITH 子句：使用 WITH 子句提高查询可读性和性能
避免过度使用子查询：尽量使用 JOIN 替代子查询
优化连接顺序：将返回结果集较小的表放在前面

2. 索引设计

覆盖索引：创建覆盖索引，包含查询中所有需要的列，避免回表
复合索引：为连接列和过滤列创建复合索引
函数索引：为频繁使用函数的列创建函数索引
分区索引：对于大型表，使用分区索引

3. 统计信息管理

定期收集统计信息：根据表的变化频率定期收集统计信息
使用动态采样：对于临时表或统计信息不准确的表，使用动态采样
锁定统计信息：对于稳定的表，锁定统计信息，避免统计信息变化导致执行计划不稳定

4. 监控与调优

监控查询性能：使用 AWR、ASH 等工具监控复杂查询的性能
分析执行计划：定期分析复杂查询的执行计划，确保执行计划最优
使用 SQL Tuning Advisor：对于性能较差的复杂查询，使用 SQL Tuning Advisor 获取优化建议
考虑使用 Parallel Execution：对于大型查询，考虑使用并行执行

常见问题 (FAQ)

如何优化包含多个表连接的复杂查询？

优化包含多个表连接的复杂查询的方法包括：

优化连接顺序，将返回结果集较小的表放在前面
为连接列创建索引
使用合适的连接类型（嵌套循环、哈希连接、排序合并连接）
使用 WITH 子句分解查询
考虑使用并行执行

如何提高复杂查询的执行计划稳定性？

提高复杂查询执行计划稳定性的方法包括：

使用 SQL 计划基线锁定执行计划
锁定统计信息，避免统计信息变化导致执行计划变化
使用绑定变量，避免硬解析
避免使用不稳定的提示

什么时候使用 WITH 子句？

WITH 子句适用于以下情况：

复杂查询需要多次引用相同的子查询
需要提高查询可读性
需要使用子查询物化提高性能
需要进行多级汇总

如何优化使用聚合函数的复杂查询？

优化使用聚合函数的复杂查询的方法包括：

为分组列创建索引
使用 ROLLUP/CUBE 替代多个 GROUP BY 查询
使用窗口函数替代子查询
考虑使用并行执行
优化排序操作

19c 和 21c 在复杂查询优化方面有什么主要区别？

Oracle 21c 在复杂查询优化方面相比 19c 有以下主要增强：

引入了 SQL Macros，简化复杂查询
智能优化器，能够自动选择最优的执行计划
改进了连接算法，提高了复杂连接查询的性能
批量数据处理增强
轻量级连接，减少了连接开销

如何监控复杂查询的性能？

监控复杂查询性能的方法包括：

使用 SQL Monitor 实时监控查询执行情况
分析 AWR 报告中的 Top SQL
使用 ASH 报告分析查询的等待事件
监控系统资源使用率，包括 CPU、内存、I/O 等
分析执行计划，识别性能瓶颈

总结

复杂查询优化是 Oracle 数据库性能管理的重要组成部分，需要 DBA 和开发人员具备深厚的 SQL 知识和性能调优经验。通过合理的查询设计、索引设计、统计信息管理和监控调优，可以有效提高复杂查询的性能，满足业务需求。

在实际工作中，DBA 应该根据具体的业务需求和系统环境，选择合适的优化方法。同时，应该定期监控和分析复杂查询的性能，不断优化和改进查询设计，确保数据库系统能够高效处理复杂查询。

随着 Oracle 版本的升级，新的优化技术和特性不断出现，DBA 应该及时学习和掌握这些新特性，提高复杂查询优化的效率和质量。通过合理的优化和维护，可以有效提高数据库系统的性能和可靠性，支持更高的并发访问和更大规模的数据处理。

Oracle 复杂查询优化 ​

复杂查询概述 ​

复杂查询分析方法 ​

1. 执行计划分析 ​

2. 统计信息收集 ​

3. SQL 监控 ​

复杂查询优化技巧 ​

1. 优化表连接 ​

连接顺序优化 ​

连接类型选择 ​

2. 优化子查询 ​

使用 WITH 子句 ​

子查询物化 ​

3. 优化聚合操作 ​

使用 ROLLUP/CUBE 进行多级汇总 ​

使用窗口函数优化分析查询 ​

4. 优化排序操作 ​

避免不必要的排序 ​

使用并行排序 ​

19c 和 21c 复杂查询优化新特性 ​

Oracle 19c 新特性 ​

Oracle 21c 新特性 ​

生产环境最佳实践 ​

1. 复杂查询设计 ​

2. 索引设计 ​

3. 统计信息管理 ​

4. 监控与调优 ​

常见问题 (FAQ) ​

如何优化包含多个表连接的复杂查询？ ​

如何提高复杂查询的执行计划稳定性？ ​

什么时候使用 WITH 子句？ ​

如何优化使用聚合函数的复杂查询？ ​

19c 和 21c 在复杂查询优化方面有什么主要区别？ ​

如何监控复杂查询的性能？ ​

总结 ​