oracle常识整理

柔情只为你懂 2022-03-10 14:59 232阅读 0赞

Oracle SQL编写注意事项  
1.SQL语句用大写的;因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。

2.数据表最好起别名;因为便于sql优化器快速分析。

3.尽量不要使用 insert into table value(?,?,?,?,?)格式，要指出具体要赋值的字段。INSERT.....SELECT的效率会有提高。

4.select与from语句之间只定义返回的字段名，除非返回所有的字段，尽量不要使用 \* 。

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

6.COUNT(\*)也是要避免的，因为Count(\*)会对全字段做聚集。但一般的观点相反, count(\*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)。

7.条件中使用or 会引起全表扫描，比较影响查询效率，尽可能少用或不用，实在不行可以用UNION代替。

8.尽量避免子查询。假如子查询不可避免,那么要在子查询中过滤掉尽可能多的行。能用直连接代替的就替换掉。

9.在SQL语句中，LIKE关键字支持通配符匹配，但这种匹配非凡耗费时间。例如：cust\_nm like '%custnm%'。即使在cust\_nm字段上建立了索引，在这种情况下也还是采用顺序扫描的方式，查询表中有1000条记录，就需要比较1000次。 执行查询时最好利用索引来查询，会大大提高速度。可以改成方式:instr(‘cust\_nm’, ‘AAA’,1)>0.

10.Distinct ：使用distinct是为了保证在结果集中不出现重复值，但是distinct会产生一张工作表，并进行排序来删除重复记录，这会大大增加查询和I/O的操作次数。因此应当避免使用distinct关键字。

11.负逻辑 负逻辑如!=、<>、not in等，都会导致oralce用表扫描来完成查询。当表较大时，会严重影响系统性能，可以用别的操作来代替。 尽量不要使用负逻辑如!=、<>、not in等。

12.使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，优化器将无法优化。

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

14.SQL语句包含多表连接时，必须对每个表命名别名，对每个字段的使用都要带上别名。

15.Where 子句中的连接顺序：oracle采用自下而上的顺序解析where子句，根据这个原理，表之间的连接必须写在其他where条件之前，那些可以过滤掉大量记录的条件必须写在where子句的末尾。如：  
低效：select \* from emp e where sal>5000 and job = ‘manager’ and 25< (select count (\*) from emp where mgr=e.empno);  
高效：select \* from emp e where 25<(select count(\*) from emp where mgr=e.empno) and sal>5000 and job=’manager’;  
16.尽量不使用右连接。参与左连接的列不能为常量。例如，不允许如下语句： select \* from t1 left outer join t2 on t1.f1='A'。  
左连接的写法必须带“outer”关键字。例如： select f1 from t1 left outer join t2 on t1.f1 = t2.f1；而不是： select f1 from t1 left join t2 on t1.f1 = t2.f1。

17.在使用UNION或UNION ALL 的前后的两个SQL需要加 ( )。  
union扫描的是全索引，可以适当用集合差：MINUS 和 集合交：INTERSECT 来代替。  
UNION ALL不会去除重复记录，执行效率要高于UNION。

18.优化group by提高group by语句的效率，可以将不需要的记录在group by之前过滤掉。如：  
低效：select job, avg(sal) from emp group by job having job = ‘president’ or job=’manager’;  
高效： select job, avg(sal) from emp having job=’president’ or job=’manager’ group by job;

19.将计算从等号左边移到右边,例如：把a\*2>4 改为a>4/2；把TO\_CHAR(zip) = ‘ 94002’ 改为zip = TO\_NUMBER('94002');

20.不要使用Not，如Goods\_no != 2，要改为: where Goods\_no>2 or Goods\_no<2 当Oracle“碰到”NOT，他就会停止使用索引转而执行全表扫描。

21.不要使用is null , 如WHERE DEPT\_CODE IS NOT NULL 要改为: WHERE DEPT\_CODE >=0;

22.尽量多使用commit只要有可能就在程序中对每个delete insert update操作尽量多使用commit,这样系统性能会因为commit所释放的资源而大大提高。  
COMMIT所释放的资源:  
a.回滚段上用于恢复数据的信息.  
b.被程序语句获得的锁  
c.redo log buffer 中的空间  
d.ORACLE为管理上述3种资源中的内部花费  
23.尽量把循环的操作封装到PL/SQL写的存储过程里，因为存储过程都在服务端执行，所以没有数据往返的消耗。有机会，将一些查询封装到函数里，而在普通SQL里使用这些函数，同样是很有效的优化。

24.开发人员编写sql语句时，尽量使用绑定变量，增加sql在sharepool中重用命中的几率。

25.大数据量的操作：大表中删除大记录：分段循环删除，批量绑定删除 ；大表中更新大记录：分段更新。

26.对于开销比较大的SQL查询，要建立数据库索引。数据库索引可以大大加快数据库的查询速度，索引是把表中的逻辑值映射到RowID，因此索引能进行快速定位数据的物理地址，快速返回想要查询的结果集。  
索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时， 索引本身也会被修改。这意味着每条记录的INSERT，DELETE ，UPDATE将为此多付出4、5次的磁盘I/O 。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。

27.索引不被使用的几种情况：null，比较，not函数

28.索引列上不要进行计算，如where trunc(order\_date)=trunc(sysdate) , i+2>4。索引失效的原因也简单，索引是针对原值建的二叉树，你将列值\*3/4+2折腾一番后，原来的二叉树当然就用不上了。解决的方法:(1)换成等价语法，比如trunc(order\_date) 换成where order\_date>trunc(sysdate)-1 and order\_date<trunc(sysdate)+1(2)特别为计算建立函数索引：create index Ｉ\_XXXX on shop\_order(trunc(order\_date))

29.低效：select \* from emp where deptno > 3高效：select \* from emp where deptno >=4  
两者的区别在于，前者dbms将直接跳到第一个deptno等于4的记录，而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的。

30.当插入的数据为数据表中的记录数量的10%以上，首先需要删除该表的索引来提高数据的插入效率，当数据插入后，再建立索引。

31.通常如果一个表有5个字段经常作为查询条件，那么创建5个单独字段的索引，然后再创建一个包含这5个字段的组合索引。索引的列尽量编写在where条件语句的最后，以便执行计划命中索引。

32.删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况).而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.

33.Update 多个Column 例子:  
低效: UPDATE EMP  
SET EMP\_CAT = (SELECT MAX(CATEGORY) FROM EMP\_CATEGORIES),  
SAL\_RANGE = (SELECT MAX(SAL\_RANGE) FROM EMP\_CATEGORIES)  
WHERE EMP\_DEPT = 0020;  
高效: UPDATE EMP  
SET (EMP\_CAT, SAL\_RANGE)= (SELECT MAX(CATEGORY) ,  
MAX(SAL\_RANGE)FROM EMP\_CATEGORIES)  
WHERE EMP\_DEPT = 0020;