海量数据库设计思想

分区

 

1.范围分区

A.通常按时间字段划分

CREATE TABLE 

PARTITION BY RANGE()

(

PARTITION partition_1 VALUES LESS THAN (TO_DATE('01/02/2000','DD/MM/YYYY')),

PARTITION partition_2 VALUES LESS THAN (TO_DATE('01/03/2000','DD/MM/YYYY'))

)

B.使用传递表空间的方式导出历史数据入OLAP数据库

     表空间上的对象必须为自包含，表示为：

      a.索引在这个表空间上，它所引用到的表不在其他表空间上

      b.分区表的部分分区不在其他表空间上

      c.完整性约束的参考对象不在其他表空间上

      d.表中包含的LOB对象不在其他表空间上

C.使用分区交换的方法，将分区表（不同分区使用不同表空间）的部分分区转换到某临时建立的表空间，然后使用表空间传递

ALTER TABLE   partition_table  EXCHANGE PARTITION   partition_1  WITH TABLE   tmp_table  INCLUDING INDEXES WITH VALIDATION

tmp_table与 partition_table有相同的表结构定义

2. 哈希分区

A.适合于在各个分区中数据分布要求均匀的情况下，比如分区表空间存放在不同的容量相近的物理介质中，可以提高DML的并行操作（因为平均）

CREATE TABLE

PARTITION BY HASH()

PARTITIONS 8

B.哈希分区是一种基于hash函数的分区方式，不能DROP,SPLIT以及MERGE分区，但是可以TRUNCATE分区，分区索引，分区裁剪可用

C.哈希分区数据均匀分布的前提条件是，hash列重复率比较低

3. 列表分区

提供一种按照字段的值来进行分区的方法，适合有高重复率的字段值的表

CREATE TABLE

PARTITION BY LIST()

(

PARTITION partition_1 VALUES(),

PARTITION partition_2 VALUES(),

PARTITION partition_other VALUES(DEFAULT) --默认分区

)

4. 组合分区

A.范围-哈希组合分区

CREATE  TABLE

PARTITION BY RANGE()

SUBPARTITION BY HASH()

(

PARTITION p1 VALUES LESS THAN()

     (

      SUBPARTITION sp1,

      SUBPARTITION sp2

     )

),

(

PARTITION p2 VALUES LESS THAN()

     (

      SUBPARTITION sp3,

      SUBPARTITION sp4

     )

)

B.范围-列表组合分区

CREATE  TABLE

PARTITION BY RANGE()

SUBPARTITION BY LIST()

(

PARTITION p1 VALUES LESS THAN()

     (

      SUBPARTITION sp1 VALUES () ,

      SUBPARTITION sp2 VALUES () 

     )

),

(

PARTITION p2 VALUES LESS THAN()

     (

      SUBPARTITION sp3 VALUES () ,

      SUBPARTITION sp4 VALUES () 

     )

)

索引

1. B树索引

A.主键或者唯一性约束

主键更强调表的关系性，它可以被其他表的外键所用

唯一性约束则强调字段值的唯一性

即使有上亿条记录的表中，B树索引的性能不被影响多少

B.键值重复率较低的字段

2. 位图索引

每一个位图索引的键值中，都会存放指向很多行的指针，而不是一行，这样，位图索引存放的键值就会比B树索引少得多

位图索引适合对索引的键值进行count这样的操作，也非常适合在索引字段间进行或（“OR”）与（“AND”）这样的逻辑运算操作

位图索引占用的空间要远小于B树索引占用的空间

不适用于有大量“写”操作的数据库，在于更新时会阻塞相应键值的多行记录

3. 全文索引

create index xxx on t(name) indextype is ctxsys.context;

select * from t where contain(name,'XXX') >0

创建了DR$开头的四个全文索引的基表，所有的全文索引的信息都保留在这些基表内。索引建立在基表上。全文索引本身并不包含数据，也不占用空间（无段对象在user_segments）

全文索引占用的空间非常大，会达到原表的几倍或者更多

以context方式创建的全文索引默认不是基于事务的，当提及DML操作，修改的信息不会影响索引，而是存放在DR$PENGING表中，需要同步索引更新

alter index xxx rebuild parameters('sync')

对象属性

OLTP系统以下的对象属性应该受到关注：

PCT_FREE               一个数据块中空闲空间的最小百分比

PCT_USED               一个数据块存储数据的最小百分比

INI_TRANS               一个数据块上最初分配的事务数

MAX_TRANS               一个数据块上最大分配的事务数

INITIAL_EXTENT          第一个extent的大小

NEXT_EXTENT          下一个extent的大小

MIN_EXTENTS          段首次分配的extent的大小

MAX_EXTENTS          最大可分配的extent的大小

PCT_INCREASE          extent扩展时，每次增加的百分数

FREELISTS               段中空闲块列表

CACHE                    是否缓存到内存中

OLAP系统以下的对象属性应该受到关注：

DEGREE

对象并行执行时使用的并行度，并非并行度越高，执行效率就越高，和系统资源，CPU数量，是否RAC架构等因素有关

在对象（表，索引等）上创建并行度

服务器CPU数X每个CPU启用的线程数=默认并行度

select degree from user_tables where table_name=''

使用HINT方式

select /*+ parallel(t1 4) */ from t1;

并行度不能在分区级别设置

COMPRESSION

分区表或者分区索引是可以单独压缩的

alter table t1 modify partition p1 compress;

分区索引的压缩要求先建索引的压缩，然后取消分区索引的压缩

alter index t1_idx rebuild partition p1 nocompress

数据压缩，释放更多的磁盘空间给其他对象使用，前提是数据重复率高有效

提高查询性能，压缩之后，扫描的数据块数量变少，物理I/O减少，一致性读减少。

DML操作涉及数据压缩的有：

alter table move

create table as select

insert /*+ append */

sqlldr+ direct path

普通的insert操作没有数据压缩