建表的几条原则

• 预估表记录数

1. 表数据量是随着时间持续增加（像订单类表），并且运行一年后，表记录数超过2000万条，则需要考虑表分区。
2. 如果表数据基本上是静态的（像用户信息表），一般不考虑分区。如果记录数接近亿条，则可以考虑用HASH分区。

• 表命名规范

1. 表名只能使用字母、数字和下划线，字母都小写。
2. 数据库里若存在多个用户，则以用户名简写开头，比如用户库的表以USR_开头，订单库的表以ORD_开头；若只有一个用户，则以t（表）或v（视图）开头。
3. 临时表以tmp_开头；备份表以bak_开头。

• 字段规范

1. 表的字段数量不超过30个，在非必要的情况下不要使用clob等大字段。一张表里所有字段的总长度尽量小，绝对不要超过8000（数据块大小就是8K）。
2. 不同表里相同意义的字段，取名一样。列如A表的更新时间字段取名update_time，那B表就不要再取名modify_time。
3. 选择字段类型的时候，能用number就不要用varchar2。做查询的时候，同等条件的number效率比varcha2高。
4. 值为枚举型值的字段，用number(1)或者char(1)；如果有超过10个以上的枚举值，用char（2）。多个表里该类字段的值定义要一致。如a表status=0表示正常，则b表也应一样定义。这里可以约定一下：用char做类型时，值为A、B、C这样的字母类型；用number做类型时，值为0-9，这样方便区分。
5. 字段尽量设置为not null,或者设置默认值。

• 备注

1. 表和字段，都需要有comment，尤其是枚举型值字段。

建索引

• 与mysql索引区别

1. 在mysql中，每个表都会有一个id字段做主键，也就是聚簇索引，其它的辅助索引都要通过聚簇索引来查询。
2. 在oracle中，所有的索引都是通过rowid直接查询的，不存在“二次查询”，所有如果该id字段不与其他表做管理，则在表设计时不需要该字段。

• 指定表空间

1. 将表数据和索引数据分开存储。建索引时明确指定表空间。

• 数量

1. 索引数量不宜过多，一般情况下4个以内；索引太多会影响记录的插入。

• 复合索引

1. 个人认为为了限制记录的唯一性时，可以建唯一索引；否则如果几个字段均有可能作为查询条件时，可以分成多个索引。
2. 复合索引字段的先后顺序很重要，第一个字段必须是查询时使用最频繁的，否则有可能用不到索引。举个不恰当的例子：用户信息表里有证件类型(cert_type)和证件号码(cert_no)，建唯一索引的时候，索引字段顺序肯定是需要cert_no,cert_type，而不是cert_type, cert_no。因为查询的时候，很有可能只是用了cert_no，而没用到cert_type。我说“不恰当”，是因为oracle的CBO比较厉害，在使用cert_type,cert_no作为复合索引的时候，oracle会分析后以跳跃索引处理，仍然能用到索引。

SQL基本规范

• 日期类型字段用法

错误用法：to_char(create_time)>’20220101’

正确用法：create_time>to_date(‘20220101’,’yyyymmdd’)+0.999

说明：create_time为date类型，如果create_time字段有索引，加了函数就会用不到索引。

• 写查询条件时，字段类型必须一致

比如，status为char(1)，就不要写成status=1，而是status=’1’。否则后续若status字典值扩展了，用了字母，sql就会报错。

• 索引列字段不用函数，查询时要尽可能将函数操作移至等号右边。
• 尽量避免使用order by和group by排序操作，大量的排序操作影响性能。如必须使用排序操作，尽量用在有索引的列上。
• 数据清理时的注意点：

1. 做delete或update数据时，首先要select count(*)一下，计算一下受影响的记录数。
2. 如果受影响记录数是百万级的，或者查询条件是全表扫描的，那务必在查询条件增加个rownum<=10000，一次影响1万条。
3. 举例1：（删除500万行记录）

declare

i integer:=500;

begin

while i>0 loop

delete from oper_log t where t.create_time<to_date('20220228','yyyymmdd') and rownum<=10000;

commit;

i := i-1;

end loop;

end;

1. 举例2：（按天清理数据到备份表）

declare

i integer:=200;

mindate varchar2(8) :='20211001';

maxdate varchar2(8) :='20211231';

currdate varchar2(8) := mindate;

begin

while currdate <= maxdate loop

insert into bak_oper_log

select * from oper_log t where t.create_time<to_date(currdate,'yyyymmdd');

delete from oper_log t where t.create_time<to_date(currdate,'yyyymmdd');

commit;

currdate := to_char(to_date(currdate,'yyyymmdd')+1,'yyyymmdd');

end loop;

end;