Oracle优化SQL语句 — 语法知识点与使用方法详解
一、环境准备（Oracle数据库安装简要说明）
注意：Oracle数据库安装较为复杂，以下为简化版安装流程（以 Oracle Database 21c Express Edition 为例）。
1. 下载 Oracle Database 21c XE
- 官网地址：https://www.oracle.com/database/technologies/xe-downloads.html
- 支持平台：Windows / Linux
2. 安装步骤（以 Windows 为例）
- 以管理员身份运行安装程序
OracleXE21c_Win64.exe - 设置SYS / SYSTEM 用户密码（记住该密码）
- 选择安装路径（默认即可）
- 安装完成后，服务自动启动：
OracleServiceXEOracleXETNSListener
3. 验证安装
# 打开命令行sqlplus sys/your_password@//localhost:1521/XE as sysdba若成功进入 SQL> 提示符，说明安装成功。
4. 创建测试用户（可选）
-- 以 sys 用户登录后执行CREATEUSERtest_user IDENTIFIEDBYtest123;GRANTCONNECT,RESOURCE,DBATOtest_user;二、常规SQL语句优化
1. 不用“*”代替所有列名
✅ 原理：
SELECT *会读取所有列，增加 I/O 和网络传输开销。- 明确指定列可利用覆盖索引，提升性能。
📌 案例代码：
-- ❌ 不推荐SELECT*FROMemployees;-- ✅ 推荐：只查询需要的字段SELECTemployee_id,first_name,last_name,salaryFROMemployees;2. 用 TRUNCATE 代替 DELETE（清空整表时）
✅ 原理：
DELETE是 DML，逐行删除并写日志，可回滚，但慢。TRUNCATE是 DDL，直接释放数据段，不可回滚，速度快，不触发触发器。
⚠️ 注意：
TRUNCATE不能带WHERE条件。- 需要
DROP ANY TABLE权限（通常 DBA 或表所有者）。
📌 案例代码：
-- 删除所有员工记录（可回滚，慢）DELETEFROMemployees_temp;-- 清空整个表（不可回滚，快）TRUNCATETABLEemployees_temp;3. 在确保完整性的情况下多用 COMMIT
✅ 原理：
- 频繁
COMMIT可释放 UNDO 段，减少锁争用，避免 ORA-1555 错误。 - 但不要过度提交（影响事务一致性）。
📌 案例代码（批量插入 + 分批提交）：
DECLAREi NUMBER :=0;BEGINFORrecIN(SELECTlevelASid,'User_'||levelASnameFROMdualCONNECTBYlevel<=10000)LOOPINSERTINTOusers(user_id,user_name)VALUES(rec.id,rec.name);i :=i+1;-- 每 1000 行提交一次IFMOD(i,1000)=0THENCOMMIT;DBMS_OUTPUT.PUT_LINE('Committed '||i||' records');ENDIF;ENDLOOP;COMMIT;-- 提交剩余记录END;/4. 尽量减少表的查询次数
✅ 原理：
- 多次访问同一表会增加逻辑读和物理读。
- 使用子查询、连接或分析函数合并操作。
📌 案例代码：
-- ❌ 两次查询 employees 表SELECTCOUNT(*)FROMemployeesWHEREdepartment_id=10;SELECTAVG(salary)FROMemployeesWHEREdepartment_id=10;-- ✅ 一次查询完成SELECTCOUNT(*),AVG(salary)FROMemployeesWHEREdepartment_id=10;5. 用 [NOT] EXISTS 代替 [NOT] IN
✅ 原理：
NOT IN在子查询含NULL时返回空结果（逻辑陷阱）。EXISTS使用半连接（semi-join），效率更高。
📌 案例代码：
-- ❌ NOT IN：若子查询有 NULL，结果为空！SELECTemployee_idFROMemployeesWHEREdepartment_idNOTIN(SELECTdepartment_idFROMdepartmentsWHERElocation_id=1700);-- ✅ 使用 NOT EXISTS（安全且高效）SELECTe.employee_idFROMemployees eWHERENOTEXISTS(SELECT1FROMdepartments dWHEREd.department_id=e.department_idANDd.location_id=1700);三、表连接优化
1. 驱动表的选择
✅ 原理：
- 驱动表（Driving Table）是嵌套循环连接中先被扫描的表。
- 应选择结果集小的表作为驱动表。
📌 案例（使用 /*+ LEADING */ 提示）：
-- 假设 departments 表小，employees 表大SELECT/*+ LEADING(d) */e.first_name,d.department_nameFROMemployees eJOINdepartments dONe.department_id=d.department_idWHEREd.location_id=1700;2. WHERE 子句的连接顺序
✅ 原理：
- 虽然 CBO（Cost-Based Optimizer）会重排，但清晰的写法有助于阅读和调试。
- 先写连接条件，再写过滤条件。
📌 案例：
-- 推荐写法SELECTe.first_name,j.job_titleFROMemployees eJOINjobs jONe.job_id=j.job_idWHEREe.salary>5000ANDj.job_titleLIKE'%Manager%';四、合理使用索引
1. 何时使用索引？
- 列出现在
WHERE、JOIN、ORDER BY、GROUP BY中。 - 高选择性列（如主键、唯一ID）。
- 避免在低选择性列（如性别）上建单列索引。
2. 索引列和表达式的选择
❌ 错误：对列使用函数 → 索引失效
-- 索引无法使用SELECT*FROMemployeesWHEREUPPER(last_name)='KING';✅ 正确：使用函数索引 或 改写条件
-- 方式1：创建函数索引CREATEINDEXidx_emp_upper_lastnameONemployees(UPPER(last_name));-- 方式2：避免函数（如果业务允许）SELECT*FROMemployeesWHERElast_name='King';-- 假设数据规范3. 选择复合索引主列
✅ 原理：
- 复合索引
(col1, col2, col3)只在WHERE包含col1时有效（最左前缀原则）。
📌 案例：
-- 创建复合索引CREATEINDEXidx_emp_dept_salONemployees(department_id,salary);-- ✅ 能用索引SELECT*FROMemployeesWHEREdepartment_id=10ANDsalary>5000;-- ✅ 能用索引（只用到第一列）SELECT*FROMemployeesWHEREdepartment_id=10;-- ❌ 无法使用索引SELECT*FROMemployeesWHEREsalary>5000;4. 避免全表扫描大表
- 对大表（>百万行）的查询必须有索引支持。
- 使用
EXPLAIN PLAN验证是否走索引。
5. 监视索引是否被使用
-- 开启索引监控（11g 后已弃用，改用 AWR 或 V$SEGMENT_STATISTICS）-- 替代方案：查询执行计划或使用 SQL Monitor-- 查看索引统计信息SELECTindex_name,table_name,num_rows,last_analyzedFROMuser_indexesWHEREtable_name='EMPLOYEES';五、优化器的使用
1. 运行 EXPLAIN PLAN
📌 案例：
-- 生成执行计划EXPLAINPLANFORSELECTe.first_name,d.department_nameFROMemployees eJOINdepartments dONe.department_id=d.department_idWHEREe.salary>10000;-- 查看执行计划SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);输出解读关键字段：
OPERATION: 访问方式（TABLE ACCESS FULL / INDEX RANGE SCAN）COST: 估算代价ROWS: 估算返回行数
2. Oracle 中 SQL 执行计划管理（SPM）
适用于防止执行计划突变（如统计信息更新后变差）
-- 加载 SQL 到 SPM BaselineDECLAREl_plans_loaded PLS_INTEGER;BEGINl_plans_loaded :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'abc123xyz'-- 从 V$SQL 获取);END;/六、数据库和 SQL 重演
1. 数据库重演（Database Replay）
捕获生产负载，在测试环境重放（需 Enterprise Edition）
2. SQL 重演（SQL Performance Analyzer, SPA）
比较变更前后 SQL 性能（如升级、参数调整）
-- 创建 SPA 任务（示例）DECLAREl_task_name VARCHAR2(30);BEGINl_task_name :=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name=>'my_sqlset',task_name=>'spa_task_01');DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>l_task_name,execution_type=>'TEST EXECUTE');END;/七、Oracle 性能顾问
1. SQL 调优顾问（SQL Tuning Advisor）
-- 创建调优任务DECLAREl_task_name VARCHAR2(30);BEGINl_task_name :=DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text=>'SELECT * FROM employees WHERE salary > 10000',task_name=>'tune_emp_salary');DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'tune_emp_salary');END;/-- 查看建议SELECTDBMS_SQLTUNE.REPORT_TUNING_TASK('tune_emp_salary')FROMdual;2. SQL 访问顾问（SQL Access Advisor）
建议创建索引、物化视图等
DECLAREl_task_name VARCHAR2(30);l_wkld_name VARCHAR2(30);BEGIN-- 创建工作负载（可来自 SQL Cache）l_wkld_name :=DBMS_ADVISOR.CREATE_FILE(directory=>'DATA_PUMP_DIR',filename=>'workload.txt');l_task_name :=DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor','access_task_01');DBMS_ADVISOR.ADD_STS_REF(l_task_name,'MY_WORKLOAD_STS');DBMS_ADVISOR.EXECUTE_TASK(l_task_name);END;/八、综合性案例
场景：优化一个慢查询报表
原始 SQL（性能差）：
SELECT*FROMemployees e,departments d,locations lWHEREe.department_id=d.department_id(+)ANDd.location_id=l.location_id(+)ANDe.salary>(SELECTAVG(salary)FROMemployees)ANDUPPER(e.last_name)LIKE'%A%';优化步骤：
步骤1：避免SELECT *和外连接写法
-- 改为 ANSI JOIN，明确字段SELECTe.employee_id,e.first_name,e.last_name,e.salary,d.department_name,l.cityFROMemployees eLEFTJOINdepartments dONe.department_id=d.department_idLEFTJOINlocations lONd.location_id=l.location_idWHEREe.salary>(SELECTAVG(salary)FROMemployees)ANDUPPER(e.last_name)LIKE'%A%';步骤2：创建函数索引
CREATEINDEXidx_emp_upper_lastnameONemployees(UPPER(last_name));步骤3：物化子查询（避免重复计算）
WITHavg_salAS(SELECTAVG(salary)ASavg_salaryFROMemployees)SELECTe.employee_id,e.first_name,e.last_name,e.salary,d.department_name,l.cityFROMemployees eLEFTJOINdepartments dONe.department_id=d.department_idLEFTJOINlocations lONd.location_id=l.location_idCROSSJOINavg_salWHEREe.salary>avg_sal.avg_salaryANDUPPER(e.last_name)LIKE'%A%';步骤4：验证执行计划
EXPLAINPLANFOR-- 上述 WITH 查询SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);预期结果：使用
INDEX RANGE SCANonidx_emp_upper_lastname，子查询只执行一次。
总结
| 优化点 | 关键操作 |
|---|---|
| 列选择 | 避免*，只选必要字段 |
| 删除数据 | 大量清空用TRUNCATE |
| 事务控制 | 批量操作分批COMMIT |
| 子查询 | 用EXISTS替代IN |
| 索引 | 高选择性列、复合索引最左前缀、避免函数 |
| 执行计划 | 用EXPLAIN PLAN验证 |
| 自动优化 | 使用 SQL Tuning Advisor |
通过以上方法，可显著提升 Oracle SQL 性能，降低系统资源消耗。
