分区表基础介绍

分区表

Oracle提供了范围分区、散列分区、列表分区、组合分区4种分区的方法

 

范围分区：按照列值范围将数据分布到不同分区

SQL>  CREATE TABLE ran_tab (

  2                              range_key_column date ,

  3                              data varchar2(20)

  4                             )

  5    PARTITION BY RANGE (range_key_column)

  6    ( PARTITION part_1 VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy')),

  7      PARTITION part_2 VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))

  8    )

  9  /

 

插入值

SQL> insert into ran_tab(range_key_column,data) values(to_date('2005-05-01','yyyy-mm-dd'),'chifan');

SQL> insert into ran_tab(range_key_column,data) values(to_date('2005-06-01','yyyy-mm-dd'),'chifan');

 

查看分区

SQL> select * from ran_tab partition(part_1);

 

RANGE_KEY_COLUMN DATA

---------------- --------------------

2004-6-1         chifan

2003-6-1         chifan

 

SQL> select * from ran_tab partition(part_2);

 

RANGE_KEY_COLUMN DATA

---------------- --------------------

2005-5-1         chifan

2005-6-1         chifan

 

 

查看分区表

SQL> select segment_name,partition_name,tablespace_name

  2  from user_segments where segment_name='RAN_TAB';

 

SEGMENT_NAME                                                                   PARTITION_NAME                 TABLESPACE_NAME

------------------- ----------- ------------------------------ ---------------

RAN_TAB                                                                     PART_1                         USER02

RAN_TAB                                                                          PART_2                         USER02

 

增加分区

SQL> alter table ran_tab add partition part_3 values less than (to_date('2007-01-01','yyyy-mm-dd'));

 

Table altered

 

删除分区

SQL> alter table ran_tab drop partition part_3;

 

Table altered

 

散列分区：对列进行hash算法的分区，将数据均匀的分布到各个分区

SQL> CREATE TABLE p_hash_emp

  2   ( empno int,

  3    ename varchar2(20)

  4    )

  5   PARTITION BY HASH (empno)

  6   ( partition part_1 tablespace users,

  7     partition part_2 tablespace user02

  8    )

  9  /

 

Table created

 

插入数据

SQL> insert into p_hash_emp values(1,'a');

SQL> insert into p_hash_emp values(2,'b');

SQL> insert into p_hash_emp values(3,'c');

SQL> insert into p_hash_emp values(4,'d');

SQL> insert into p_hash_emp values(5,'de');

 

查看

SQL> select * from p_hash_emp partition(part_1);

 

                                  EMPNO ENAME

--------------------------------------- --------------------

                                      2 b

                                      5 de

 

SQL> select * from p_hash_emp partition(part_2);

 

                                  EMPNO ENAME

--------------------------------------- --------------------

                                      1 a

                                      3 c

                                      4 d

添加分区

SQL> alter table p_hash_emp add partition part_3;

Table altered

 

 

列表分区：将离散的数据有效的部署到不同分区

SQL> create table region_emp(

  2                          deptno number,

  3                          dname  varchar2(10)

  4                          )

  5  partition by list(dname)

  6               (partition p1 values ('a'),

  7                partition p2 values ('b','c'),

  8                partition p3 values ('d','e')

  9                )

 10  /

 

Table created

 

 

SQL> insert into region_emp values(1,'a');

SQL> insert into region_emp values(2,'b');

SQL> insert into region_emp values(3,'e');

 

 

SQL> select * from region_emp partition(p1);

    DEPTNO DNAME

---------- ----------

         1 a

 

SQL> select * from region_emp partition(p2);

     DEPTNO DNAME

---------- ----------

         2 b

 

SQL> select * from region_emp partition(p3);

     DEPTNO DNAME

---------- ----------

         3 e

 

 

分区表上建立索引

 

对于分区表来说，每个表分区对应一个分区段，当在分区表上建立索引时既可以建立全局索引，也可以建立分区索引。如果建立全局索引，索引数据会存放到一个索引段中，如果建立分区索引，则索引数据会存放到几个索引分区段中去

 

全局索引： 默认情况下载分区表上建立的索引索引全局索引，建立全局索引时也可以加参数global，当然默认就是这个值

SQL> create index ind_a on ran_tab(range_key_column);

Index created

 

SQL> create index ind_a on ran_tab(range_key_column) global;

Index created

两句话意思一样，只是前面省略默认参数

 

分区索引：建立分区索引时，索引数据存放到几个分区段中，分区索引是基于分区表建立的，不能基于普通表建立，并且分区个数与表的分区是完全对应的

SQL> create index ind_a on ran_tab(range_key_column) local;

Index created

 

查看分区索引

SQL> select partition_name,index_name from user_ind_partitions;

PARTITION_NAME                 INDEX_NAME

------------------------------ ------------------------------

PART_1                         IND_A

PART_2                         IND_A

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10678398/viewspace-700073/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10678398/viewspace-700073/