Oracle宝典--设计规范

表设计原则

范式

1.大部分场景需要遵循第三范式。

2.适当的场景需要考虑反范式。

小表

1.一般需要设置主键。

2.一般需要设置约束。

3.尽量规划在一个表空间下。

大表

1.表超过10G需要考虑是否进行分区。

select owner,
       segment_name,
       segment_type,
       sum(bytes) / 1024 / 1024 / 1024 object_size
  from dba_segments t
 where t.segment_type = 'TABLE'
    or t.segment_type = 'TABLE PARTITION'
 group by owner, segment_name, segment_type
 order by object_size desc;

2.分区表分区超过100个需要注意。

3.大表需要设计数据维护。

4.大表不可以设计触发器。

表类型

1.表字段尽量加上注释

2.列避免使用LONG、CHAR

3.时间类型使用date、数字类型使用number、字符串类型使用Varchar2。

索引设计原则

1.用不上分区条件的局部索引不适宜建。

2.索引字段需要避免列运算。

3.位图索引不适合用在表频繁更新的场合。

4.位图索引不适合用在列重复度很低的场合。

--查询位图索引
select t.index_name, t.index_type, t.status, t.blevel, t.leaf_blocks
  from user_indexes t
 where index_type in ('BITMAP');

5.外键未建立索引将引发死锁及影响表连接的性能。

6.建立联合索引需要谨慎，根据实际设置联合索引的字段顺序。

7.避免超过4个字段的联合索引。

8.避免单表索引个数超过5个。

9.单表如果2个月没有使用索引，需要考虑是否删除索引。

10.单表如果没有任何索引，需要考虑是否需要增加。

索引常见失效场景

1.对表进行move操作，会导致索引失效，操作许考虑索引的重建。

2.对分区表进行系列操作时，如split，drop，truncate分区时，容易岛主分区表的全局索引失效，需要考虑增加update global indexes的关键字进行操作，或者重建索引。

3.分区表进行split时，如果max区中已经存在记录，这个时候split就会导致有记录的新增分区的局部

--普通索引失效查询
select t.index_name,
       t.table_name,
       blevel,
       t.num_rows,
       t.leaf_blocks,
       t.distinct_keys
  from dba_indexes t
 where t.status = 'INVALID';

--分区索引失效
select t2.owner,
       t1.blevel,
       t1.leaf_blocks,
       t1.index_name,
       t2.table_name,
       t1.partition_name,
       t1.status,
       t2.owner
  from dba_ind_partitions t1, dba_indexes t2
 where t1.index_name = t2.index_name
   and t1.status = 'UNUSABLE'
   and t2.owner in ('SYS');