Oracle数据库开发规范

本文最后更新于：1 年前

一、Oracle数据库开发规范

数据库对象命名。命名只能使用英文字母、数字和下划线。名称长度不要超过30个字符，命名要用富有含义的英文词汇，避免使用拼音缩写。
各表之间相关列名尽量同名。
表、表字段、存储过程、函数等都必须要有注释。
SQL语句里面的Oracle关键字最好使用大写，表名、列名等小写。关键字如果小写，要求一条语句关键字全部小写，不允许一条语句里面关键字有的大写，有的小写。
数据库程序必须使用绑定变量。
使用表时必须要设定别名。
创建表和索引必须要分开表空间存放。目前创建脚本都没有指定表空间，表和索引都放在数据表空间。以后必须要指定表空间，表放在数据表空间，索引放在索引表空间。目前系统的数据表空间是ACCFUND，索引表空间是ACCFUND_INDEX。
表建主键时，必须指定主键索引空间为ACCFUND_INDEX，其次，主键名称应使用PK+表名缩写+主键字段名缩写规则来命名，不应使用ERWIN等数据库建模工具自动使用的名字（sys_开头）。索引名称应使用 IDX+表名缩写+索引字段名缩写规则来命名。
慎重使用数据库链接，数据库链接是比较影响性能的。
在新项目设计阶段，应该有DBA参加，在数据库对象确定之后才能开始编码。
尽量使用规范、优化的SQL语句，不能只注重功能，不考虑性能。

二、SQL语句优化建议

1、共享SQL语句

为了不重复解析相同的SQL语句，在第一次解析之后， ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存，可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同， ORACLE就能很快获得已经被解析的语句以及最好的执行路径。 ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用。

当你向ORACLE 提交一个SQL语句，ORACLE会首先在这块内存中查找相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格,换行等)。

共享的语句必须满足三个条件:

字符级的比较：当前被执行的语句和共享池中的语句必须完全相同；
两个语句所指的对象必须完全相同；
两个SQL语句中必须使用相同名字的绑定变量(bind variables)。

例如：第一组的两个SQL语句是相同的（可以共享），而第二组中的两个语句是不同的（即使在运行时，赋于不同的绑定变量相同的值）

1
2
3

select pin , name from people where pin = :blk1.pin;

select pin , name from people where pin = :blk1.pin;

1
2
3

select pin , name from people where pin = :blk1.ot_ind;

select pin , name from people where pin = :blk1.ov_ind;

2、选择最有效率的表名顺序(只在基于规则的优化器中有效)

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表：driving table）将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表（FROM子句中最后的那个表）并对记录进行排序，然后扫描第二个表（FROM子句中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

例如：表 TAB1 16,384 条记录；表 TAB2 1 条记录。

选择TAB2作为基础表（最好的方法）执行时间0.96秒

1	`select count(*) from tab1,tab2`

选择TAB1作为基础表（不佳的方法）执行时间26.09秒

1	`select count(*) from tab2,tab1`

如果有3个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。

例如：EMP表描述了LOCATION表和CATEGORY表的交集。

SELECT * 

FROM LOCATION L , 

   CATEGORY C,

   EMP E 

WHERE E.EMP_NO BETWEEN 1000 AND 2000

AND E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN

将比下列SQL更有效率

SELECT * 

FROM EMP E ,

LOCATION L , 

   CATEGORY C

WHERE  E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN

AND E.EMP_NO BETWEEN 1000 AND 2000

3、WHERE子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

4、SELECT子句中避免使用

当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用‘’是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中，会将‘’依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

5、尽量多使用COMMIT

只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少。

COMMIT所释放的资源：

回滚段上用于恢复数据的信息.
被程序语句获得的锁
redo log buffer 中的空间
ORACLE为管理上述3种资源中的内部花费

注意：在使用COMMIT时必须要注意到事务的完整性，现实中效率和事务完整性往往是鱼和熊掌不可得兼得。

6、用Where子句替换HAVING子句

避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于对一些集合函数的比较，如COUNT() 等等。除此而外，一般的条件应该写在WHERE子句中。

例如:

低效：

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION

GROUP BY  REGION

HAVING REGION != ‘SYDNEY’

AND REGION != ‘PERTH’

高效：

SELECT REGION，AVG(LOG_SIZE)

 FROM LOCATION

 WHERE REGION != ‘SYDNEY’

 AND REGION != ‘PERTH’

 GROUP BY REGION

7、减少对表的查询

在含有子查询的SQL语句中，要特别注意减少对表的查询。

低效：

SELECT TAB_NAME
   FROM TABLES
   WHERE TAB_NAME = ( SELECT TAB_NAME 
            FROM TAB_COLUMNS
             WHERE VERSION = 604)
   AND　DB_VER= ( SELECT DB_VER 
             FROM TAB_COLUMNS
              WHERE VERSION = 604)

高效：

 SELECT TAB_NAME
  FROM TABLES
   WHERE  (TAB_NAME,DB_VER)
