oracle查询优化改写技巧和案例(学习笔记)

oracle查询优化改写技巧和案例（学习笔记）

第一章

1.4将空值转换成实际值

函数coalesce(exp1,exp2,...)

返回第一个为非空的值，避免了返回空值；

1.5查找满足多个条件的行

用到or：只要达到其中一个条件就可以的；

查询所有的提成的员工：即提成不为空，comm is NOT NULL;这个很好的例子；

用括号把多个条件给分隔开；

1.8在where字句中引用别名的列；

即要引用别名的列时必须是一张表中的字段；

select * from (sal as 工资,comm as 提成 from emp) x where 工资>2000;

1.9拼接列

用“||”把各列连起来

员工的工作是：ename || ‘的工作是’|| job

1.10在slect语句中使用条件逻辑

即用case来解决：格式为

字段，

case

when then ;

when then ;

else ;

end as 别名

情景：当工资<2000元时，就返回“过低”，<4000 就返回“过高”，

复习考题：（P9）要按照工资档次统计人数；

1.11限制返回的行数

用rownum是依次对数据做出的标识，是所有的数据取出来后才能确定其序号；rownum<= 2而不能rownum = 2；

查询某个序号可以这样：

select * from (select rownum as sn,emp.* from emp) where rownum<=2) where sn = 2;

1.12从表中随机返回n条记录

先随机排序，再取数据（正确）：

selectempno,ename from (select empno,ename from emp order by

dbms_random.value()) where rownum<=3;

先取数据再随机排序（错误）：

selectempno,ename from emp where rownum<=3 order by

dbms_random.value();

1.13模糊查询

通配符主要有“like"、"_"、"%"

如果查询中包含通配符就要用到转义字符：

select * from v where vname like '\_like' ESCAPE '\';

第二章给查询结果排序

2.1以指定的次序返回查询结果

ASC：升序排序，从小到大排序；desc：倒序排序，即从大到小；

order by 3 asc:表示按第三列排序：这种排序适用于该列取值不定时或者说经常改变的列，很方便，比如查询的列增加了，而我们只是排序第一列；

2.2按多个字段排序

排序的字段要用","分开，比如：order by A desc, B asc;

2.3按字符串排序

用到了substr（）函数；主要是运用了快速查找顾客的电话的尾号4的顺序；

substr(phone_number,-4) 表示后四位；

2.4translate()函数

translate(expr,from_string,to_string):from_string和to_string以字符为单位，对应字符一一替换；

如果to_string 为空，则返回空值；如果to_string对应的位置没有字符，删除from_string 中列出的字符将会被消掉；

运用：

2.5按数字和字母混合字符串中的字母排序

把重要的东西提取出来，次要的东西踢掉，这道题是对translate的运用；

先构造视图

create or replace view v as

selectempno || '' || ename as data from emp;

selectdata,translate(data,'-0123456789','-') as ename from v order by 2;

2.6处理排序空值

在order by 的后面加个NULLS FIRST或NULLS LAST;

2.7根据条件不同列中的值来排序

领导对工资在1000到2000的感兴趣；

select empno as 编码，ename as 姓名,sal as 工资from emp where deptmo = 30 order by case when sal>= 1000 and sal<2000 then 1 else 2 end,3;

第三章操作多个表（P要操作）

2015/04/29 17:00

3.1union all 与空字符串

空字符串不等于空值；

3.2union与or

一般不用union all，这样避免的重复的数据；(P25要复习)

为了消除bitmap convert的影响：alter session set "_b_tree_bitmap_plans" = false; 但有是用union或or会被忽略，从而出现错误；不过加入一个唯一标识后，即保证了正确去重，又防止了不该发生的去重。

还可以用rowid来解决union或or的去重问题；

数据取自视图用的是rownum；

create or replace view v as

selecte.deptno,e.mgr,e.job,d.dname from emp e

inner join dept d on d.deptno=e.deptno;

with e as (select rownum as sn,deptno,mgr,job,from v)

selectdeptno

