--------------------------------------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创建SQL Server数据库01——创建数据库
8. **PL/SQL基础**(ch10.pdf):介绍了Oracle的PL/SQL编程语言,包括PL/SQL块的结构、变量、常量、条件语句(IF-THEN-ELSIF-ELSE)、循环结构(WHILE、FOR)和异常处理。 9. **游标和存储过程**(ch11.pdf):详细...
Mysql epet ch07dog表.sql
- **SQL映射**:通过XML文件或者注解来定义SQL语句,将SQL与Java代码分离,提高可维护性。 - **动态SQL**:MyBatis允许在SQL语句中进行条件判断,实现动态查询,增强了SQL的灵活性。 - **参数映射**:通过Map、POJO...
在EBS(Oracle Enterprise Business Suite)环境中,SQL(Structured Query Language)是不可或缺的工具,用于与数据库进行交互,包括查询、插入、更新和删除数据。本资料集合为EBS SQL开发初学者提供了一个全面的...
数据库ch07数据库ch07
ch07SQL高级查询技术.ppt ch08管理安全性.ppt ch09PLSQL基础.ppt ch10管理表.ppt ch11索引.ppt ch12视图.ppt ch13过程、函数和程序包.ppt ch14触发器.ppt ch15事务处理和并发控制.ppt ch16闪回操作.ppt ch17数据传输...
Ch07和Ch14可能详细介绍了PL/SQL的变量声明、条件语句(IF-THEN-ELSE)、循环(LOOP)、异常处理(EXCEPTION)等内容。 4. **函数和存储过程**:在Ch09和AppC中,可能详细讲解了Oracle中的内置函数和用户自定义存储...
"ch07.indd.pdf"可能涵盖了SQL查询和DML操作,包括SELECT语句、联接、子查询、事务处理等内容。 "ch06.indd.pdf"可能讨论的是数据库安全性和权限管理,如用户管理、角色、对象权限、系统权限等。"ch35.indd.pdf...
9. **组合逻辑电路的设计**:通过布尔代数和卡诺图化简来设计和优化复杂的组合逻辑电路。 10. **时序逻辑电路**:如寄存器、移位寄存器和计数器,这些电路不仅有记忆功能,还依赖于时钟信号来控制数据的传输和处理...
3. **ch09、ch08、ch07、ch02、ch10、ch05**:这些可能是书籍章节的文件夹,按照编号顺序排列,很可能包含了VB.NET与SQL Server数据库开发的教程内容。每个章节可能涵盖一个或多个主题,如数据库连接、数据访问控件...
整数规划是运筹学和优化领域中的一个重要分支,它涉及到在满足一系列约束条件下,寻找一个整数值解的最优化问题。在这个问题中,决策变量不仅受到数学模型中线性关系的约束,还被限制必须取整数值。整数规划在实际...
标题中的“信息系统软件设计(C#.NET版)CH07代码”揭示了这是一个关于C#.NET编程语言在构建信息系统中的具体应用,特别是集中在第7章的内容。C#.NET是由微软开发的一种面向对象的编程语言,它被广泛用于开发Web应用...
3. **sql查询CH05**:介绍了SQL查询语言的基础,包括SELECT语句、JOIN操作、子查询、聚合函数以及排序和分组等,这些都是数据库查询的基本技能。 4. **索引与应用CH06**:索引能显著提高查询性能。本章节会讨论索引...
│ ch07SQL高___技_.ppt │ ch08管理安全性.ppt │ ch09PLSQL基_.ppt │ ch10管理表.ppt │ ch11索引.ppt │ ch12__.ppt │ ch13_程、函数和程序包.ppt │ ch14触_器.ppt │ ch15事__理和并_控制.ppt │ ch16_回操作...
这些章节可能包括表的设计原则、数据类型的选择、关系模型、索引的创建与优化,以及如何利用Transact-SQL(T-SQL)进行数据操作和查询。其中,T-SQL是SQL Server的主要编程语言,用于执行数据库查询、更新和事务处理...
修正的“sql.sql乱码”问题,意味着文件内容现在是可读且无误的,这对于理解和执行SQL语句至关重要。 "README.txt"文件通常提供源码包的使用指南,可能包含了如何运行示例、安装依赖或者配置环境的说明,对于初学者...