= ( SELECT TAB_NAME,DB_VER) 
        FROM TAB_COLUMNS
       WHERE VERSION = 604)

8、使用表的别名（Alias）

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上，这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。（Column歧义指的是由于SQL中不同的表具有相同的Column名，当SQL语句中出现这个Column时，SQL解析器无法判断这个Column的归属）

9、用NOT EXISTS替代NOT IN

在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。为了避免使用NOT IN，我们可以把它改写成外连接（Outer Joins）或NOT EXISTS。

10、用表连接替换EXISTS

通常来说，采用表连接的方式比EXISTS更有效率。

低效：

SELECT ENAME

  FROM EMP E

  WHERE EXISTS (SELECT *
    
          FROM DEPT

          WHERE DEPT_NO = E.DEPT_NO

          AND DEPT_CAT = ‘A’);

高效：

SELECT ENAME

   FROM DEPT D,EMP E

   WHERE E.DEPT_NO = D.DEPT_NO

   AND DEPT_CAT = ‘A’ ;

11、避免在索引列上使用计算。这是一个非常实用的规则，请务必牢记

WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。

低效：

1 2	`select * from accfund.fund_defray fd where to_char(fd.fd_defraydate,'yyyy')='2009';`

高效：

1
2
3

select * from accfund.fund_defray fd
where fd.fd_defraydate>=to_date('20090101','yyyymmdd')
and fd.fd_defraydate< to_date('20100101','yyyymmdd');

12、用>=替代>

如果DEPTNO上有一个索引。

高效：

SELECT *

 FROM EMP

 WHERE DEPTNO >=4;

低效:

SELECT *

FROM EMP

WHERE DEPTNO >3;

两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录，而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

13、避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。

低效：（索引失效）

SELECT *

FROM DEPARTMENT

WHERE DEPT_CODE IS NOT NULL;

高效：（索引有效）

SELECT *

FROM DEPARTMENT

WHERE DEPT_CODE >=0;

14、总是使用索引的第一个列

如果索引是建立在多个列上，只有在它的第一个列（leading column）被where子句引用时，优化器才会选择使用该索引。

15、优化实例

在重建分区表之后，单位科反馈说缴费查询模块很慢，要10多分钟才出结果。我们将缴费查询的后台程序调出来，对存储过程进行单步调试，确定是其中一条SQL语句慢。

select max(FD_APPLYTIME)
   from accfund.fund_defray
   where --add by zqj 05/07/20
   (FD_DEFRAY_TYPE = '901' OR FD_DEFRAY_TYPE = '903' OR FD_DEFRAY_TYPE = '906')
   AND fd_insu_id = '1'
   and FD_MEMB_ID = '00755388'; --执行完需10分钟

上面的语句执行完成需要10多分钟，这条语句效率非常差，初步优化如下：

select max(fd.fd_applytime)
 from accfund.fund_defray fd
 where fd.fd_insu_id = '1'
  and fd.fd_defray_type in ('901', '903', '906')

  and fd.fd_memb_id = '00755388';  --执行完需2分钟

改变了where条件的顺序，经过初步优化之后，效率得到一定提升，但是仍然需要2分钟，不很理想，再次优化如下：

select /*+ index(fd IDX_FD_ORG_ID_NEW) */ max(fd.fd_applytime)
 from accfund.fund_defray fd
 where fd.fd_memb_id = '00755388'
  and fd.fd_insu_id = '1'
  and fd.fd_defray_type in ('901', '903', '906');

通过指定索引，SQL执行完只需0.2秒，基本达到优化效果；经过优化之后，缴费查询模块使用正常。

上面的例子是一条SQL影响一个模块，有一些更严重的会影响到整个数据库的运行，甚至导致宕机。因此一定要重视程序的高效性。

三、数据库系统优化计划

优化调整项目	优化效果	预计花费时间	备注
分析表和索引，生成统计信息，并制订定期分析计划	让系统使用CBO，SQL语句生成更优化的执行计划，缩短执行时间	24－48小时；计划在春节前完成	以后要每月更新一次统计信息
用statspack分析数据库性能，找出瓶颈并优化之	找出系统最消耗资源的事件，并优化之	12－20小时/次；需要不定期执行	需要在数据库繁忙时期执行，找出瓶颈
分离表和索引，逐步将ACCFUND下所有索引重建到索引表空间，并指定索引维护计划	表和索引分开，减少IO争用，便于管理	1－2个月	要制订计划，逐步更新，只能在下班操作，每天更新部分索引
将ACCFUND下所有对象导出，然后重新导入	通过导入导出整合数据库区、断的碎片，提升读写性能，改善SQL执行计划	48小时	需要有充足的停机时间；数据库创建到现在没有进行过碎片整理，有必要整理一次

数据库

#oracle

Oracle数据库开发规范

http://example.com/2022/06/26/Oracle数据库开发规范/

作者

Crush

发布于

2022年6月26日

更新于

2023年7月9日

许可协议

SQL Server学习笔记上一篇

SpringBoot-常见场景下一篇