from(select sn ,deptno,from e where mgr = 7698

union

selectsn,deptno,from e where job = 'SALESMAN')

order by 1;

这里的with之后的“e”只是临时创建一个view只在查询期存在，执行with完之后就消失；

2015/05/26 11:15

为了消除bitmp convert的影响，设置参数为

alter session set "_b_tree_bitmap_plans" = false;

对比plan

explain plan for select empno,ename,fromemp where wmpno = 7788 or ename = 'SCOTT';

select * from table(dbms_xplan.display)

第八章：日期操作

2016/04/10 15:29

8.6确定一年内属于周内某一天的所有日期

with x as

(selecttrunc(sysdate,'y')+(level-1) dy from dual

connect by level<=add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')

)

select dy,to_char(dy,'dy') as 周五from x where to_char(dy,'d') = 6;

第十章高级查询

2015/04/24 16:54

10.1给结果集分页

第十一章报表和数据仓库运算

2015/04/27 09:44

11.1行转列

1.使用case when end

查询需求为对emp按job分组汇总，每个部门显示为一列。

第一种情况：

select job as 工作,

case deptno when 10 then sal end as 部门10工资，

case deptno when 20 then sal end as 部门20工资，

case deptno when 10 then sal end as 部门30工资，

sal as 合计工资from emp order by 1;

第二种情况：

select job as 工作，sum（case deptno when 10 then sal end ）as 部门10工作，

sum（case deptno when 20 then sal end ）as 部门20工作，

sum ( case deptno when 30 then sal end ) as 部门30工作，

sum(sal) as 合计工资；

2.使用pivot函数

select * from (select job ,sal,com,deptno from emp) pivot(sum(sal) as

s,sum(comm)) as c

fordeptno in ( 10 as ds10,20 as ds20,30 as ds30) order by 1;

11.2列转行

1.使用UNion All ；（10g以前常用，但不方便）

2.使用unpivot函数;（11g以后就可以用，方便）

select * from table_nameunpivot(new_column_name1 for new_column_name2 in(column_name1,column_name2,column_name3) )

2015/04/30 09:54

11.3将结果集反向转置为一列

selectemps from (select enames,job,to_char(sal) as sal,null as t_col from emp where deptno = 10)

unpivot include nulls(emps for col in(ename,job,sal,t_col));

参数inclue nulls表示即使数据为空也显示这一行；

第十二章分层查询

2015/12/06 14:34

12.8：字段内list值去重

某个字段里存的值如这样的（心尘;重复;java;oracle;猪猪;幸福;重复;皮卡丘;远洋;java;燕燕;），那么如何去掉重复的值呢？

1.如果要处理的表只有一行数据，可以只使用regexp_substr(),regexp_count()和connec by level，即可处理，

其sql语句为：

selectregexp_substr(col_name,'[^;]+',1,level) from table_name

connect by level <=regexp_count(col_name,';') +1;

未完待续！可是继续关注

2020年(Oracle管理)如何优化SQL语句以提高Oracle执行效率

（Oracle管理）如何优化SQL语句以提高Oracle执 行效率

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表drivingtable)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表。 （2）WHERE子句中的连接顺序： Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。（3）SELECT子句中避免使用‘*’： Oracle在解析的过程中,会将‘*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。 （4）减少访问数据库的次数： Oracle在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等。（5）在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200。 （6）使用DECODE函数来减少处理时间： 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。 （7）整合简单,无关联的数据库访问： 如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)。 （8）删除重复记录： 最高效的删除重复记录方法(因为使用了ROWID)例子：DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)

优化sql语句提高oracle执行效率(34种方法)

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表driving table)将被最先处理，在 FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表 (intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。 （2）WHERE子句中的连接顺序： Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录 的条件必须写在WHERE子句的末尾。 （3）SELECT子句中避免使用‘*’： Oracle在解析的过程中, 会将‘*’依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。 （4）减少访问数据库的次数： Oracle在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量, 读数据块等。 （5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量,建议值为200。 （6）使用DECODE函数来减少处理时间： 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。 （7）整合简单,无关联的数据库访问： 如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)。 （8）删除重复记录： 最高效的删除重复记录方法( 因为使用了ROWID)例子： DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)

