|
|
P4
|
发表于 2015-9-22 20:45:49
1.1：建议不使用”*“来代替所有列名
说明：
这样的写法对oracle系统来说会存在解析的动态问题，oracle会查询数据字典开将*替换成相应的字段，这自然就消耗了系统
时间。
1.2：用truncate替代delete
说明：
1）：在使用delete语句时，oracle会使用撤销表空间（undo tablespace）来存放要恢复的信息，如在这个时间段没有发出
commit，而是使用了rollback语句，oracle系统会将这些数据进行恢复。
2）：使用truncate语句时，系统不会使用撤销表空间，所以数据不能回滚，那么速度也较快，前提条件确定是该数据是要删
除的，一般使用在清空表数据的情况。
语法：
truncate table [cluster]schema.[table_name][cluster_name][drop | reuse storage]
table_name：要清空的表名
cluster_name：要情况的族名
drop | reuse storage：表示保留被删除的空间以供该表新的数据使用，默认是reuse storage，即收回被删除的空间系统。
for example：
创建一个存储过程，实现truncate命令动态删除数据表
create or replace procedure trun_table(table_deleted in varchar2)
as
cur_name integer;
begin
cur_name :=dbms_sql.open_cursor;
dbms_sql.parse(cur_name,'truncate table '|| table_deleted ||' drop storage',dbms_sql.native);
dbms_sql.close_cursor(cur_name);
exception
when others then dbms_sql.close_cursor(cur_name);
raise;
end trun_table;
1.3：在确保完整性的情况下多用commit语句
说明：经常将几个相互联系的dml语句写在一个begin .. end 中，建议在每一个end的前面使用commit语句，是否占用的资源。
commit所释放的资源如下：
--回滚段上用于恢复数据的信息，撤销表空间也只做短暂的保留。
--被程序语句所获得锁。
--redo log buffer中的空间。
--oracle为管理上述资源的内部花费
1.4：尽量减少表的查询次数
说明：
在含有子查询的sql中，要特别注意减少对表的查询，如下的两段sql代码：
第一种： 低效sql
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');
第二种： 优化后的sql
select empno,ename,job
from emp
where deptno in (select deptno from dept where loc='BEIJING' or loc='NEW YORK');
1.5：使用[not] exists 代替 [not] in
说明：[not] in 子句将执行一个内部的排序和合并，无论在那种情况下，[not] in 都是最低效的，因为它对子查询的表执行了一个全
表遍历。可以使用外链接（outer joins）、not exists 或是 exists代替
第一种： 低效sql
select empno,ename,job
from emp
where deptno not in (select deptno from dept where loc='BEIJING');
第二种： 优化后的sql
select empno,ename,job
from emp
where not exists (select deptno from dept where loc ='BEIJING');
注意：那么在什么情况下使用[not] in呢？如果[not] in 后面的括号内的是列表（可枚举的几个）或子查询所有满足结果集较少。
explain plan for
select * from table(dbms_xplan.display);
表连接优化
2.1：驱动表的选择
说明：驱动表是指被最先访问的表（通常以全表访问的方式被访问），在oracle11g优化器会检查sql语句中的每个表的物理大小、
索引状态，然后选择花费最低的执行路径。
for example：
select s.name,d.dept_name
from department d,students s
where d.dept_no=s.dept_no;
说明：students表dept_no列创建了索引，而department表的dept_no上没有索引，由于department最先被访问（紧跟
from其后），这样的department表将被作为查询中的驱动表。由此可见，只有当两个表都建立有索引，优化器才能
按照紧跟其from后的驱动表的规则来对待。
2.2：where子句的连接顺序
说明：oracle采用自上而下的顺序解析where子句，那些可以过滤最大数据记录的条件必须写在where子句的末尾，也就在在表进行
连接操作以后，过滤掉的数据越多越好。
2.3：合理使用索引
2.3.1：何时使用索引
说明：在使用索引的情况下，用于选择表中部分行，提高查询效率。
创建索引原则：
--以查询关键字为基础，表中的行随机排序。
--以查询的列数相对比较少的表。
--表中大多数查询都包含相对简单的where从句
--对于经常以查询关键子为基础的表，并且该表中的行遵从均匀分布。
--换成命中率低，并且不需要操作系统权限。
2.3.2：索引列和表达式的选择
选择索引列的原则：
--sql语句中频繁由于进行表连接的关键字。
--可选择性高（重复性少的）关键字。。
--对于取值较少的关键字或表达式，不要采取B树索引，可以考虑建立位图索引。
--不要将那些频繁修改的列作为索引。
--不要使用包含操作符或函数的where从句中的关键字作为索引列，如 果要使用的话，可以建立函数索引。
--如果大量并发的insert、delete、update语句访问了父表或者是子表，则考虑使用完整性约束的外部键为索引。
--在选择索引列时，要考虑该索引引起的insert、update、delete操作是否值得。
2.3.3：选择复合索引主列
几个字段作为联合索引，主列是最先被选择的列。
--也要注意，联合主键的建立情况。
2.3.4：避免全表扫描大表
说明：在应用程序中，除了必要的情况，比如月报数据的统计，打印所有的清单等可以选择
全表扫描，其他情况要避免全表扫 描。
全表扫描的情况：
--所查询的表没有索引。
--需要返回全部的行。
--带like并使用”%“这样的语句就是全表扫描。
--对索引主列有限制条件，比如使用了函数，则oracle使用全表扫描，不如：
select * from emp where substr(ename,1,1)='M';
--带有 is null, is not null或者是！=等子句也会导致全表扫描。
2.3.5：监视索引是否被使用
语法：
alter schema.index_name monitoring usage; --设置监视索引
select * from v$object_usage; --检查所用使用情况
drop index grade_index； --删除索引
ALTER INDEX index_name REBUILD; --索引重建 |
|
京ICP备12024223-8号
不良信息举报电话：13718043309
