Oracle中编写Sql语句注意事项

落日映苍穹つ 2022-07-14 03:26 265阅读 0赞

**基本的Sql编写注意事项（SQL优化法则）**

1.尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。
    原因：这篇文章讲解的生动形象。http://www.cnblogs.com/iceword/archive/2011/02/15/1955337.html
    
    2.不用NOT IN操作符，可以用NOT EXISTS或者外连接+替代。
    原因：因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。
    因此建议开发人员在建表时，把需要索引的列设成NOT NULL。	  如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，详情请查阅oracle中的位图索引资料）。
    
    3.不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，可以用“<” or “>”代替。
    
    原因：下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。
    select cust_Id,cust_name
    
    from customers
    
    where cust_rating <> 'aa';
    
    把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。
    select cust_Id,cust_name
    
    from customers
    
    where cust_rating < 'aa' or cust_rating > 'aa';
    特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描
    
    4.应该使用索引的第一个列 
    如果索引是建立在多个列上 , 只有在它的第一个列 (leading column) 被 where 子句引用时 , 优化器才会选择使用该索引 .
    这也是一条简单而重要的规则，当仅引用索引的第二个列时 , 优化器使用了全表扫描而忽略了索引
    
    5.Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。
    办法：可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。
    ------------------------------------------------------------------------------------
    查询表中存在的索引
    select * from user_indexes where table_name = 'table_name';
    
    6.当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。
    
    7.对于有连接的列“||”，最后一个连接列索引会无效。尽量避免连接，
    可以分开连接或者使用不作用在列上的函数替代。 
    
    8.如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。
    select empno,ename,deptno
    
    from emp
    
    where trunc(hiredate)='01-MAY-81';
    
    把上面的语句改成下面的语句，这样就可以通过索引进行查找。
    
    select empno,ename,deptno
    
    from emp
    
    where hiredate<(to_date('01-MAY-81')+0.9999); 
    
    9.Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。 
    
    10.对数据类型不同的列进行比较时，会使索引失效。
    
    比较不匹配的数据类型也是比较难于发现的性能问题之一。
    
    注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。
    
    select bank_name,address,city,state,zip
    
    from banks
    
    where account_number = 990354;
    
    Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：
    
    select bank_name,address,city,state,zip
    
    from banks
    
    where account_number ='990354';
    
    特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。
    
    11.用“>=”替代“>”。 
    
    12.UNION操作符会对结果进行筛选，消除重复，数据量大的情况下可能会引起磁盘排序。
    如果不需要删除重复记录，应该使用UNION ALL。 
    
    13.Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，
    可以过滤掉最大数量记录的条件必须写在Where子句的末尾。 
    
    14.Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，
    将记录最少的表放在最后。
    
    15.Order By语句中的非索引列会降低性能，可以通过添加索引的方式处理。
    严格控制在Order By语句中使用表达式。 
    
    16.不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，
    防止相同的Sql语句被多次分析。 
    
    17.当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。
    
    18.SELECT 子句中避免使用 ‘ * ‘ ：
    ORACLE 在解析的过程中 , 会将 '*' 依次转换成所有的列名 , 这个工作是通过查询数据字典完成的 , 这意味着将耗费更多的时间
    
    19.sql 语句用大写的 ；因为 oracle 总是先解析 sql 语句，把小写的字母转换成大写的再执行	
    
    20.在 java 代码中尽量少用连接符“＋”连接字符串！
    
    21.优化 GROUP BY:
    提高 GROUP BY 语句的效率 , 可以通过将不需要的记录在 GROUP BY 之前过滤掉 . 下面两个查询返回相同结果但第二个明显就快了许多 .
    低效 : 
    SELECT JOB , AVG(SAL) 
    FROM EMP 
    GROUP JOB 
    HAVING JOB = ‘PRESIDENT' 
    OR JOB = ‘MANAGER' 
    高效 : 
    SELECT JOB , AVG(SAL) 
    FROM EMP 
    WHERE JOB = ‘PRESIDENT' 
    OR JOB = ‘MANAGER' 
    GROUP JOB
    22. 用 TRUNCATE 替代 DELETE ：
    当删除表中的记录时 , 在通常情况下 , 回滚段 (rollback segments ) 用来存放可以被恢复的信息 . 如果你没有 COMMIT 事务 ,
    ORACLE 会将数据恢复到删除之前的状态 ( 准确地说是 恢复到执行删除命令之前的状况 )而当运用 TRUNCATE 时 , 
    回滚段不再存放任何可被恢复的信息 . 
    当命令运行后 , 数据不能被恢复 . 因此很少的资源被调用 , 执行时间也会很短 .( 译者按 : TRUNCATE 只在删除全表适用 ,TRUNCATE 是 DDL 不是 DML)	
    23.尽量多使用 COMMIT ：
    只要有可能 , 在程序中尽量多使用 COMMIT, 这样程序的性能得到提高 , 需求也会因为 COMMIT 所释放的资源而减少 : 
    COMMIT 所释放的资源 : 
    a. 回滚段上用于恢复数据的信息 . 
    b. 被程序语句获得的锁 
    c. redo log buffer 中的空间 
    d. ORACLE 为管理上述 3 种资源中的内部花费	
    
    ......待续