oracle sql语句优化总结

来源：互联网发布：深入浅出数据分析 mobi 编辑：程序博客网时间：2024/06/03 21:33

1.直接路径读方式

--直接路径读方式drop table test;create table test  as select * from dba_objects where 1=2;set timing oninsert  /*+ append */ into test select * from t;commit;--注意这个直接路径方式插入试验输出的物理读（这是首次读哦）set autotrace traceonlyselect count(*) from test;

2.绑定变量使得速度加快

SQL>--未使用绑定变量SQL> begin  2      for i in 1 .. 100000  3      loop  4          execute immediate  5          'insert into t values ( '||i||')';  6      end loop;  7      commit;  8  end;  9  /  PL/SQL 过程已成功完成。已用时间:  00: 00: 43.50SQL>--使用绑定变量SQL> begin  2      for i in 1 .. 100000  3      loop  4          execute immediate  5          'insert into t values ( :x )' using i;  6      end loop;  7          commit;  8  end;  9  /  PL/SQL 过程已成功完成。已用时间:  00: 00: 04.77

3.批量提交使得速度加快

SQL> drop table t purge;表已删除。SQL> create table t(x int);表已创建。SQL> set timing onSQL> begin  2      for i in 1 .. 100000 loop  3         insert into t1 values (i);  4        commit;  5      end loop;  6  end;  7  /PL/SQL 过程已成功完成。已用时间:  00: 00: 11.21SQL> drop table t purge;表已删除。SQL> create table t(x int);表已创建。SQL> begin  2      for i in 1 .. 100000  loop  3         insert into t values (i);  4      end loop;  5    commit;  6  end;  7  /PL/SQL 过程已成功完成。已用时间:  00: 00: 04.26

4.关闭日志提高性能

SQL> --测试直接路径读方式SQL> drop table test;表已删除。SQL> create table test  as select * from dba_objects where 1=2;表已创建。SQL> set timing onSQL> insert  /*+ append */ into test select * from t;已创建4664384行。已用时间:  00: 00: 05.01SQL> --测试nolgging关闭日志+直接路径读方式SQL> drop table test;表已删除。SQL> create table test  as select * from dba_objects where 1=2;表已创建。SQL> alter table test nologging;表已更改。SQL> set timing onSQL> insert  /*+ append */ into test select * from t;已创建4664384行。已用时间:  00: 00: 04.39

5.避免对列进行运算，否则将用不到索引，除非使用函数索引。

drop table t purge;create table t as select * from dba_objects;create index idx_object_id on t(created);set autotrace traceonlyset linesize 1000--以下写法大量的出现在开发人员的代码中，是一个非常常见的通病,由于对列进行了运算，所以用不到索引，如下：select * from t where trunc(created)>=TO_DATE('2013-12-14', 'YYYY-MM-DD')and trunc(created)<=TO_DATE('2013-12-15', 'YYYY-MM-DD');执行计划--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |    12 |  2484 |   296   (2)| 00:00:04 ||*  1 |  TABLE ACCESS FULL| T    |    12 |  2484 |   296   (2)| 00:00:04 |--------------------------------------------------------------------------   1 - filter(TRUNC(INTERNAL_FUNCTION("CREATED"))>=TO_DATE(' 2013-12-14              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND              TRUNC(INTERNAL_FUNCTION("CREATED"))<=TO_DATE(' 2013-12-15 00:00:00',              'syyyy-mm-dd hh24:mi:ss'))统计信息----------------------------------------------------------          0  recursive calls          0  db block gets       1049  consistent gets          0  physical reads          0  redo size       1390  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed                    ---调整为如下等价语句后，就可以用到索引了。select * from t where created>=TO_DATE('2013-12-14', 'YYYY-MM-DD')and created<TO_DATE('2013-12-15', 'YYYY-MM-DD')+1;执行计划---------------------------------------------------------------------------------------------| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |               |     1 |   207 |     3   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |   207 |     3   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |     1 |       |     2   (0)| 00:00:01 |---------------------------------------------------------------------------------------------   2 - access("CREATED">=TO_DATE(' 2013-12-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')              AND "CREATED"<TO_DATE(' 2013-12-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))统计信息----------------------------------------------------------          0  recursive calls          0  db block gets          3  consistent gets          0  physical reads          0  redo size       1393  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

6.索引遇到like '%LJB' 或者是'%LJB%'的查询，是用不到索引的

create index idx_reverse_objname on t(reverse(object_name));set autotrace on select object_name,object_id from t where reverse(object_name) like reverse('%LJB'); OBJECT_NAME           OBJECT_ID---------------------------- --AAALJB                        8执行计划---------------------------------------------------------------------------------------------------| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                     |  3596 |   509K|   290   (0)| 00:00:04 ||   1 |  TABLE ACCESS BY INDEX ROWID| T                   |  3596 |   509K|   290   (0)| 00:00:04 ||*  2 |   INDEX RANGE SCAN          | IDX_REVERSE_OBJNAME |   647 |       |     6   (0)| 00:00:01 |---------------------------------------------------------------------------------------------------统计信息----------------------------------------------------------          0  recursive calls          0  db block gets          5  consistent gets          0  physical reads          0  redo size        496  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

0 0