OracleSQL性能优化方法

OracleSQL性能优化方法 Oracle性能优化方法(SQL篇) (1) 1综述 (2) 2表分区的应用 (2) 3访咨询Table的方式 (3) 4共享SQL语句 (3) 5选择最有效率的表名顺序 (5) 6WHERE子句中的连接顺序． (6) 7SELECT子句中幸免使用’*’ (6) 8减少访咨询数据库的次数 (6) 9使用DECODE函数来减少处理时刻 (7) 10整合简单,无关联的数据库访咨询 (8) 11删除重复记录 (8) 12用TRUNCATE替代DELETE (9) 13尽量多使用COMMIT (9) 14运算记录条数 (9) 15用Where子句替换HA VING子句 (9) 16减少对表的查询 (10) 17通过内部函数提高SQL效率 (11) 18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21识不低效执行的SQL语句 (13) 22使用TKPROF 工具来查询SQL性能状态 (14) 23用EXPLAIN PLAN 分析SQL语句 (14) 24实时批量的处理 (16)

1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要通过反反复复的过程。在数据库建立时，就能依照顾用的需要合理设计分配表空间以及储备参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存体会，从而更好地进行数据库的调优。 数据库性能调优的方法 ●调整内存 ●调整I/O ●调整资源的争用咨询题 ●调整操作系统参数 ●调整数据库的设计 ●调整应用程序 本文针对应用程序的调整，来讲明对数据库性能如何进行优化。 2表分区的应用 关于海量数据的表，能够考虑建立分区以提高操作效率。建立分区一样以关键字为分区的标志，也能够以其他字段作为分区的标志，但效率不如关键字高。建立分区的语句在建表时能够进行讲明： create table TABLENAME() partition by range (PutOutNo) (partition PART1 values lessthan (200312319999) partition PART2 values lessthan (200412319999) 。。。。。。 如此，在进行大部分数据查询，数据更新和数据插入时，Oracle自动判定操作应该在哪个分区进行，幸免了整表操作，提高了执行的效率

Oracle SQL性能优化方法研究

Oracle SQL性能优化方法探讨 Oracle性能优化方法(SQL篇) (1) 1综述 (2) 2表分区的应用 (2) 3访问Table的方式 (3) 4共享SQL语句 (3) 5选择最有效率的表名顺序 (5) 6WHERE子句中的连接顺序． (6) 7SELECT子句中幸免使用’*’ (6) 8减少访问数据库的次数 (6) 9使用DECODE函数来减少处理时刻 (7) 10整合简单,无关联的数据库访问 (8) 11删除重复记录 (8) 12用TRUNCATE替代DELETE (9) 13尽量多使用COMMIT (9) 14计算记录条数 (9) 15用Where子句替换HAVING子句 (9) 16减少对表的查询 (10) 17通过内部函数提高SQL效率 (11)

18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21识不低效执行的SQL语句 (13) 22使用TKPROF 工具来查询SQL性能状态 (14) 23用EXPLAIN PLAN 分析SQL语句 (14) 24实时批量的处理 (16)

1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要通过反反复复的过程。在数据库建立时，就能依照顾用的需要合理设计分配表空间以及存储参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存经验，从而更好地进行数据库的调优。 数据库性能调优的方法 ●调整内存 ●调整I/O ●调整资源的争用问题 ●调整操作系统参数 ●调整数据库的设计 ●调整应用程序 本文针对应用程序的调整，来讲明对数据库性能如何进行优化。 2表分区的应用 关于海量数据的表，能够考虑建立分区以提高操作效率。建

确保Oracle数据库sql语句高效执行的优化总结(一)

SQL 的优化主要涉及几个方面： （1）相关的统计信息缺失或者不准确 （2）索引问题 （3）SQL的本身的效率问题，比如使用绑定变量，批量DML 采用bulk等，这个就考验写SQL的基本功了，这一点也是最主要的一点。 一、SQL 编写注意事项 1.1 查看SQL 对于生产环境上的SQL,可以从AWR 或者Statspack 报告中获取相关的SQL信息。这部分参考： 查看SQL 的性能怎么样，最直接的工具就是通过执行计划，通过执行计划可以看到SQL 的执行路径，逻辑读，物理读等信息，可以这些信息，可以帮助我们判断SQL 是否还有优化的余地。 1.2 SQL 编写的具体注意事项 这部分工作是基本功。在SQL 编写过程中，避免一些低效的写法，能将SQL 的效率提高几倍。如： 与使用TRUNC 相比，使用TO_CHAR 所用的CPU 时间与前者相差一个数量级（即相差12倍）。因为TO_CHAR 必须把日期转换为一个串，这要使用一

个更大的代码路径，并利用当前的所有NLS来完成这个工作。然后必须执行一个串与串的比较。另一方面，TRUNC 只需把后5 个字节设置为1.然后将两个7 字节的二进制数进行比较。因此，如果只是要截断一个DATE 列，你将应该避免使用TO_CHAR。 1.3 多表关联方式 表之间的关联有如下三种方式： （1）Nested Loop Inner table 循环与outer table匹配，这种是表有索引，选择性较好，表之间的差距不大。===》两层for 循环，小表匹配大表。 （2）Hash John 小表做hash ，放内存，然后拿大表的每条记录做hash，然后与之前小表的Hash 值匹配。==》大表匹配小表。 （3）Sorted Merge Into 表有序，并且没有索引。 二. 相关理论说明 2.1 Oracle 优化器：CBO 和RBO Oracle 的优化器有两种： （1）RBO(Rule-BasedOptimization): 基于规则的优化器 （2）CBO(Cost-BasedOptimization): 基于代价的优化器

Oracle性能优化总结

个人理解，数据库性能最关键的因素在于IO，因为操作存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个人理解应该分为物理的和逻辑的优化，物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化 物理优化： 一、优化存

3.管理员可以通过下述语句来查看数据缓冲区的使用情况 select name,value from v$sysstat where name in('db block gets','consistent gets','physica l reads'); 数据缓冲区使用命中率（physical reads除以db block gets加consistent gets之和)一定要小于10%，否则需要增加数据缓冲区大小 4.管理员可以通过执行下述语句，查看日志缓冲区的使用情况 select name,value from v$sysstat where name in ('redo entries','redo log space requests') 根据查询出的结果可以计算出日志缓冲区的申请失败率：requests除以entries 申请失败率应该解决与0，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区 二、物理I/0的优化 1.在磁盘上建立数据文件前首先运行磁盘碎片整理程序 为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。如果有足够的连续磁盘空间建立数据文件，那么就容易避免数据文件产生碎片。 2.不要使用磁盘压缩（Oracle文件不支持磁盘压缩） 3.不要使用磁盘加密 加密像磁盘压缩一样加了一个处理层，降低磁盘读写速度。如果担心自己的数据可能泄露，可以使用dbms_obfuscation包和label security选择性地加密数据的敏感部分 4.使用RAID raid使用应注意： 选择硬件raid超过软件raid；日志文件不要放在raid5卷上，因为raid5读性能高而写性能差；把日志文件和归档日志放在与控制文件和数据文件分离的磁盘控制系统上 5.分离页面交换文件到多个磁盘物理卷 跨越至少两个磁盘建立两个页面文件。可以建立四个页面文件并在性能上受益，确保所有页面文件的大小之和至少是物理存的两倍。

