应用优化

最新推荐文章于 2024-07-26 14:23:29 发布

clx00001

最新推荐文章于 2024-07-26 14:23:29 发布

阅读量95

点赞数

创建索引必须考虑的问题
1 使用索引扫描和全表扫描分别需要读取多少个块？
2 对给定表最常用的查询条件的列组合是什么？
这可以通过查询v$sqlarea或v$sqltext，查看执行次数较多的SQL语句。
3 创建索引时给定列的选择性
一般按照选择性降序排列索引列
4 在SQL语句的WHERE短语中所有列都必须包括在索引中吗？
不用。包括空值的列以及选择性低的列应该排除在索引列之外。
5 表是基于事务的还是基于查询的？
对经常更新的表，必须将多余的索引删除。
6 表被更新的方式，是批量更新还是在线事务型？
决定是否要删除索引或使之无效

复合索引与单列索引的有效性
一般而言，如果一个索引有多列，建立一个多列的复合索引要比建多个单列索引有效。

何时重建索引？
alter index index_name validate structure;
然后查询index_stats的height字段，如果变化较大可能需要重建索引。

重建一个大索引考虑的问题
并行创建和nologging选项

如：alter index index_name rebuild
    parallel (degree 4)
    nologging
    tablespace xxx;

在书写SQL时应该考虑到的与索引有关的问题
何时使用索引，何时不使用索引，见以下示例：
表line_items有1000000条记录
索引列为shipped_date
数据在shipped_date列上的分布十分分散
索引列的选择性高
注意：这里最关键的是列shipped_date在表中的数据非常分散，查询时要访问到大部分的数据块，因此这里要用全表扫描
select /*+ FULL(LINE_ITEMS) PARALLEL(LINE_ITEMS,2) */ *
from line_items
where shipped_date between sysdate
and (sysdate-30);

表ORDERSe 100000行
列ord_id可以包括字母和数字并且以大写保存，但应用程序允许小写输入
建有复合索引ord_id,ord_status,ord_date
索引的选择性很好
正确的写法是：
select *
from orders
where ord_id=upper(':b1') /*此处不应该写成upper(ord_id)=':b1'*/
and ord_status = 'not filled'
and ord_date = sysdate;

注意数据类型的匹配，这可能会导致索引的正常使用；如列类型为varchar2，则赋值时一定要加引号

如果要使用索引，就不要使用null操作符

注意绑定变量的使用

避免在循环中重复使用单个的insert,update,delete操作，尽可能批量操作
如下例：
before:
declare
   ord_struct orders%rowtype;
   cursor c_ord is
     select *
       from orders
     where ord_status='not filled'
       and ord_date= sysdate;
begin
open c_ord;
loop
    fetch c_ord into ord_struct;
    exit when c_ord%notfound;

insert into temp_ord values (ord_struct.ord_id,ord_struct.ord_date,(ord_struct.ord_price*1.1),ord_struct.ord_status);

commit;
end loop;
close c_ord;
end;
/

after:
begin
insert /*+ APPEND */ into temp_ord
select ord_id,ord_date,(ord_price*1.1),ord_status from orders
where ord_status='Not filled'
and ord_date=sysdate;
commit;
end;
/

不要在子查询中关联父表，这是一个CPU密集型的操作，如：
before:
select outer.*
from emp outer
where outer.salary >
   (select avg(salary)
      from emp inner
     where inner.dept_id=outer.dept_id);

after:
select e1.*
from emp e1, (select e2.dept_id dept_id, avg(e2.salary) avg_sal from emp e2
group by dept_id) dept_avg_sal
where e1.dept_id=dept_avg_sal.dept_id
and e1.dalary>dept_avg_sal.avg_sal;

SQL优化时注
1 当使用索引效率不高时，全表扫描通常有效；
2 当SQL包含子查询时，先优化子查询。如果可以用表连接来代替子查询，先尝试用表连接来实现。
3 使用not exists来代替not in.
4 使用like ‘A%’操作来代替substr函数
5 当合适的时候使用nvl函数来避免偶然的类型转换。另外，NVL函数的效率比||操作要高点
6 对使用OR的复杂查询，考虑使用union all来进行拆分；
7 使用最有效的索引
8 create indexes on foreign key columns if the queries always retrieve master-detail relationship-based rows
9 按选择性降序排列创建复合索引
10 使用非唯一索引来支持唯一索引。如，如果ORDERS表需要在ord_id上创建一个主键约束，如果有另一个复合索引并以ord_id为先导列，则不需要再建立一个独立的唯一索引。
11 对低基数列并对这些列进行or,and 或not 操作时，在列上建立位图索引。如果表上有频繁的并发DML操作，则不应该考虑使用位图索引。
11 对相对静态表，并且查询其中一定范围内的数据，考虑用单表hash或者索引cluster。
12 尽量避免在复杂SQL中使用视图
13 尽量避免远程访问。
14 批量集合操作时选择数组(array)
15 考虑动量sql的使用
16 对大表考虑使用表和索引分区
17 创建索引时，可以考虑在session级修改sort_area_size为一个较大值以尽量减少磁盘排序，加快创建速度。