Oracle Cluster table And Partition

簇表和分区表：

Oracle 提供了很多种存储数据的表形式。比如Heap、cluster.

在项目中我们如何选则物理的存储结构：

Ø  读取的行数存放的比较集中

Ø  SELECT or DML 语句

Ø  表达小

Ø  行大小和数据块大小

Ø  小事务或者大事务

Ø  SELECT 语句使用并行查询

何为簇表？

多个表的数据存放在同一个块之中。

比如一张emp和dept表。我们经常要在这两张表查询数据，我们就看了一将这两张表放在同一个块中，那么我们就可以用更少的IO获取我们所需要的数据。

SELECT d.dname,e.ename

From dept d ,emp e

Where d.deptno = e.deptno;

 

簇表又可以分为索引簇和哈希簇：

所谓索引簇就是使用索引访问数据。

Hash 簇就是oracle通过哈希函数进行数据的存储。

索引簇适用于表的数据不断的增长。

哈希表适用于标的数据不经常变动，相对静态的表。

-- CLUSTER 的key是 dept_id 在table中这个列可以不命名为dept_id 但是数据类型number必须匹配。

--Size选项是用来告诉Oracle预计有1024字节的数据和每一个Cluster 的key相关。

--Oracle 将使用这个信息来估算每一个块能容纳的最大cluster数目

--Size太高 每一个block存放很少的key，并且将使用更多的表空间，太低将存放太多的数据连接，将偏离Cluster目的

CREATE CLUSTER e_d_cluster(

  dept_id NUMBER

)SIZE 1024;

 

--如果簇表是index cluster ,那么 我们还需要建簇索引

--作用是：存储一个cluster key ,并且返回包含该key的block地址

CREATE INDEX e_d_cluster_idx

ON CLUSTER e_d_cluster

 

-- 建表

CREATE TABLE t_department (

    dept_id NUMBER PRIMARY KEY,

    dname VARCHAR2(14),

    loc VARCHAR2(13)

    )CLUSTER e_d_cluster(dept_id);

 

CREATE TABLE t_employee(

    emp_id NUMBER PRIMARY KEY,

    ename VARCHAR2(10),

    job VARCHAR2(10),

    mgr NUMBER,

    hiredate DATE,

    sal NUMBER,

    comm NUMBER,

    dept_id NUMBER REFERENCES t_department(dept_id)

) CLUSTER e_d_cluster(dept_id);

那么Oracle是怎么存放的呢？

 

 

 

 

 

 

 

                                                  

--View whether or not the two tables storing together

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(t_department.rowid) dept_rid,

DBMS_ROWID.ROWID_BLOCK_NUMBER(t_employee.rowid) emp_rid,

t_department.dept_id

FROM t_department ,t_employee

WHERE t_employee.dept_id = t_department.DEPT_ID

分区表：

1 Data Partitioning 的原因：

表太大不好管理，采取“分而治之”政策。将大表和索引分成容易管理的小块。

2数据分区的概念：我们不仅可以分区还可以进行更进一步的组合分区。

它是一种逻辑分区，不束缚于硬件的架构，完全依赖于业务的需求。

 

3 优缺点的比较：

Advantage:

Ø  增强可用性，如果标的某一个分区出现故障，表在其他分区的数据仍然可以用。

Ø  维护方便，如果某分区故障，只需要修复该分区既可。

Ø  均衡I/O:可以把不同的分区映射到磁盘以平衡I/O.

Ø  改善查询性能：有的放矢（只关心你需要的数据），并行处理（提高批量处理的能力

提高关键数据的可用性。滚动窗口操作。A操作B区，C操作E区，那么F,G区不受影响。

Disadvantage：

已经存在的表没有办法可以直接转化为分区表。

 

Oracle传统的分区类型：

范围分区：对数据库表中某一个值范围进行分区，根据该值得范围决定存储在那个分区上。

Hash分区（散列分区）：在Column值上使用散列算法。以确定将行放入到那个分区上。当列的值没有合适条件，建议使用散列分区；他通过指定分区编号来均匀分布数据一种分区类型。

List分区：当你需要明确控制将行如何映射到分区时，就使用列表分区方法。适合列的值只有固定的几个。

 

范围分区的适用情况：

适用于连续，按时间排序的数据。

在Insert 的时候不需要你指定插入到什么分区，

但是在查询的时候需要指定查看那个分区的数据，否则的话，就查看整张表。

SELECT * FROM INTO PARTION_TEST(part_1);

Update的时候也需要指定分区

UPDATE PARTION_TEST partition(part_2) t set t.pitem ='t_desk' where t.transaction_id=1;

Delete 和 Update也是一样的。

List Partition （列表分区）

适用于不连续域的数据分区，比如位置类的分区。

-- List 分区

CREATE TABLE NGP_SITE(

    site_id varchar2(2) primary key,

    site_name varchar2(10) not null,

);

PARTITION BY LIST(SITE_ID)(

    PARTITION P_SITE1 VALUES('1',’1’,’3’) tablespace space01;

    PARTITION P_SITE7 VALUES('7') tablespace space02;

);

 

--Hash 分区

PARTITION BY HASH(site_name)(

    partition paert01 tablespace p1;

    partition paert02 tablespace p2;

    partition paert03 tablespace p3;

);

-- 分区分为8个 数据分别存储到EMP 8个表空间

PARTITION BY HASH(SITE_NAME) PARTITIONS 8

STORE IN (EMP1,EMP2,EMP3,EMP4,EMP5,EMP6,EMP7,EMP8);

分区的个数建议使用2^n 个。

 

==========================================

非传统分区:

比如范围-列表分区：

 

分区索引

局部分区索引：只在某一个分区里面建立的索引

CREATE INDEX SITE_IDX ON NGP_SITE(SITE_ID) LOCAL(

    PARTITION idx_1 tablespace space01;

    PARTITION idx_1 tablespace space02;

);

本地前缀分区索引：即表的索引列和分区的列一样。

全局分区索引：

CREATE INDEX IDX_TXN_CURRENT ON NFP_SITE(site_id)

GLOBAL PARTITION BY RANGE(SITE_ID)(

    PARTITION plvalues LESS THAN ('028'),----表示成都

    PARTITION plvalues LESS THAN ('021'),----表示上海

);