oracle查询优化改写技巧和案例(学习笔记)

oracle查询优化改写技巧和案例（学习笔记） 第一章 将空值转换成实际值 函数coalesce(exp1,exp2,...) 返回第一个为非空的值，避免了返回空值； 查找满足多个条件的行 用到or：只要达到其中一个条件就可以的； 查询所有的提成的员工：即提成不为空，comm is NOT NULL;这个很好的例子； 用括号把多个条件给分隔开； 在where字句中引用别名的列； 即要引用别名的列时必须是一张表中的字段； select * from (sal as 工资 ,comm as 提成from emp) x where 工资 >2000; 拼接列 用“||”把各列连起来 员工的工作是：ename || ‘的工作是’|| job 在slect语句中使用条件逻辑 即用case来解决：格式为 字段， case when then ; when then ; else ; end as 别名 情景：当工资<2000元时，就返回“过低”，<4000 就返回“过高”， 复习考题：（P9）要按照工资档次统计人数； 限制返回的行数 用rownum是依次对数据做出的标识，是所有的数据取出来后才能确定其序号； rownum<= 2而不能rownum = 2； 查询某个序号可以这样： select * from (select rownum as sn,emp.* from emp) where rownum <=2) where sn = 2;

从表中随机返回n条记录 先随机排序，再取数据（正确）： select empno,ename from (select empno,ename from emp order by ()) where rownum <=3;先取数据再随机排序（错误）： select empno,ename from emp where rownum <=3 order by (); 模糊查询 通配符主要有“like"、"_"、"%" 如果查询中包含通配符就要用到转义字符： select * from v where vname like '\_like' ESCAPE '\'; 第二章给查询结果排序 以指定的次序返回查询结果 ASC：升序排序，从小到大排序；desc：倒序排序，即从大到小； order by 3 asc:表示按第三列排序：这种排序适用于该列取值不定时或者说经常改变的列，很方便，比如查询的列增加了，而我们只是排序第一列； 按多个字段排序 排序的字段要用","分开，比如：order by A desc, B asc; 按字符串排序 用到了substr（）函数；主要是运用了快速查找顾客的电话的尾号4的顺序； substr(phone_number,-4) 表示后四位； ()函数 translate(expr,from_string,to_string):from_string和to_string以字符为单位，对应字符一一替换； 如果to_string 为空，则返回空值；如果to_string对应的位置没有字符，删除 from_string中列出的字符将会被消掉； 运用： 按数字和字母混合字符串中的字母排序 把重要的东西提取出来，次要的东西踢掉，这道题是对translate的运用； 先构造视图 create or replace view v as select empno || '' || ename as data from emp;

