oracle数据库开发注意事项

前端之家收集整理的这篇文章主要介绍了oracle数据库开发注意事项前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

作为java程序员对数据库有或多或少的了解,基本使用大家都会,一些关键性的细节可能在平时的开发过程中没有注意到,导致写出来的sql查不出数据,或查询速度特别忙、超时等情况,以下是工作中常用oracle数据库的一些总结:

一、oracle数据库开发规范

1、所有的名称用英文

要用简单明了的英文单词或简明的英文缩写,不要用拼音,特别是拼音缩写。目的很明确,主要是让人容易明白这个对象是做什么用的。

2、一律大写,特别是表名

有些数据库,表的命名乃至其他数据对象的命名是大小写敏感的,为了避免不必要的麻烦,并且尊重通常的习惯,建议一律采用大写MysqL数据库和表名在 Windows 中是大小写不敏感的 ,而在大多数类型的 Unix 系统中是大小写敏感的)。

对于sql语句,如无特殊要求,也一律使用大写。

3、表的命名

①.基础表统一采用"<模块名>_<应用名>"构成

②.备份表,COMMENT部分必须说明具体意义,命名格式:原表名_YYYYMMDD

③.历史表(应用已经删除)需要保留,去掉模块名(CARD_,PAYMENT_)更改为HIST_。

④.表命名全大写,在存储过程或应用程序中使用时,全大写。

⑤.自用表同一采用格式"T_DB_<应用名>"。

4、属性(列或字段)

①.采用有意义的英文单词,如果单词过长,可采用缩写代替(或单词组合,单词过长可缩写)。列名尽可能简短、精要(单词过长的情况可简写),尽量控制在20个字符以内。

②.所有列名必须有COMMENT,并注明有限取值列所有取值的含义。如state 取值为0,1 需标明0和1的含义。

③.对于不同表相同内容属性字段的命名,必须统一命名,数据类型和精度定义也需统一。

④.除非必要,否则尽量不加冗余列。所谓冗余列,是指能通过其他列计算出来的列,或者是与某列表达同一含义的列,或者是从其他表复制过来的列等等。冗余列需要应用程序来维护一致性,相关列的值改变的时候,冗余列也需要随之修改,而这一规则未必所有人都知道,就有可能因此发生不一致的情况。除非是应用的特殊需要,或者是为了优化某些逻辑很复杂的查询等操作,可以适当考虑加冗余列。

⑤.除非必要,否则尽量不使用LONG,TEXT,BLOB,CLOB,NCLOB,LONG,LONG RAW这一类的数据类型,而是使用其他可以替代的数据类型;优先使用varchar2类型替代CHAR类型,除非列宽有严格的要求而且得到应用严格支持

⑥.字段的类型及精度在设计以及后面进行开发时,需要与应用的设计、开发人员商讨,以得到双方认可的类型及宽度。

⑦.表增加数据创建时间和更新时间。

⑧.状态等相同或者相近表示某种状态的字段统一使用varchar2,长度由开发和DBA协商拟定,成功(开启,生效等)为01,失败(关闭失效等)为02,无状态为03,其他为预留各开发人员自定。

⑨.金额字段或者涉及到钱的字段设置为非空,默认值为0,以避免NULL的问题。

5、主键/外键

①.表主(外)键命名格式:"PK(FK)_<应用名>_主(外)键标识",如:"PK_CARD_USER_INFO_ID"等。

②.每个表,都必须要有主键。主键是每行数据的唯一标识,保证主键不可随意更新修改,特别在不知道是否需要主键的时候,加上主键会为你的程序以及将来查找数据中的错误等等,提供一定的帮助。

③.特别提醒,不同的表主键的命名严禁使用相同的命名,避免混乱。

6、索引

①.创建表时必须指定主键名称,主键索引名称包括表名缩写_列名缩写和一个PK_前缀,如:"PK_CARD_USER_INFO_CARDID"等。

②.唯一键索引名称包括表名缩写_列名缩写和一个UNK_前缀,如:"UNK_TRAN_NORMAL_0101_TRANUUID"等。

③.外键列的索引名称包括子表名缩写_父表名缩写_列名缩写和一个FKN_前缀。

④.对于不用于约束的索引包括表名缩写_列名缩写和一个IDX_前缀,如:"INX_TRAN_NORMAL_0101_EQPID"等。

⑤.某些基于函数的索引包括表名缩写_列名缩写和一个FCN_前缀。

⑥.对于复合索引,需要添加的列名称以数字进行替代,数字按着列出现顺序排序。如:"INX_TRAN_SETTLE_0101_46_18"等。

注意:

①.根据实际业务需要经过讨论后建立索引,避免建无用索引。

②.索引必须建在索引表空间。

③.避免数据库索引抑制,即创建索引但是查询仍走全表扫描。

7、触发器

①.INSERT型触发器统一命名:"TR_INSERT_<应用名>"。

②.UPDATE型触发器统一命名:"TR_UPDATE_<应用名>"。

③.DELETE型触发器统一命名:"TR_DELETE_<应用名>"。

8、视图

视图统一命名:"V_<应用名>"。

9、序列

①.序列统一命名为:"SEQ_<应用名>_<列名>"组成。

②.若线上环境为RAC(oracle集群),如果使用cache,请慎重考虑业务问题。若需要保证缓存相对连续,创建序列是需要加order。

10、同义词

同义词命名与其基础对象的名称一致,但要去除用户前缀或含有远程数据库链接的后缀。同义词安全性考虑,使用数据用户隔离,需要使用link用户创建,link用户允许读取表,不允许做任何修改

11、存储过程

①.存储过程统一命名为:"PROC_存储过程功能标识(缩写)"。
②.所有存储过程参数入参格式必须为:IN_<参数名> IN 数据类型 --字段备注说明。
③.所
有存储过程参数出参格式必须为:OUT_<参数名> OUT 数据类型 --字段备注说明

CREATE PROCEDURE PROC_INSERT_HIST_CARDINFO (IN_START_DATE IN VARCHAR2,--起始日期
				    IN_END_DATE IN VARCHAR2,--结束日期   
                                    OUT_STREAMSID OUT VARCHAR2  --执行结果,0-成功,1-失败
) IS
BEGIN  
...
END;
所有存储过程开头放置注释,如下红字部分:
/*
 创建人:liuym
 创建时间:2017-06-10
 功能描述:根据传入的IN_START_DATE起始日期和IN_END_DATE结束日期,从旧系统卡表导数据到新系统卡表
*/

12、函数

①.函数统一命名为:"FUN_函数功能标识"。

②.注释方面请参照存储过程。

13、包

包统一命名为:"PKG_包标识"。

14、数据库新建表对象需要的元素

①.表空间。
②.表名及表备注。
③.字段名、字段类型、是否可空、默认值以及字段comment(注意:列出所有有限取值列取值的含义)。
④.主键、外键和唯一约束。
⑤.查询时经常用到的列以及查询方式。
⑥.表的查询频率以及数据量。


二、查询语句不走索引的常见原因

数据库访问表的方式:

1、全表扫描

2、索引访问方式

所要访问的表数据量比较大(数据量一般大于100W),查询返回的数据占表总数据的比例不大于10%且表的选择性比较好,尽量走索引扫描方式。

注:
“唯一键的数量/表中的行数”的比值越接近1,则该列的Oracle可选择性越高,该列就越适合创建索引。

因为优化器还不够强大,还有很多限制,或者因为一些逻辑原因,分析认为sql要走索引比较好,但是事实却无法正确利用索引。这时候,除了给ORACLE需要的统计信息之外,写的sql必须要能够给优化器足够多的额外有效信息,让优化器能够选择更好的执行计划。
要让给优化器正确使用需要的索引,要考虑两点:
1).如何避免优化器的限制
2).根据业务数据特点改写sql语句
说明:这里说的走不了索引,是指走不了正常的RANGE SCAN,非(FAST) FULL INDEX SCAN。

查询不走索引的常用情况:

1、使用谓词不等于(<>,!=),目前的优化器一般认为不等于的选择性不好,会找到很多行,所以用不上索引,就算强制索引,也是扫描全部的索引,走不了RANGE SCAN。

避免使用不等于(<>,!=)的解决方法

1)如果不等条件之外的值不多,而且是确认的,可以改为等值或IN查询,比如status状态字段一般值类别很少。

2)如果不等条件之外的值很多,可以改为> OR <的形式,当然第2种方法包含了方法1。

示例:

使用PL/sql,按F5可以查看sql的执行计划。使用不等于走的是全表扫描

注:card_id列创建组件索引。

2、NOT IN,NOT EXISTS与<>类似,也是走不了索引的(注意,这里说走不了索引,只针对于不等连接谓词,不包括其他谓词)。

加hint使用前置索引, 只能扫描全部索引。hint用法:/*+index(表名 索引名)*/

NOT EXISTS与NOT IN类似

3、 由于索引不能存储全为NULL的行,造成无法走索引。

解决方法
1)视业务要求是否可以不统计NULL,加条件where xxx IS NOT NULL,COUNT(列).....等等过滤NULL的语句

2)复合列索引,t(object_id,0)是一个特殊的函数索引,使用object_id作为前导列,它有明显的优点,可以不管object_id的值,也不用改sql,这比较好,因为加入的0是1个字节,也不会很大。

3)函数索引。

例如:NVL(COLUMN_NAME,0)
类似地可以使用NVL,DECODE,CASE WHEN等建立函数索引,但是函数索引有明显的缺点:

1)必须修改sql语句和函数索引匹配

2)这种类似的函数索引,必须要求column_name不存在0的数据,这样有所限制,如果column_name存在0,那么会使统计错误

4、LIKE前通配查询,造成无法走索引。

解决方法

1)是否可以根据业务需求把前通配去掉SELECT * FROM t WHERE t.NAME LIKE '%DINGJUN%';

--修改

SELECT * FROM t WHERE t.NAME LIKE 'DINGJUN%';

2) 是否和此LIKE一样的前通配或全通配sql有很多,如果是,考虑建立函数索引

5、对索引列使用了函数,数学运算,其他表达式等。

解决方法:去掉对索引列的相关运算,保持索引列纯净。

最常见:date列trunc(column_name)或者是varchar类型的列进行to_date。

6、ORACLE使用了隐式类型转换,导致索引无法使用。

解决方法:必须避隐式类型转换。

例如:

DROP TABLE t1;
CREATE TABLEt1(x VARCHAR2(100));
CREATE INDEXidx_t1 ON t1(x);
SELECT *FROM t1 WHERE x = 1;
原因:Varchar2优先级低于number,所以oracle会自动执行转换to_number(x)
,导致x列的索引无法被使用。


三、数据库中常见的问题

1、NULL值的问题

在oracle中NULL是一个‘不确定’的值,不能对NULL做任何算术运算,且NULL既不不等于NULL,也不等于NULL,只能通过IS NULL和IS NOT NULL来判断是否是空值。

2、尽量用NOT EXISTS代替NOT IN

①.NOT IN 子查询中要是有NULL返回,则查询结果集是NULL。

②.如果查询语句使用了not in,那么对内外表都进行全表扫描,没有用到索引;而not exists的子查询依然能用到表上的索引。

create table #t1(c1 int,c2 int);
create table #t2(c1 int,c2 int);
insert into #t1 values(1,2);
insert into #t1 values(1,3);
insert into #t2 values(1,2);
insert into #t2 values(1,null);
select * from #t1 where c2 not in(select c2 from #t2);  -->执行结果:无
select * from #t1 where not exists(select 1 from #t2 where #t2.c2=#t1.c2)  -->执行结果:1  3
3、对分区表查询时尽可能带上分区字段,否则分区无效。

4、应该建索引列的特点

①.经常查询的列

②.经常使用在WHERE子句中的列

③.经常需要排序的列

5、索引的优缺点

有点:

①.通过创建唯一索引,可以保证数据库表中数据的唯一性。

②.可以大大加快索引的检索速度,这也是创建索引的最主要原因。

③.可以加速表和表之间的连接。

④.在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

缺点:

①.创建索引和维护索引要耗费时间,这种时间随着数据量的增加增加

②.创建索引需要占用物理空间。

③.当对表中的数据进行增加删除修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

④.创建索引的过程中可能会锁表,导致无法对表中的数据做插入、删除和更新操作。

6、慎用触发器

触发器功能强大,可实现许多复杂的功能,但过多使用触发器会造成数据库及应用程序的维护困难。在数据库操作中,我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作…… 同时规则、约束、缺省值也是保证数据完整性的重要保障。如果我们对触发器过分的依赖,势必影响数据库的结构,同时增加了维护的复杂程度。

7、对数据量比较大的表进行查询时需多注意,尤其是涉及页面展示的查询

原文链接:https://www.f2er.com/oracle/208908.html

猜你在找的Oracle相关文章