Oracle访问的一些小细节

 

1.        SELECT与FROM语句之间需要定义返回的字段名，尽量不要使用 *。

2.        字段名应按照表的字段物理顺序编写，字段提取要按照“需多少、提多少”的原则（因为大批量数据的抽取会影响SQL缓存的效率）。

3.        要避免使用COUNT(*)，因为COUNT(*)会对全字段做聚集，使用COUNT (0)或COUNT(1)这样的查询语句。

4.        尽量避免子查询，查询嵌套层次越多,效率越低。

5.        在必须用子查询时,要在子查询中过滤掉尽可能多的行，能用连接代替的就用连接。

6.        通配符会导致顺序扫描数据表，这种匹配非常耗费时间，尽量不要使用通配符。

7.        避免使用DISTINCT关键字，在使用中DISTINCT会产生一张工作表，并进行排序来删除重复记录，这会大大增加查询和I/O的操作次数。

 

1、  join 与on 必须严格匹配，不允许出现没有on的join。LEFT JOIN 和全关联，特别是多个表进行关联，每个关联表查询，进行扫描的时候都是一个笛卡尔乘积的数量级，扫描数量很大，因此关联操作，需给where或者on的条件进行索引。

2、  join…on 后面慎用 or ，如果用到，请把or的范围用( )括起来。

3、  多表连接时，必须对每个表命名别名，对每个字段的使用都要带上别名。

4、  表之间的连接必须写在其他where条件之前，可以过滤掉大量记录的条件必须写在where子句的末尾

建议：

select * from emp e where 25<(select count(*) from emp where mgr=e.empno) and sal>5000 and job=’manager’;

 

不建议；

select * from emp e where sal>5000 and job = ‘manager’ and 25< (select count (*) from emp where mgr=e.empno);

 

5、  建议不使用右连接

6、  参与左连接的列不能为常量。

不建议：

select * from t1 left outer join t2 on t1.f1='A'。

 

1、  在使用UNION或UNION  ALL 的前后的两个SQL需要加 ( )

2、  union扫描的是全索引，可以适当用集合差MINUS 和集合交INTERSECT 来代替。

 

1、 建议用not exists 比not in 要快

2、  如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in

如：表A（小表），表B（大表）

建议1：

select * from A where exists(select cc from B where cc=A.cc)

建议2：

select * from B where cc in (select cc from A)

 

3、  计算需要在等号右边

如：

1、a>4/2； 2、zip = TO_NUMBER('94002')

 

4、  建议不要使用!=，如：

Goods_no != 2，要改为:  where Goods_no>2 or Goods_no<2

 

5、  建议不要使用is null

WHERE DEPT_CODE IS NOT NULL 要改为:  WHERE DEPT_CODE >=0;

 

6、  删除全表数据时使用truncate替代 delete,同时注意truncate只能在删除全表时适用，因为truncate是ddl而不是dml语句。

7、  在保证事务完整性和一致性前提下，尽量多使用commit，有可能对delete、insert、 update操作尽量多使用commit,这样系统性能会因为commit所释放的资源而大大提高。

8、  对于查询大记录的操作使用分页技术

9、  条件尽可能少用或不用OR，因为OR会引起全表扫描，影响查询效率，可以用UNION代替。

10、             避免使用负逻辑如!=、<>、not in等，会导致Oracle全表扫描，建议用别的操作来代替。

11、             应尽量避免在 where 子句中对字段进行 null 值判断，这样会导致全表扫描。

12、             提供一些直接条件，使优化器较好优化效果。

select employee.emp_name,department.dep_name from department,employee where (employee.dep_id = department.dep_id) and (department.dep_code='01') and (employee.dep_code='01');

 

13、             ORDER BY和GROPU BY 尽量使用索引来排

14、             为提高group by语句的效率，将不需要的记录在group by之前过滤掉

建议：

select job, avg(sal) from emp having  job=’president’ or job=’manager’ group by job;

 

不建议：

select job, avg(sal) from emp group by job having job = ‘president’ or job=’manager’;

 

1、  索引列中不要有空值，因为空值导致优化器无法优化

2、  对于开销比较大的SQL查询，要建立数据库索引

3、  可以通过indexhints来强制SQL使用索引,Index Hints的格式如下:/*+ INDEX ( table [index [index]...] ) */

4、  索引列上不要进行计算

不建议：

where trunc(order_date)=trunc(sysdate)

 

5、  索引列上>= 代替 >

6、  索引数量应不超过列总数的40%

7、  索引的列尽量编写在where条件语句的最后，以便执行计划命中索引。

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

9、  索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

 

1、  任何被Mybatis 使用的对象属性必须定义完整的Setter/Getter方法

2、  避免使用自定义类型的对象属性

1、  XML使用<!-- -->注释，前后至少留出一个半角空格

2、  避免注释出现在SQL语句中

不建议

<select id="getMemberInfo" resultClass="java.util.HashMap">   select Uid, Memberid, FullName   <!-- 这里不要出现注释 -->    from Member with (nolock)      <!-- 这里不要出现注释 -->    where Memberid = #{memberid:CHAR}  <!-- 这里不要出现注释 -->   </select>

 

3、  防止SQL注入禁止使用${}写法，推荐使用#{}写法

4、  避免WHERE/ON语句中列类型隐式转换：

变量类型优先级应不高于数据库中的列类型

指定字符串变量的JDBC类型

#{userName,jdbcType=VARCHAR}

5、  避免对WHERE语句中的字段进行函数运算，尤其对于日期类型的字段使用函数操作，建议对变量使用函数操作，而不是对字段使用

6、  避免WHERE/ON语句中对字符串类型的字段使用CAST()、RTRIM()、LEN()、LEFT()、CHARINDEX()等函数运算

7、  使用AS关键字指定别名，而不是空格；避免使用无意义的别名

8、  子查询中指定表的别名

 

1、  多条数据查询时必须要限制查询条数，每次最大200条

2、  统计数据量sql要直接用统计函数，避免调用查询List

3、  查询一条数据用单条查询函数，避免查询List取一条

4、  查询数据时，除了TenantId，必须要再带一个参数，避免全表查询

5、  避免在for循环中查询数据库