一般 sql 优化有几种解决方案:
一、索引
二、物化视图
三、分区
一、索引
索引的说明:
索引是与表相关的一个可选结构,在逻辑上和物理上都独立于表的数据,索引能优化查询,不能优化 DML 操作,Oracle 自动维护索引,频繁的 DML 操作反而会引起大量的索引维护。
如果 SQL 语句仅访问被索引的列,那么数据库只需从索引中读取数据,而不用读取表,
如果该语句同时还要访问除索引列之外的列,那么,数据库会使用 rowid 来查找表中的行,
通常,为检索表数据,数据库以交替方式先读取索引块,然后读取相应的表块。
索引的目的是: 主要是减少 IO, 这是本质, 这样才能体现索引的效率。
1 大表,返回的行数 < 5%
2 经常使用 where 子句查询的列
3 离散度高的列
4 更新键值代价低
5 逻辑 AND、OR 效率高
6 查看索引在建在那表、列:
- select * from user_indexes;
- select * from user_ind_columns;
建立索引的方式 (这里只列举了常用的索引):
1)唯一索引:唯一索引值键值不重复。
- create unique index emp_idx on emp1(empno);--drop index empno_idx;
2) 一般索引:索引值键值可以重复
- create index emp_idx on emp1(empno)
3) 组合索引:绑定 2 个或更多列的索引
- create index job_dep_idx on emp1(job,deptno);--drop index job_dep_idx ;
索引碎片问题:
查看执行计划:
- set autotrace traceonly explain;
索引碎片问题:由于对基表做 DML 操作,导致索引表块的自动更改操作,尤其是基表的 delete 操作会引起 index 表的 index_entries 的逻辑删除,注意只有当一个索引块中的全部 index_entry 都被删除了,才会把这个索引块删除,索引对基表的 delete、insert 操作都会产生索引碎片问题。
在 Oracle 文档里并没有清晰的给出索引碎片的量化标准, Oracle 建议通过 Segment Advisor(段顾问)解决表和索引的碎片问题,如果你想自行解决,可以通过查看 index_stats 视图,当以下三种情形之一发生时,说明积累的碎片应该整理了(仅供参考)。
- 1.HEIGHT >=4 2 PCT_USED< 50% 3 DEL_LF_ROWS/LF_ROWS>0.2
这里举个例子,我创建一个表里面循环插入一百万条记录。
建立表、索引:
- create table t(id int);
- create index ind_1 on t(id);
执行插入记录:
- begin
- for i in 1..1000000 loopinsert into t values(i);
- if mod(i, 100) = 0 thencommit;
- end
- if;
- end loop;
- end;
- create table t(id int);
- create index ind_1 on t(id);
执行下面语句分析索引:
- analyze index ind_1 validate structure;
- select name,
- HEIGHT,
- PCT_USED,
- DEL_LF_ROWS / LF_ROWS from index_stats;
上图所示表示我这张表里面的索引并没有满足
1.HEIGHT >=4 2.PCT_USED<50% 3.DEL_LF_ROWS/LF_ROWS>0.2 三者的任意一个条件所以我不需要整理索引碎片
我再执行下面语句,删除七十万条数据
- delete t where rownum<700000;
上图所示其中一个条件已经满足需要整理的条件了
我们可以执行下面语句来进行碎片的整理
- alter index ind_1 rebuild [online] [tablespace name];
整理完成之后,该表并未满足索引碎片需要整理的条件。
二、物化视图
物化视图和普通视图的区别:
1、物化视图自动刷新或者手动刷新的,普通视图不用刷新。
2、物化视图也可以直接 update,但是不影响基础表,对普通视图的 update 反映到基础表上。
3、物化视图主要用于远程数据访问,物化视图中的数据需要占用磁盘空间,普通视图中不保存数据。
具体语法:
- create materialized view[view_name] refresh[fast | complete | force][on[commit | demand] | start with(start_time) next(next_time)] as {创建物化视图用的查询语句
- }
1.refresh [fast|complete|force] 视图刷新的方式: fast: 增量刷新. 假设前一次刷新的时间为 t1, 那么使用 fast 模式刷新物化视图时, 只向视图中添加 t1 到当前时间段内, 主表变化过的数据. 为了记录这种变化,建立增量刷新物化视图还需要一个物化视图日志表。create materialized view log on (主表名)。complete: 全部刷新。相当于重新执行一次创建视图的查询语句。force: 这是默认的数据刷新方式。当可以使用 fast 模式时,数据刷新将采用 fast 方式;否则使用 complete 方式。2.MV 数据刷新的时间:on demand: 在用户需要刷新的时候刷新,这里就要求用户自己动手去刷新数据了(也可以使用 job 定时刷新)on commit: 当主表中有数据提交的时候,立即刷新 MV 中的数据;举个例子:如果创建物化视图出现权限不足的情况需要先登录到 sysdba 然后进行授权这里我
来源: