day18——sql优化
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优化器,而直接采用基于规则或者基于成本的优化器
SQL> show parameter optimizer_mode;
NAME TYPE VALUE
------------------------------------ ----------- ---------
optimizer_mode string ALL_ROWS
ALL_ROWS:是一种基于成本的优化器,它将选择一种在最短时间内返回所有数据的执行计划
访问表的方式
全表扫描:
采用顺序访问的方式访问每条记录,实际过程中oracle一次读入多个数据块来加快全表扫描
实际数据库中通过参数db_file_multiblock_read_count来控制一次读取的块的数量.对该参数进行合理配置可以优化I/O
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- -------
db_file_multiblock_read_count integer 16
===================================================
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) ,这个功能并不适用于多表连接查询.
A. 字符级的比较:
当前被执行的语句和共享池中的语句必须完全相同.
例如:
SELECT * FROM EMP;
和下列每一个都不同
SELECT * from EMP;
Select * From Emp;
SELECT * FROM emp;
=======================================
B. 两个语句所指的对象必须完全相同:
例如:
用户 对象名 如何访问
Jack sal_limit private synonym
Work_city public synonym
Plant_detail public synonym
Jill sal_limit private synonym
=======================================
C.两个sql语句中必须使用相同的绑定变量
=======================================
D.选择最有效率的表名顺序
from后面连接多表,最后面的表为基础表(最先被处理),选择条目少的放在from的最后面,oracle处理多表时会排序及合并,先扫描基础表排序,然后扫描下一个表并于第一个表进行合并.
t1 100000条记录
t2 1 条记录
显示sql运行时间
sql>set timing on
选择t2表做基础表(效率最高)
sql>select count(*) from t1,t2 (执行时间很短)
sql>select count(*) from t2,t1 (执行时间长)
=======================================
E.select 子句中避免使用 *
=======================================
F.减少访问数据库的次数
1.select * from emp where emp_no=123;
select * from emp where emp_no=321;
(执行2次,低效)
2.select * from emp a,emp b where a.emp_no=123 and b.emp_no=321;
(执行1次,高效)
=======================================
G.使用 decode函数
decode 函数
只有ORACLE公司的SQL提供了此函数
SELECT DECODE(SIGN(5 – 5), 1,
’Is Positive’, -1, ’Is Negative’, ‘Is Zero’)
FROM DUAL
select decode(SIGN(5 - 5),1,
'Is Positive',-1,
'Is Negative','Is Zero') FROM DUAL;
例1:
工资在8000元以下的将加20％；工资在8000元以上的加15％
select decode(sign(sal-8000),1,sal*1.15,-1,sal*1.2)gongzi,sal from emp
((sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1))
例2:
比如我要查询某班男生和女生的数量分别是多少?
通常我们这么写:
select count(*) from 表 where 性别 ＝ 男；
select count(*) from 表 where 性别 ＝ 女；
要想显示到一起还要union一下，太麻烦了
用decode呢，只需要一句话
select count(decode(性别，男，1，null)),count(decode(性别，女，1，null)) from 表
=======================================
H.用 truncate 替换delete
尽量多的使用commit
=======================================
I.having使用
HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。
例:
找出那些平均工资在$2000以上的部门
SQL> select deptno,avg(sal) avg_sal from emp
2 group by deptno having avg(sal) >2000;
=======================================
case ..when
case 语句带有选择效果知返回第一个条件满足要求的语句，即语句一语句二都的判断都为 true ，返回排在前面的。
SELECT ename,
(CASE deptno
WHEN 10 THEN 'ACCOUNTING'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'SALES'
WHEN 40 THEN 'OPERATIONS'
ELSE 'Unassigned'
END ) as Department
FROM emp;
SELECT ename, sal, deptno,
CASE
WHEN sal <= 500 then 0
WHEN sal > 500 and sal<1500 then 100
WHEN sal >= 1500 and sal < 2500 and deptno=10 then 200
WHEN sal > 1500 and sal < 2500 and deptno=20 then 500
WHEN sal >= 2500 then 300
ELSE 0
END "bonus"
FROM emp;
=======================================
删除重复记录
最高效的删除重复记录方法 ( 因为使用了ROWID)
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);
=========================================
用union/union all 替换 or(适用于索引列)
SQL> create table a as select * from dba_objects;
SQL> create table b as select * from dba_objects;
SQL> create index ia on a(object_id);
SQL> create index ib on b(object_id);
SQL> set autotrace traceonly
SQL> select owner from a where object_id=22 or object_id in
(select data_object_id from b where object_id=22);
149921 consistent gets
0 physical reads
优化为:
SQL> select owner from a where object_id=22
union all
select owner from a where object_id <>22
and object_id in (select data_object_id from b where object_id=22);
10 consistent gets
0 physical reads
逻辑读减少了很多
==========================================
执行顺序：
1.select 列列表 from 表列表名/视图列表名 where 条件.
先where 后select
2.select 列列表 from 表列表名/视图列表名 where 条件 group by (列列表) having 条件
先where 再group 再having 后select
3.select 列列表 from 表列表名/视图列表名 where 条件 group by (列列表) having 条件 order by 列列表
先where 再group 再having 再select 后order
4.select 列列表 from 表1 join 表2 on 表1.列1=表2.列1...join 表n on 表n.列1=表(n-1).列1 where 表1.条件 and 表2.条件...表n.
先join 再where 后select
有个问题 如果 条件字句顺序是固定的
==========================================
J.使用同义词
1. 同义词是表、索引、视图等模式对象的一个别名。oracle数据库只在数据字典中保存其定义描述，同义词不占用任何实际的物理空间。
2. 使用同义词优点：避免当管理员对数据库对象做出修改和变动之后，必须重新编译应用程序。使用同义词后，即使引用的对象发生变化，也只需要在数据库中对同义词进行修改，而不对应用程序做任何修改。
3. 同义词分类：公有同义词、私有同义词。公有同义词由public用户组拥有，数据库中的所有用户都可以使用公有同义词。
私有同义词只被创建它的用户所拥有，只能由该用户以及被授权的其他用户使用。
4. 创建公有同义词：
SQL> alter user scott account unlock;
SQL> password scott
SQL> create user sq1 identified by abc123 account unlock;
SQL> grant create session to sq1;
SQL> grant create table to sq1;
SQL> alter user sq1 quota unlimited on users;
创建公有同义词：sys下
SQL> create public synonym eemp for scott.emp;
SQL> grant public to sq1; (把public角色赋予sq1用户)
SQL> grant select on scott.emp to sq1;
SQL> conn sq1/abc123
SQL> select * from eemp;
5.创建私有同义词
sys下
SQL> create synonym aaa for scott.emp;
SQL> select * from aaa;
SQL> grant create synonym to scott;
SQL> conn scott/abc123;(scott登录)
SQL> create synonym bbb for emp;
SQL> select * from bbb;
6.查看同义词
SQL> desc dba_synonyms;
SQL> desc user_synonyms;
7.删除同义词
SQL> drop synonym aaa;
