建表的几条原则
- 预估表记录数
- 表数据量是随着时间持续增加（像订单类表），并且运行一年后，表记录数超过2000万条，则需要考虑表分区。
- 如果表数据基本上是静态的（像用户信息表），一般不考虑分区。如果记录数接近亿条，则可以考虑用HASH分区。
- 表命名规范
- 表名只能使用字母、数字和下划线，字母都小写。
- 数据库里若存在多个用户，则以用户名简写开头，比如用户库的表以USR_开头，订单库的表以ORD_开头；若只有一个用户，则以t（表）或v（视图）开头。
- 临时表以tmp_开头；备份表以bak_开头。
- 字段规范
- 表的字段数量不超过30个，在非必要的情况下不要使用clob等大字段。一张表里所有字段的总长度尽量小，绝对不要超过8000（数据块大小就是8K）。
- 不同表里相同意义的字段，取名一样。列如A表的更新时间字段取名update_time，那B表就不要再取名modify_time。
- 选择字段类型的时候，能用number就不要用varchar2。做查询的时候，同等条件的number效率比varcha2高。
- 值为枚举型值的字段，用number(1)或者char(1)；如果有超过10个以上的枚举值，用char（2）。多个表里该类字段的值定义要一致。如a表status=0表示正常，则b表也应一样定义。这里可以约定一下：用char做类型时，值为A、B、C这样的字母类型；用number做类型时，值为0-9，这样方便区分。
- 字段尽量设置为not null,或者设置默认值。
- 备注
- 表和字段，都需要有comment，尤其是枚举型值字段。
建索引
- 与mysql索引区别
- 在mysql中，每个表都会有一个id字段做主键，也就是聚簇索引，其它的辅助索引都要通过聚簇索引来查询。
- 在oracle中，所有的索引都是通过rowid直接查询的，不存在“二次查询”，所有如果该id字段不与其他表做管理，则在表设计时不需要该字段。
- 指定表空间
- 将表数据和索引数据分开存储。建索引时明确指定表空间。
- 数量
- 索引数量不宜过多，一般情况下4个以内；索引太多会影响记录的插入。
- 复合索引
- 个人认为为了限制记录的唯一性时，可以建唯一索引；否则如果几个字段均有可能作为查询条件时，可以分成多个索引。
- 复合索引字段的先后顺序很重要，第一个字段必须是查询时使用最频繁的，否则有可能用不到索引。举个不恰当的例子：用户信息表里有证件类型(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排序操作，大量的排序操作影响性能。如必须使用排序操作，尽量用在有索引的列上。
- 数据清理时的注意点：
- 做delete或update数据时，首先要select count(*)一下，计算一下受影响的记录数。
- 如果受影响记录数是百万级的，或者查询条件是全表扫描的，那务必在查询条件增加个rownum<=10000，一次影响1万条。
- 举例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;
- 举例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;