ORACLE设计规范

ORACLE设计规范1、数据库模型设计方法规范1.1、数据建模原则性规范1.2、实体型之间关系认定规范1.3、范式化1NF的规范1.4、范式化2NF的规范1.5、范式化3NF的规范1.6、反范式化冗余字段使用规范1.7、数据库对象命名基本规范第一：长度规范：凡是需要命名的对象其标识符均不能超过30个字符，也即：Oracle中的表名、字段名，函数名，过程名，触发器名，序列名，视图名的长度均不能超过30个字符；第二：构成规范：数据库各种名称必须以字母开头，但严禁使用SYS开头；名称只能含有字母，数字和下划线“_”三类字符，“_”用于间隔名称中的各语义字段；不要使用DUAL作表名；第三：大小写规范：构成Oracle数据库中的各种名称（表明，字段名，过程名，视图名等等）的所有字符，必须使用大写，也就是不能在脚本中，对任何名称添加双引号“”来设定字符的大小写形式，只要不采用“”限制，Oracle自动会将各名称转化成大写。

2、表的设计规范2.1、表的主键规范遵循如下三点原则：第一：有无原则：除临时表和外部表，以及流水表，日志表外，其他表都要建立主键；第二：构成原则：主键不能使用含有实际语义的列，应该增加一个xx_id字段做主键，类型为number，取值来自序列sequence；第三：创建原则：对于500万以上的表，请数据组参与设计实施，采用先建唯一索引再添加主键约束的方式来创建主键；2.2、表的主键列规范对于实体表，主键就是一列，就是没有任何语义的自增的NUMBER列，对于关系表，主键就是相关实体表主键形成的复合主键，是多列；2.3、使用注释的规范2.4、一个表所含字段总长度的规范2.5、一个表所含字段访问频繁度的规范2.6、一个表所含数据量的规范2.7、大对象字段（BLOB，CLOB）使用规范2.8、增量同步表的设计规范字典信息表和需要使用增量同步的表必须增加如下属性：2.9、表的表空间使用规范2.10、索引的表空间使用规范3、设计分区表的规范3.1、RANGE分区的规范3.2、LIST分区的规范3.3、HASH分区的规范3.4、RANGE-LIST分区的规范3.5、RANGE-HASH分区的规范4、索引的设计规范4.1、主键索引的规范4.2、唯一约束索引的规范4.3、外键列索引的规范4.4、复合索引的规范4.5、函数索引的规范4.6、位图索引的规范4.7、反向索引的规范4.8、分区索引的规范4.9、索引重建的规范5、SQL访问规范5.1、避免SELECT *程序中不能出现SELECT*，即使是选择全部选择项，也需要全部指明，这主要出于如下原因：第一：使用*相对比较慢，因为Oracle 需要遍历更多的内部字典信息；第二：为避免以后相关表增加字段造成程序错误，比如INSERT INTO SELECT和SELECT INTO语句会报错；5.2、避免笛卡尔运算多表关联查询不能出现笛卡尔积，如果在报表中为集聚表（或称中间表）生成多个维度组成的复合主键需要使用迪克尔积的，必须请数据组确认性能。

5.3、使用CTAS备份在进行DML操作（INSERT，UPDATE，DELETE）之前，必须对数据进行备份，使用如下语句：方法一：表数据全部备份：CREATETABLE TAB_NAME_BAK AS SELECT * FROM TAB_NAME;方法二：部分备份：对大表仅备份将要修改的数据：CREATE TABLE TAB_NAME_BAKAS SELECT * FROM TAB_NAME WHERE [选择出被操作数据的条件];5.4、INSERT时需写全列名代码中INSERT语句必须写出全部列名，以保证表增加字段后语句执行不受影响：如：INSERT INTO TAB(COL1,COL2)VALUES(COL1_VAL,COL2_VAL)；再如：INSERT INTO TAB(COL1,COL2)SELECT COL1_VAL,COL2_VAL FROM TAB_BB；不能将COL1,COL2和COL1_VAL,COL2_VAL省略;5.5、大数据量的DMLDML操作涉及到大数据量时，请分解为多次执行；对于UPDATE和DELETE每次涉及数据量在1万条左右，并且每次执行完就提交；对于INSERT INTO SELECT如果采用提示(/*+ append parallel */)可以处理百万级别的数据量。

5.6、完成事务及时commit对于一个完成了的事务，请用commit显示提交，这是避免锁争用的锁等待的需要，特别是对DML操作频繁的表；5.7、java的变量绑定使用“变量绑定”来处理一条SQL带不同常量多次执行的情况，动态绑定可以大大优化SQL的执行效率，还可以优化Oracle的内存使用。

在Java中，结合使用setXXX系列方法，可以为不同数据类型的绑定变量进行赋值，从而大大优化了SQL语句的性能。

JAVA情况下的动态绑定示例如下：String v_id = 'xxxxx';String v_sql = 'select name from tb_a where id = ? ';stmt = con.prepareStatement( v_sql );stmt.setString(1, v_id ); //为绑定变量赋值stmt.executeQuery();5.8、perl的变量绑定使用“变量绑定”来处理一条SQL带不同常量多次执行的情况，动态绑定可以大大优化SQL的执行效率，还可以优化Oracle的内存使用。