基于ORACLE的查询优化

基于ORACLE的查询优化 一、DBMS查询规则 查询优化是在查询执行引擎生成一个执行策略的过程中，尽量使查询的总开销和总时间达到最小。 任何关系数据库都有一套解决查询的规则，而各种关系数据库查询的过程稍有所区别，但是基本的操作思想和过程是一致的。DBMS的查询规则一般分为以下几个过程：语法分析与翻译处理；查询优化处理；执行。 图1 DBMS查询规则图 1.语法分析与翻译处理 查询处理开始之前，系统必须在扩展关系代数的基础上将

查询语句翻译成可使用的形式，为此这一阶段完成两个主要的功能：一是检查查询语句语法的正确性；二是把查询语句分析成关系数据库能够理解的各个组成部分，构造该查询的语法分析树，并将其翻译成关系代数表达式。 2.查询优化处理 查询优化是为查询选择最有效的查询执行计划的过程。查询优化一方面在关系代数级进行优化，要做的是力图找出与给定表达式等价且执行效率最高的一个表达式。为了在诸多查询执行计划中做出选择，优化器必须估计每个查询执行计划的代价。在没有真正执行查询计划之前，准确计算出查询计划的代价通常是不可能的。因此，优化器要利用各关系的统计信息，来对计划做出最佳估计。 3.执行 执行就是将优化器所选择的执行计划执行，执行引擎从相应的数据库文件中筛选数据，将结果输出。 优化器在选取优化规则时，大多数DBMS(如ORACLE、SYBASE、SQL SERVER等)都是采用基于代价的优化方法，优化器从数据字典中获取许多统计信息，根据不同的算法估计出不同查询规划的代价，然后选择一个较优的规划。

