/**PageBeginHtml Block Begin **/ /***自定义返回顶部小火箭***/ /*生成博客目录的JS 开始*/ /*生成博客目录的JS 结束*/

优化SQL语句

* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。
* @author Alan
* @Email no008@foxmail.com

 

正文


前言

image







一：常规SQL语句优化

image

1：建议 不用  *  来替代所有列名

image


2：用 truncate 替代 delete

image

           示例：创建一个存储过程，实现使用 truncate 命令动态删除数据表。

复制代码
  1 SYS@orcl> create or replace procedure trun_table(table_deleted in varchar2) as --创建一个存储过程，传入一个表示表名称的参数，实现清空指定的表
  2   2    cur_name integer;--定义内部变量，存储打开的游标
  3   3  begin
  4   4    cur_name := dbms_sql.open_cursor;--打开游标
  5   5    dbms_sql.parse(cur_name,'truncate table'||table_deleted ||'drop storage',dbms_sql.native);--执行truncate table tb_name命令，从而实现清空指定的表
  6   6    dbms_sql.close_cursor(cur_name);--关闭游标
  7   7  exception
  8   8    when others then dbms_sql.close_cursor(cur_name);--出现异常，关闭游标
  9   9    raise;
 10  10  end trun_table;
 11  11  /
 12 
 13 Procedure created.
 14 
 15 SYS@orcl>
复制代码


3：在确保完整性的情况下多使用 commit 语句

image


4：尽量减少表的查询次数

在含有子查询的sql语句中，要特别注意减少对表的查询。

  • 1：低效率的SQL查询语句
  1  SCOTT@orcl> select empno,ename,job from emp where deptno in(select deptno from dept where loc='BEIJING') OR DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');
  2 
  3 no rows selected
  4
  • 2：对上面的代码进行适当的修改。高效率的SQL查询语句如下：
  1 
  2 
  3 SCOTT@orcl> select empno,ename,job from emp where deptno in(select deptno from dept where loc='BEIJING' OR LOC='NEW YORK');
  4 
  5 no rows selected
  6 
  7 SCOTT@orcl>

image


5： 用 [not] exists 替代 【not】 in

image

  •        1 ：低效率的 not  in 子句

复制代码
  1 SCOTT@orcl> select empno,ename from emp where deptno not in (select deptno from dept where loc='BEIJING');
  2 
  3      EMPNO ENAME
  4 ---------- ----------
  5       9527 EAST
  6       7934 MILLER
  7       7839 KING
  8       7782 CLARK
  9       8889 dfadf
 10       7900 JAMES
 11       7844 TURNER
 12       7698 BLAKE
 13       7654 MARTIN
 14       7521 WARD
 15       7499 ALLEN
 16 
 17      EMPNO ENAME
 18 ---------- ----------
 19       7902 FORD
 20       7876 ADAMS
 21       7788 SCOTT
 22       7566 JONES
 23       7369 SMITH
 24 
 25 16 rows selected.
 26 
 27 SCOTT@orcl>
复制代码

  •        2：高效的exists 子句

复制代码
  1 
  2 SCOTT@orcl> select empno,ename from emp where  exists (select deptno from dept where loc !='BEIJING');
  3 
  4      EMPNO ENAME
  5 ---------- ----------
  6       9527 EAST
  7       8889 dfadf
  8       7369 SMITH
  9       7499 ALLEN
 10       7521 WARD
 11       7566 JONES
 12       7654 MARTIN
 13       7698 BLAKE
 14       7782 CLARK
 15       7788 SCOTT
 16       7839 KING
 17 
 18      EMPNO ENAME
 19 ---------- ----------
 20       7844 TURNER
 21       7876 ADAMS
 22       7900 JAMES
 23       7902 FORD
 24       7934 MILLER
 25 
 26 16 rows selected.
 27 
 28 SCOTT@orcl>
复制代码


image


二：表链接优化

image

1：驱动表的选择

image


2：where 子句的链接顺序

image



三：合理使用索引

image

1：何时使用索引

image


2：索引列和表达式的选择

image


3：选择复合索引主列

imageimage

           示例：为tb_test 表创建 一个复合索引complex_inde 该索引包括 column1、column2、column3个列。

image


4：避免全表扫描大表

image



5：监视索引是否被使用

imageimage

         示例：监视学生成绩表 studentgrade 的grade_index是否被使用。

  • 1：设置监视索引 grade_index ：
复制代码
  1 SCOTT@orcl> desc studentgrade;
  2  Name                                      Null?    Type
  3  ----------------------------------------- -------- ----------------------------
  4  ID                                        NOT NULL NUMBER
  5  NAME                                               VARCHAR2(10)
  6  SUBJECT                                            VARCHAR2(10)
  7  GRADE                                              NUMBER
  8 
  9 SCOTT@orcl> alter index grade_index monitoring usage;
 10 
 11 Index altered.
 12 
 13 SCOTT@orcl>
复制代码
  • 2 检查索引使用情况
复制代码
  1 
  2 SCOTT@orcl> select * from v$object_usage;
  3 
  4 INDEX_NAME                     TABLE_NAME                     MON USE
  5 ------------------------------ ------------------------------ --- ---
  6 START_MONITORING    END_MONITORING
  7 ------------------- -------------------
  8 GRADE_INDEX                    STUDENTGRADE                   YES NO
  9 03/27/2018 22:28:19
 10 
 11 
 12 SCOTT@orcl>
复制代码
  • 3 在第二步的检测中，如果发现索引grade_index 在限定时间内得不到使用(即：used列的值为NO)则建议使用drop 　index 语句删除；
  1 SCOTT@orcl> drop index grade_index;
  2 
  3 Index dropped.
  4 
  5 SCOTT@orcl>


四：优化器的使用

image


1：优化器的概念

imageimage

image



2：运行 explan plan

image



3： oracle 11g 中的sql 执行计划的管理

imageimage



五：数据库和SQL重演

image


1： 数据库重演

image

2：sql 重演

image

image



六： oracle 性能顾问

image

1：SQL 调优顾问

image


2：SQL访问顾问

image





















—————————————————————————————————————————————————————————————————————————————————————————————————

posted @   一品堂.技术学习笔记  阅读(517)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛

目录导航