常规sql语句优化


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;  --索引重建
标签: 暂无标签
ting-gpng

写了 5 篇文章,拥有财富 65,被 2 人关注

转播转播 分享分享 分享淘帖
回复

使用道具

P4 | 发表于 2017-9-13 10:39:46
谢谢分享,顶一个
回复

使用道具

P4 | 发表于 2016-1-5 11:29:36
thank you!
回复

使用道具

P4 | 发表于 2015-11-12 15:32:05
受益!
回复

使用道具

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