对于ORACLE，在ORACLE7之前，主要是使用基于规则的优化器RULE BASED OPTIMIZATION (简称RBO)。从ORACLE8开始，ORACLE把一个代价引擎(COST ENGINE)集成到数据库内核中，用来估计每个执行计划需要的代价，该代价将每个执行计划所耗费的资源进行量化，从而CBO可以根据这个代价选择出最优的执行计划，即基于代价的优化器COST BASED OPTIMIZATION(简称CBO)。 要注意的是：虽然CBO的功能随着ORACLE新版本的推出，功能越来越强，但它不是能包治百病的神药，否则就不再需要DBA了。 由于一系列因素都会影响语句的执行，优化器综合权衡各个因素，在众多的执行计划中选择认为是最佳的执行计划。然而，应用设计人员通常比优化器更知道关于特定应用的数据特点。无论优化器多么智能，在某些情况下开发人员能选择出比优化器选择的最优执行计划还要好的执行计划，这是需要人工干预数据库优化的主要原因。事实表明，在某些情况下，确实需要DBA对某些语句进行手工优化。 二SQL语句优化步骤 数据库系统的生命周期可分为设计、开发和成品三个阶段。

oracle性能优化总结

Oracle 性能优化50个方法https://www.wendangku.net/doc/fb10007048.html,

1. 选用适合的ORACLE优化器 ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器. 2. 访问Table的方式 ORACLE 采用两种访问表中记录的方式: a. 全表扫描全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描,这样的访问方式是效率最低的. b. 通过ROWID访问表你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索 引列的查询就可以得到性能上的提高. 3. 共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用. 可惜的是ORACLE 只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询. 数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达 成共享,SQL语句必须完全相同(包括空格,换行等). 共享的语句必须满足三个条件: A. 字符级的比较: 当前被执行的语句和共享池中的语句必须完全相同. 例如: SELECT * FROM EMP; 和下列每一个都不同 SELECT * from EMP; Select * From Emp;

ORACLE性能优化之SQL优化-优化器

Oracle9i优化器介绍 By Davis E-Mail:todavis@https://www.wendangku.net/doc/fb10007048.html, Blog:https://www.wendangku.net/doc/fb10007048.html, 选择合适的优化器目标 默认情况下，CBO 以最佳吞吐量为目标，这意味着Oracle 使用尽可能少的资源去处理被语句访问到的所有行；当然CBO 也可以用最快的响应速度来优化SQL，这意味着Oracle 用尽可能少的资源去处理被语句访问到的第一行或前面少数行，当然这种情况对于整个语句 来说可能消耗更多的资源。 优化器产生的执行计划会因―优化器目标‖的不同而不同。如果以最佳吞吐量为目标， 结果更倾向于使用全表扫描而不是索引扫描，或者使用排序合并连接而不是嵌套循环连接；如果以最快的响应速度为目标，其结果则通常倾向于使用索引扫描和嵌套循环连接。 例如，假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接，排序合并连接能够较快的返回全部查询结果，而嵌套循环能快速的返回第一行或前面少数行结果。如果你是以提高吞吐量为优化器目标，优化器就会倾向于选择排序合并连接；如果你的优化器目标是提高响应速度，则优化器倾向于选择嵌套循环连接。 选择优化器目标要以你的应用为基础，一般规则是： 1、对于批处理应用，以最佳吞吐量为优化目标为好。例如Oracle 报表应用程序。 2、对于交互式应用，以最快响应速度为优化目标为好。例如SQLPLUS 的查询。 影响优化器优化目标的因素主要有： 1、OPTIMIZER_MODE 初始化参数。 2、数据字典中的CBO 统计数据。 3、用来改变CBO 优化目标的Hints。 OPTIMIZER_MODE初始化参数 这个初始化参数用来规定实例的默认优化方法。其值列表及说明如下： Value CHOOSE ALL_ROWS Description 此为缺省值。优化器既可以使用基于成本的优化方法(CBO)，也可以使用基于规则的优化方法(RBO)，其决定于是否有可用的统计信息。 1、如果在被访问的表中，至少有一个表在数据字典中有可用的统计 信息存在，则优化器使用基于成本的方法。 2、如果在被访问的表中，只有部分表在数据字典中有可用的统计信 息，优化器仍然会使用基于成本的方法，但是优化器必须为无统 计信息的表利用一些内部信息去尝试其他的统计，比如分配给这 些表的数据块的数量等，这可能会导致产生不理想的执行计划。 3、如果在被访问的表中，没有一个表在数据字典中有统计信息，则 优化器使用基于规则的方法。 不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞 1

oracle查询优化改写技巧和案例(学习笔记)

o r a c l e查询优化改写技巧和案例(学习笔记) -标准化文件发布号：（9556-EUATWK-MWUB-WUNN-INNUL-DDQTY-KII

oracle查询优化改写技巧和案例（学习笔记） 第一章 将空值转换成实际值 函数coalesce(exp1,exp2,...) 返回第一个为非空的值，避免了返回空值； 查找满足多个条件的行 用到or：只要达到其中一个条件就可以的； 查询所有的提成的员工：即提成不为空，comm is NOT NULL;这个很好的例子； 用括号把多个条件给分隔开； 在where字句中引用别名的列； 即要引用别名的列时必须是一张表中的字段； select * from (sal as 工资 ,comm as 提成from emp) x where 工资 >2000; 拼接列 用“||”把各列连起来 员工的工作是：ename || ‘的工作是’|| job 在slect语句中使用条件逻辑 即用case来解决：格式为 字段， case when then ; when then ; else ; end as 别名 情景：当工资<2000元时，就返回“过低”，<4000 就返回“过高”， 复习考题：（P9）要按照工资档次统计人数； 限制返回的行数 用rownum是依次对数据做出的标识，是所有的数据取出来后才能确定其序号；rownum<= 2而不能rownum = 2； 查询某个序号可以这样： select * from (select rownum as sn,emp.* from emp) where rownum <=2) where sn = 2;

oracle sql性能优化题目

1.下面哪些是sql语句处理过程ABCD （A）分析（B）优化（C）行资源生成（D）执行 2.sql语句在分析过程中要进行哪些操作？ABC （A）语法分析（B）语义分析（C）如果是DML，还有共享池检查（D）优化 3.下面对索引的描述哪些是正确的ABCD （A）类似书的目录结构 （B）可以提高sql的查询速度 （C）会降低insert、update、delete的速度 （D）与所索引的表是互相独立的物理结构 （E）储存null 4.索引有哪几种扫描方式ABCDE （A）唯一索引扫描（B）索引范围扫（C）索引跳跃扫描（D）索引全扫描（E）索引快速扫描 5.下列哪些属于索引的类型：ABCD （A）B-tree索引（B）函数索引（C）全局索引（D）本地索引 6.下列对建立索引说法正确的是：AD （A）where后面的条件具备建立索引的先天条件 （B）索引的列越多越好 （C）所有的列都可以建立索引 （D）哪个列能快速定位数据，那么那个列就是建立索引的列 7.一般来说2张表连接有哪几种方式？ABC （A）NESTED LOOPS（B）HASH JOIN（C）SORT MERGE JOIN（D）FULL JOIN 8.对NESTED LOOPS表连接来说，下面哪些说法是正确的：AC （A）drivingrowsource(外部表)比较小 （B）只能用于等值连接中 （C）innerrowsource(内部表) 有高选择率的索引

