Oracle SQL语句优化

1，建表时：

 1）创建主键

 2）创建索引

 3）如果表数据量很大考虑创建分区

 4）可以使用number类型的就不适用varchar2,这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，

       而对于数字型而言只需要比较一次就够了。

2，创建索引注意事项：

 1）首先应考虑在 where 及 order by 涉及的列上建立索引。

 2）避免在索引列上使用NOT

       NOT会产生在和在索引列上使用函数相同的影响。当ORACLE遇到NOT,就会停止使用索引转而执行全表扫描。

 3）避免在索引列上使用计算

 2，数据：

 1）尽量不要存储null，可以给默认值。避免where条件出现 is null 或 is not null。

3，写SQL语句时：

 1）尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，

      如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，

      然后这样查询：select id from t where num=0。

 2）尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

 3）尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，

      如：select id from t where num=10 or num=20

      可以这样查询：select id from t where num=10union allselect id from t where num=20。

 4）in 和 not in 也要慎用，否则会导致全表扫描。可以用EXISTS替代。或者根据实际数据情况用其他函数代替。

      如：select id from t where num in(1,2,3,4,5,6)

      对于连续的数值，能用 between 就不要用 in 了：
      select id from t where num between 1 and 6

 5）where 条件后用like '%%' 也会导致全表扫描。

 6）在 where 子句中使用参数，也会导致全表扫描。

       因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然        而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。

       如下面语句将进行全表扫描：

       select id from t where num=v_num;

       可以改为强制查询使用索引：

       select id from t with(index(索引名)) where num=v_num;  

 7) 避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
     select id from t where num/2=100
     应改为:
     select id from t where num=100*2 ;

 8) 尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

     select id from t where substring(name,1,3)='abc' ；

     应改为:

     select id from t where name like 'abc%'；

 9）不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

10）在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用        该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

11）尽量不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

12）在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速           度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

13）当对多个表进行连接查询的时候，Oracle分析器会按照从右到左的顺序处理from字句中的表名

       select a.emp_id,b.emp_deptid

         from emp a.dept b;

 

在执行时，Oracle会先查询dept表，根据dept表查询到行作为数据源串行连接emp表继续执行，因此dept表又称为基础表或者驱动 表。由于连接的顺序对查询的效率有非常大的影响，因此在处理多表连接时，必须选择记录条数较少的表作为基础表。Oracle会使用排序或者合并的方式进行连接，比如先扫描dept表，然后对dept表进行排序，再扫描emp表，最后将所有检索出来的记录与第一个表的记录进行合并。

如果有三个以上的表连接查询，就需要选择交叉表作为基础表。交叉表是指那个被其他表所引用的表。由于emo_log是emp和dept表中的交叉表，既包含了dept表的内容又包含了emp的内容，因此上述查询可以将emp_log作为驱动表
select a.emp_id,b.emp_deptid,c.emp_name

  from  emp a.dept b,emp_log c;

14) 默认情况下，Oracle采用自下而上的顺序解析where字句，因此在处理多表查询的时候，表之间的连接必须写在其他的where       条件之前，但是过滤数据记录的条件则必须写在where子句的尾部，以便在过滤了数据之后再进行连接处理，这样可以提升         sql语句的性能。

 

      select a.empno ,a.ename,c.deptno,b.sal

        from emp b,dept c,emp_log a

     where a.deptno=b.deptno

         and c.deptno in (20,30);

15) 使用decode函数，灵活使用该函数可以减少很多对表的不必要的访问。

16）使用 >= 代替 >

       因为DBMS将直接跳到第一个ID等于4的记录而后者将首先定位到ID=3的记录并且向前扫描到第一个ID大于3的记录。

17）sql语句用大写的，因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行。

18）尽量多使用COMMIT

       只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
       COMMIT所释放的资源: 
       a. 回滚段上用于恢复数据的信息. 
       b. 被程序语句获得的锁 
       c. redo log buffer 中的空间 
       d. ORACLE为管理上述3种资源中的内部花费

19）创建临时表

        

 1）会话级临时表

  会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。

  格式：
  Create Global Temporary Table Table_Name    (   Col1 Type1,   Col2 Type2   ...  )   On Commit Preserve Rows;  

 2）事务级临时表  事务级临时表是指临时表中的数据只在事务生命周期中存在。  

  Create Global Temporary Table Table_Name    (    Col1 Type1,    Col2 Type2    ...  )   On Commit Delete Rows; 
  当一个事务结束（commit or rollback），Oracle自动清除临时表中数据。 

20）UNDO 表空间

       UNDO 表空间也很重要，如果你的逻辑里面有大量的DML操作（delete,insert,update）这时会使用UNDO 表空间，

       如果表空间太小会直接报错（表空间不能扩展），即便不报错程序能运行但是也会很慢，增大UNDO表空间后效率会有很大         的提升。