oracle sql语句优化总结

1.直接路径读方式

--直接路径读方式
drop table test;
create table test  as select * from dba_objects where 1=2;
set timing on
insert  /*+ append */ into test select * from t;
commit;
--注意这个直接路径方式插入试验输出的物理读（这是首次读哦）
set autotrace traceonly
select 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.50



SQL>--使用绑定变量

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 on
SQL> 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.21




SQL> 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 on
SQL> insert  /*+ append */ into test select * from t;
已创建4664384行。
已用时间:  00: 00: 05.01




SQL> --测试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 on
SQL> 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 traceonly
set 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