（D）连接之前需要排序 9.sql 在数据库共享池中能否共享的说法哪些是正确的？ACD （A）sql必须是同一个用户执行的 （B）执行的sql不区分大小写 （C）执行的sql所处的当时的数据库环境必须是一样的 （D）同样的sql生成的HASH值一定是一样的 10.以下对绑定变量的说法正确的是：ABD （A）绑定变量能减少硬解析的次数 （B）绑定变量有的时候会引起执行计划的错误选择 （C）绑定变量不会带来性能问题 （D）对数据分布很不均匀的列不适合使用绑定变量 12.下面哪些方法可以取得sql的执行计划ABCD （A）PL/SQL DEVELOP 按F5 （B）Dbms_xplan.display_cursor （C）查询视图v$sql_plan （D）SET AUTOTRACE ON 18.下面哪些sql的写法是可能会造成性能问题的（where条件的字段均有索引）？ABCD （A）SELECT * FROM T_NULL WHERE OBJECT_ID ISNULL; （B）SELECT * FROM A_PAY_FLOW WHERE C.SETTLE_MODE=NVL(:B1,C.SETTLE_MODE) ; （C）SELECT * FROM A_CASHCHK WHERE TO_CHAR(RELATE_NO)=TO_CHAR(:B4); （D）SELECTCOUNT(*) FROM S_REGION_OUTGE WHERESYSDATE-A.START_TIME<=30; 19.下面哪些sql的写法是可能会造成性能问题的（where条件的字段均有索引）？ABCDE （A）SELECTCOUNT(*) FROM O_ORG WHERE SCCIFSTATORG(ORG_NO, ‘02’) =1; （B）SELECT T1.OWNER, T1.OBJECT_ID, F_GETNAME(OBJECT_ID) FROM T_FROM1 T1 WHERE OBJECT_ID <2000; （C）SELECT * FROM S_APP WHERE CONS_NAME LIKE‘%’||:2||’%’ （D）SELECTMIN(OBJECT_ID),MAX(OBJECT_ID) FROM T1; （E）INSERTINTOTABLESELECT XXXX FROM DUAL;

Oracle的SQL语句执行效率问题查找与解决方法

Oracle的SQL语句执行效率问题查找与解决方法 一、识别占用资源较多的语句的方法（4种方法） 1.测试组和最终用户反馈的与反应缓慢有关的问题。 2.利用V_$SQLAREA视图提供了执行的细节。（执行、读取磁盘和读取缓冲区的次数） ?数据列 EXECUTIONS：执行次数 DISK_READS：读盘次数 COMMAND_TYPE：命令类型（3:select,2:insert;6:update;7delete;47:pl/sql程序 单元） OPTIMIZER_MODE：优化方式 SQL_TEXT：Sql语句 SHARABLE_MEM：占用shared pool的内存多少 BUFFER_GETS：读取缓冲区的次数 ?用途 1、帮忙找出性能较差的SQL语句 2、帮忙找出最高频率的SQL 3、帮忙分析是否需要索引或改善联接 3.监控当前Oracle的session，如出现时钟的标志，表示此进程中的sql运行时间较长。 4.Trace工具： a)查看数据库服务的初始参数：timed_statistics、user_dump_dest和 max_dump_file_size b)Step 1: alter session set sql_trace=true c)Step 2: run sql； d)Step 3: alter session set sql_trace=false e)Step 4:使用“TKPROF”转换跟踪文件 f)Parse，解析数量大通常表明需要增加数据库服务器的共享池大小， query或current提取数量大表明如果没有索引，语句可能会运行得更有效， disk提取数量表明索引有可能改进性能， library cache中多于一次的错过表明需要一个更大的共享池大小 二、如何管理语句处理和选项 ?基于成本（Cost Based）和基于规则（Rule Based）两种优化器，简称为CBO 和RBO ?Optimizer Mode参数值: Choose：如果存在访问过的任何表的统计数据，则使用基于成本的Optimizer,目标是获得最优的通过量。如果一些表没有统计数据，则使用估计值。如果没有可用的统计数据，则将使用基于规则的Optimizer All_rows：总是使用基于成本的Optimizer，目标是获得最优的通过量 First_rows_n：总是使用基于成本的Optimizer，目标是对返回前N行（“n”可以是1，10，100或者1000）获得最优的响应时间 First_rows：用于向后兼容。使用成本与试探性方法的结合，以便快速传递前几行 RULE：总是使用基于规则的Optimizer 三、使用数据库特性来获得有助于查看性能的处理统计信息（解释计划和AUTOTRACE） No1: Explain Plan A）使用Explain工具需要创建Explain_plan表，这必须先进入相关应用表、视图和索引