PERL绑定变量实例如下：$modsql = qq{insert into tmp_tai_rtkpi_mark(tab_name,kpi_id,ne_id,timepoint,cacu_time,start_time,stop_time,down_base,up_base,ajast_flag,inuse_flag,cal_data)values(?,?,?,?,?,?,?,?,?,?,?,?)};if ( !$dbh->prepare($modsql) ) {writeToLog( "start SQL prepare Error!/n" . DBI::errstr . "/n/n" ); }$sth_msg_in_DB = $dbh->prepare($modsql)|| die( "start SQL prepare Error!/n" . $DBI::errstr . "/n" );$sth_msg_in_DB->bind_param( 1, $kpiid_tab{$kpi_id} );$sth_msg_in_DB->bind_param( 2, $kpi_id );$sth_msg_in_DB->bind_param( 3, -1 );$sth_msg_in_DB->bind_param( 4, -1 );$sth_msg_in_DB->bind_param( 5, $current_time );$sth_msg_in_DB->bind_param( 6, $start_time );$sth_msg_in_DB->bind_param( 7, $end_time );$sth_msg_in_DB->bind_param( 8, $temp_min );$sth_msg_in_DB->bind_param( 9, $temp_max );$sth_msg_in_DB->bind_param( 10, 0 );$sth_msg_in_DB->bind_param( 11, 1 );$sth_msg_in_DB->bind_param( 12, -1 );$sth_msg_in_DB->execute() || die( "SQL Execute Error!/n" . $DBI::errstr . "/n" );5.9、避免重复访问：使用group避免重复访问（一）：同源单组单查询:如下语句要避免：SELECT CLASS,sum(COL) FROM TAB_TEST WHERE CLASS=’A’ UNION ALLSELECT CLASS,sum(COL) FROM TAB_TEST WHERE CLASS=’B’ UNION ALLSELECT CLASS,sum(COL) FROM TAB_TEST WHERE CLASS=’C’改写成：SELECT CLASS,sum(COL) FROM TAB_TEST GROUP BY CLASS5.10、避免重复访问：竖向显示变横向现实避免重复访问（二）：竖向显示变横向显示问题语句：SELECTA.C1AC1,A.C2AC2,A.C3AC3,B.C1BC1,B.C2BC2,B.C3BC3,C.C1CC1,C.C2CC2,C.C3CC3FROM(SELECT'123'X,'SYNONYM'C1, sum(2)C2,count(1)C3FROMTAB WHERE TABTYPE= 'SYNONYM')A,(SELECT'123'X,'TABLE'C1, sum(2)C2,count(1)C3FROMTAB WHERE TABTYPE= 'TABLE')B,(SELECT'123'X,'VIEW'C1, sum(2)C2,count(1)C3FROMTAB WHERE TABTYPE= 'VIEW')C;正确使用形式如下：SELECTMAX(DECODE(TABTYPE,'SYNONYM','SYNONYM',NULL)) AC1,MAX(DECODE(TABTYPE,'SYNONYM',sum(2),0))AC2,MAX(DECODE(TABTYPE,'SYNONYM',count(1),0))AC3,MAX(DECODE(TABTYPE,'TABLE','TABLE',NULL)) BC1,MAX(DECODE(TABTYPE,'TABLE',sum(2),0))BC2,MAX(DECODE(TABTYPE,'TABLE',count(1),0))BC3,MAX(DECODE(TABTYPE,'VIEW','VIEW',NULL)) CC1,MAX(DECODE(TABTYPE,'VIEW',sum(2),0))CC2,MAX(DECODE(TABTYPE,'VIEW',count(1),0))CC3FROMTABWHERETABTYPE IN('TABLE','SYNONYM','VIEW')GROUPBY TABTYPE;5.11、避免重复访问：用表更新表避免重复访问（三）：一个表同时更新另一个表的多个字段问题SQL：使用TB_SOURCE表更新表TB_TARGET的多个字段UPDATE TB_TARGET A SETA.COL1 = (selectB.COL1 from TB_SOURCE B where B.id = A.id) ,A.COL2 = (selectB.COL2 from TB_SOURCE B where B.id = A.id) ,A.COL3 = (selectB.COL3 from TB_SOURCE B where B.id = A.id) ,A.COL4 = (selectB.COL4 from TB_SOURCE B where B.id = A.id)WHERE A.id IN ( select B.id from TB_SOURCE B)正确使用形式如下：UPDATE TB_TARGET ASET (COL1, A.COL2, A.COL3, A.COL4 )=(SELECT B.COL1, B.COL2, B.COL3, B.COL4 FROM TB_SOURCE B WHERE B.id = A.id)WHERE EXISTS (select 1 from TB_SOURCE B where B.id = A.id)5.12、数据库连接及时关闭程序中必须显示关闭数据库连接，不仅正常执行完后需显示关闭，而且在异常处理块（例如java的exception段）也要显示关闭。