news 2026/4/23 17:21:35

Oracle 19c入门学习教程，从入门到精通，Oracle优化SQL语句 — 语法知识点与使用方法详解(16）

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
Oracle 19c入门学习教程，从入门到精通，Oracle优化SQL语句 — 语法知识点与使用方法详解(16）

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 为例）

  1. 以管理员身份运行安装程序OracleXE21c_Win64.exe
  2. 设置SYS / SYSTEM 用户密码（记住该密码）
  3. 选择安装路径（默认即可）
  4. 安装完成后，服务自动启动：
    • OracleServiceXE
    • OracleXETNSListener

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 性能，降低系统资源消耗。

版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/4/23 15:47:59

红外拍摄建筑缺陷数据集463张VOC+YOLO格式

红外拍摄建筑缺陷数据集463张VOCYOLO格式数据集格式&#xff1a;VOC格式YOLO格式压缩包内含&#xff1a;3个文件夹&#xff0c;分别存储图片、xml、txt文件JPEGImages文件夹中jpg图片总计&#xff1a;463Annotations文件夹中xml文件总计&#xff1a;463labels文件夹中txt文件总…

作者头像 李华
网站建设 2026/3/31 18:18:55

大学英语资源合集

过目不忘记单词动画视频 幼儿、小学、初中、高中、大学英语单词速记 文件大小: 15.5GB内容特色: 动画串联词根词缀&#xff0c;看一遍就能默写适用人群: 幼儿至大学生&#xff0c;想快速扩词汇量核心价值: 15G高能动画&#xff0c;15秒记一词终身不忘下载链接: https://pan.qu…

作者头像 李华
网站建设 2026/4/22 14:34:28

个性化学习平台：AI架构师的数据库设计

个性化学习平台&#xff1a;AI架构师的数据库设计——用数据构建“千人千面”的学习中枢 一、引入&#xff1a;从“一刀切”到“精准喂饭”&#xff0c;数据库是背后的“掌勺人” 清晨7点&#xff0c;13岁的小悠打开英语学习APP&#xff0c;首页弹出的不是千篇一律的“今日必背…

作者头像 李华
网站建设 2026/4/23 15:07:46

基于Python的交通数据分析应用(源码+lw+部署文档+讲解等)

课题介绍 本课题旨在设计实现基于Python的交通数据分析应用&#xff0c;聚焦交通管理部门、科研机构的交通流量监测、拥堵成因分析、通行效率评估及决策支撑核心需求&#xff0c;破解传统交通数据处理繁琐、分析维度单一、可视化效果差等痛点&#xff0c;构建高效精准的交通数据…

作者头像 李华