`

ch07 oracle的SQL语句优化

阅读更多

--------------------------------------SQL优化器------------------------
what:是一个为所有的sql语句创建执行计划的工具。
why:生成最快的,消耗资源最少的执行计划。
两种优化器:基于规则的优化器(RBO),基于成本的优化器(CBO)。

优化器模式
Rule 模式:完全基于数据字典生成执行计划;最古老、比较稳定;
Choose 模式:默认的优化器模式。根据统计数据的存在与否确定调用哪一个优化器;
First_rows 模式:基于成本的优化器模式,以最快的速度返回记录;
All_rows 模式:基于成本的优化器模式,确保总体时间最短,使用的资源最小;

Oracle 提供两种资料存取的机制
rule-based optimizer (RBO)
cost-based optimizer (CBO).

+---------------------------------------------------------------------------------+
+                                                                                 +                                    
+设置优化器模式的方法                                                             +
+SQL> show parameter spfile;                                                      +
+F:\oracle\product\10.2.0\db_1\dbs\initdw.ora                                     +
+Init.ora参数 optimizer_mode = rule/choose/all_rows/first_rows;                   +
+在会话层使用alter session set optimizer_goal= rule/choose/all_rows/first_rows;   +
+在SQL中添加提示/*+ rule/all_rows/first_rows */                                   +
+设置choose模式时候,将根据是否存在表或索引的统计资料来决定选择RBO或CBO;         +
+                                                                                 +
+---------------------------------------------------------------------------------+
查看oracle数据库sql语句执行计划,执行时间和统计信息

#设置sqlplus打印执行时间
SQL>set timing on

 

1、删除重复记录
最高效的删除重复记录方法 ( 因为使用了ROWID)
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
                   FROM EMP X
                   WHERE X.EMPNO = E.EMPNO);

2、用TRUNCATE(删除全表适用)替代DELETE

3、尽量多使用COMMIT

4、计算记录条数
COUNT(索引)

5、使用EXISTS(或NOT EXISTS)替代IN
低效
SELECT *
FROM EMP --(基础表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT) and rownum<100
/
高效
SELECT *
FROM EMP --(基础表)
WHERE EMPNO > 0
AND EXISTS (SELECT 'X'
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
) and rownum<100
/

6、用EXISTS替换DISTINCT
低效:
select distinct d.deptno,d.dname
from dept d,emp e
where d.deptno = e.deptno

高效:
select deptno,dname
from dept d
where exists(
select 'x' from emp e where e.deptno = d.deptno
)

EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。

+---------------------------------------------------------------------------------+
+                                                                                 + 
+识别‘低效执行’的SQL语句                                                        +
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,                                   
        ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
        ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
        SQL_TEXT
FROM   V$SQLAREA
WHERE EXECUTIONS>0
AND     BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC
/

EXECUTIONS DISK_READS BUFFER_GETS  HIT_RADIO READS_PER_RUN SQL_TEXT

         4     140555      169104        .17      35138.75
select deptno,dname from dept d where exists( select 'x' from emp e where e.dept
no = d.deptno )

         6     210767      253650        .17      35127.83
select distinct d.deptno,d.dname  from dept d,emp e where d.deptno = e.deptno
+---------------------------------------------------------------------------------+

 

7、避免在索引列上使用计算。
    WHERE子句中,如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。
低效:
select * from emp where sal*12 >25000

高效:
select * from emp where sal >25000/12

8、用>=替代>
    如果DEPTNO上有一个索引,
    低效:
   select * from emp where empno>123 and rownum<1000
    高效:
    select * from emp where empno>=124 and rownum<1000
    两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。


9、用UNION替换OR (适用于索引列)

低效:
select sal,job from emp
where sal = 800 or job = 'CLERK'

高效:
select sal,job from emp
where sal = 800
union
select sal,job from emp
where job = 'CLERK'

10、避免在索引列上使用IS NULL和IS NOT NULL
低效:
select * from emp where sal is  null
高效:
select * from emp where sal >=10000

 

11、使用提示(Hints)
    对于表的访问,可以使用两种Hints.
   FULL 和 ROWID
    FULL hint 告诉ORACLE使用全表扫描的方式访问指定表。
    例如:
 SELECT /*+ FULL(EMP) */ *
 FROM EMP
 WHERE EMPNO = 123;

    ROWID hint 告诉ORACLE使用TABLE ACCESS BY ROWID的操作访问表。
    通常, 你需要采用TABLE ACCESS BY ROWID的方式特别是当访问大表的时候, 使用这种方式, 你需要知道ROIWD的值或者使用索引。
    如果一个大表没有被设定为缓存(CACHED)表而你希望它的数据在查询结束是仍然停留在SGA中,你就可以使用CACHE hint 来告诉优化器把数据保留在SGA中。 通常CACHE hint 和 FULL hint 一起使用。
    例如:
 SELECT /*+ FULL(emp) CACHE(emp)*/ *
 FROM emp where rownum<1000;

    索引hint 告诉ORACLE使用基于索引的扫描方式。 你不必说明具体的索引名称
 例如:
 SELECT /*+ INDEX(dname) */ dname
 FROM dept
 WHERE loc ='sz'
/
    在不使用hint的情况下, 以上的查询应该也会使用索引,然而,如果该索引的重复值过多而你的优化器是CBO, 优化器就可能忽略索引。 在这种情况下, 你可以用INDEX hint强制ORACLE使用该索引。
    ORACLE hints 还包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等。
    备注:使用hint , 表示我们对ORACLE优化器缺省的执行路径不满意,需要手工修改。这是一个很有技巧性的工作。 我建议只针对特定的,少数的SQL进行hint的优化。对ORACLE的优化器还是要有信心(特别是CBO)


12、避免使用耗费资源的操作
ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。

 

 

分享到:
评论

相关推荐

    ch07-sqlserver安全性.pptx

    ch07-sqlserver安全性.pptx

    ch07创建SQL Server数据库01——创建数据库.ppt

    ch07创建SQL Server数据库01——创建数据库

    OCA Oracle Database 11g SQL Fundamentals I Exam Guide

    8. **PL/SQL基础**(ch10.pdf):介绍了Oracle的PL/SQL编程语言,包括PL/SQL块的结构、变量、常量、条件语句(IF-THEN-ELSIF-ELSE)、循环结构(WHILE、FOR)和异常处理。 9. **游标和存储过程**(ch11.pdf):详细...

    Mysql epet ch07dog表.sql

    Mysql epet ch07dog表.sql

    ch07_ibatisDemo.rar

    - **SQL映射**:通过XML文件或者注解来定义SQL语句,将SQL与Java代码分离,提高可维护性。 - **动态SQL**:MyBatis允许在SQL语句中进行条件判断,实现动态查询,增强了SQL的灵活性。 - **参数映射**:通过Map、POJO...

    EBS开发基础之 sql开发_sql_EBS_

    在EBS(Oracle Enterprise Business Suite)环境中,SQL(Structured Query Language)是不可或缺的工具,用于与数据库进行交互,包括查询、插入、更新和删除数据。本资料集合为EBS SQL开发初学者提供了一个全面的...

    数据库ch07数据库ch07.ppt

    数据库ch07数据库ch07

    oracle基础培训

    ch07SQL高级查询技术.ppt ch08管理安全性.ppt ch09PLSQL基础.ppt ch10管理表.ppt ch11索引.ppt ch12视图.ppt ch13过程、函数和程序包.ppt ch14触发器.ppt ch15事务处理和并发控制.ppt ch16闪回操作.ppt ch17数据传输...

    Beginning_Oracle_Programming

    Ch07和Ch14可能详细介绍了PL/SQL的变量声明、条件语句(IF-THEN-ELSE)、循环(LOOP)、异常处理(EXCEPTION)等内容。 4. **函数和存储过程**:在Ch09和AppC中,可能详细讲解了Oracle中的内置函数和用户自定义存储...

    Oracle Database 10g OCP Certification All-in-One Exam Guide

    "ch07.indd.pdf"可能涵盖了SQL查询和DML操作,包括SELECT语句、联接、子查询、事务处理等内容。 "ch06.indd.pdf"可能讨论的是数据库安全性和权限管理,如用户管理、角色、对象权限、系统权限等。"ch35.indd.pdf...

    ch07-2.rar

    9. **组合逻辑电路的设计**:通过布尔代数和卡诺图化简来设计和优化复杂的组合逻辑电路。 10. **时序逻辑电路**:如寄存器、移位寄存器和计数器,这些电路不仅有记忆功能,还依赖于时钟信号来控制数据的传输和处理...

    vb.net+sqlserver数据库开发.rar

    3. **ch09、ch08、ch07、ch02、ch10、ch05**:这些可能是书籍章节的文件夹,按照编号顺序排列,很可能包含了VB.NET与SQL Server数据库开发的教程内容。每个章节可能涵盖一个或多个主题,如数据库连接、数据访问控件...

    ch07 整数规划.rar_ch07_整数规划

    整数规划是运筹学和优化领域中的一个重要分支,它涉及到在满足一系列约束条件下,寻找一个整数值解的最优化问题。在这个问题中,决策变量不仅受到数学模型中线性关系的约束,还被限制必须取整数值。整数规划在实际...

    信息系统软件设计(c#.net版)CH07代码

    标题中的“信息系统软件设计(C#.NET版)CH07代码”揭示了这是一个关于C#.NET编程语言在构建信息系统中的具体应用,特别是集中在第7章的内容。C#.NET是由微软开发的一种面向对象的编程语言,它被广泛用于开发Web应用...

    SQLServer2005数据库基础及应用技术教程与实训

    3. **sql查询CH05**:介绍了SQL查询语言的基础,包括SELECT语句、JOIN操作、子查询、聚合函数以及排序和分组等,这些都是数据库查询的基本技能。 4. **索引与应用CH06**:索引能显著提高查询性能。本章节会讨论索引...

    Oracle 基础 PPT

    │ ch07SQL高___技_.ppt │ ch08管理安全性.ppt │ ch09PLSQL基_.ppt │ ch10管理表.ppt │ ch11索引.ppt │ ch12__.ppt │ ch13_程、函数和程序包.ppt │ ch14触_器.ppt │ ch15事__理和并_控制.ppt │ ch16_回操作...

    Microsoft SQL Server 2000 Professional Skills Development

    这些章节可能包括表的设计原则、数据类型的选择、关系模型、索引的创建与优化,以及如何利用Transact-SQL(T-SQL)进行数据操作和查询。其中,T-SQL是SQL Server的主要编程语言,用于执行数据库查询、更新和事务处理...

    PHP5高级应用开发实践 随书源码 (已修正sql.sql乱码)

    修正的“sql.sql乱码”问题,意味着文件内容现在是可读且无误的,这对于理解和执行SQL语句至关重要。 "README.txt"文件通常提供源码包的使用指南,可能包含了如何运行示例、安装依赖或者配置环境的说明,对于初学者...

Global site tag (gtag.js) - Google Analytics