详解：Oracle数据库的分区表

悠悠 2024-02-19 21:01 67阅读 0赞

## Oracle数据库的分区表 ##

### 表分区 ###

Oracle允许用户把一个表中的所有行分为几个部分，并将这些部分存储在不同的位置。被分区的表称为分区表，分成的每一个部分称为一个分区。

**优点：**

1）改善表的查询性能，在对表进行分区后，用户执行SQL查询时可以只访问表中的特定分区而非整个表。

2）表更容易管理，因为分区表的数据存储在多个部分中，按分区加载和删除数据比在表中加载和删除更容易。

3）便于备份和恢复，可以独立地备份和恢复每个分区。

4）提高数据安全性，将不同的分区分布在不同的磁盘，可以减小民有分区的数据同时损坏的可能性。

**分区条件：**

1）数据量大于2GB。

2）已有的和新添加的数据有明显的界限划分。

3）要分区的表不能具有long和long raw数据类型的列。

**分区表的分类**：  
范围分区、列表分区、散列分区、复合分区、间隔分区和、虚拟列分区等。

### 范围分区(range) ###

范围分区(range)是应用范围比较广的表分区方式，它以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。

可以在分区后加上(tablespace tablespace\_name)来指定该分区在哪个表空间中。这样可以提高查询性能和安全性。

一般创建范围分区时，都会将最后一个分区设置为maxvalue，使其他落入此分区。一旦需要某一数据时，可以利用拆分分区的技术将需要的从最后一个分区分离出去，单独形成一个分区，如果没有创建足够大的分区，插入的数据超出范围就会报错。如果插入的数据就是分区键上的值，则该数据落入下一分区。在按时间分区时，如果某些记录暂时无法预测范围，则可以创建maxvalue分区，所有不在指定范内的记录都会被存储到maxvalue所在的分区中。

create table sales1
    (
        sales_id number,
        product_id varchar2(5),
        sales_date date not null,
        ....
    )
    partition by range (sales_date)
    (
    partition p1 values less than (to_date(‘2013-04-1’,’yyyy-mm-dd’)),
    
    partition p2 values less than (to_date(‘2013-07-1’,’yyyy-mm-dd’)),
    
    partition p3 values less than (to_date(‘2013-10-1’,’yyyy-mm-dd’)),
    
    partition p4 values less than (to_date(‘2014-01-1’,’yyyy-mm-dd’)),
    
    partition p5 values less than (maxvalue)
    
    );

–查看第三季度的数据：select \* from sales1 partition(p3);

–删除第三季度的数据：delete from sales1 partition(p3);

### 间隔分区(Interval) ###

间隔分区(Interval)是Oracle 11g版本新引入的分区方法，是范围分区的一种增强功能，可以实现范围分区的自动化。优点为，在不需要创建表时就将所有分区划分清楚。间隔分区随着数据的增加合划分更多的分区，并自动自动创建新的分区。

例: --创建间隔分区表

create table sales2
    (
        sales_id number,
    
        product_id varchar2(5),
    
        sales_date date not null,
    
        ....
    )
        partition by range(sales_date)
    
        interval(numtoyminterval(3,’MONTH’))
    
        (partition p1 values less than (to_date(‘2013-05-1’,’yyyy/mm/dd’)));

–插入数据

insert into sales2 values (1,’a’,to\_date(‘2013-08-1’),’1’);

–获得分区情况

select table\_name,parttion\_name

from user\_tab\_partitions

where table\_name=uppper(‘sales2’);

–查询输出结果，系统自动根据输入数据情况创建新分区“SYS\_P82”

TABLE\_NAME PARTITION\_NAME

\------------------------------

SALES2 P1

SALES2 SYS\_P82

–查询分区数据

select \* from sales2 partition(sys\_p82);

1）只需创建第一个开始分区。

2）interval(numtoyminterval(3,’MONTH’))语句中，interval代表“间隔”，即按照后面括号中的定义间隔添加分区。

3）numtoyminterval(3,’MONTH’)表示每3个月为一个分区。

numtoyminterval(n, ‘interval\_unit’)函数用于将n转换成interval\_unit所指定的值。

interval\_unit的值可以为YEAR或MONTH。

与该类型相关的函数还有numtodsinterval(n, ‘interval\_unit’)，用于将n转换成interval\_unit所指

定的值。这里的interval\_unit的值可以设为DAY，HOUR，MINUTE，SECOND。但不支持YEAR和MONTH

4）系统会根据自动创建分区。

经验：可以利用间隔分区将开始创建时没有分区的表创建为新的间隔分区表。代码如下：

create table sales3
    
        partition by range(sales_date)
    
        interval(numtoyminterval(3,’MONTH’))
    
        partition p1 values less than (to_date(‘2013-04-1’,’yyyy/mm/dd’)))
    
        as select * from sales; --sales表为已经创建的表

### 列表分区 ###

列的值可以枚举的

create table   sales(
    
    　　sid number,
    
    　　sarea varchar2(10),
    
    　　total number
    
    )
    
    partition by list (sarea)(
    
        partition part1 values('北京') tablespace space1,
    
        partition part2 values('上海') tablespace space2,
    
        partition part3 values('广东') tablespace space3
    
    );

### 散列分区 ###

服务器自动给每一行给一个hash值，然后自动分区。只需要指定有哪些分区就可以了

create table employee(
    
    empno number,
    
    ename varchar2(10)
    
    )
    
    partition by hash(ename)(
    
       artition part1 tablespace space1,
    
       partition part2 tablespace space2,
    
       partition part3 tablespace space3
    
    );
    
    select * from  employee partition (part1);
    
    select * from  employee partition (part2);
    
    select * from  employee partition (part3);