Oracle数据库的分区教程

• 分区

日常开发中最常用的技术，主要针对于大数据量，频繁查询数据等需求

oracle 提供了 7种分区，本文主要介绍较常用的几种

• range 分区

  
  
  
  
  
区域分区，分区的时候必须在创建表时指定

  
  

- 语法：
  
    
    
    
    
    
  ```
    create table (...) partition by range (filed) 
        (partition r1 values less than (value));
    
    # value 为分区的依据，比如value=1000，即不超过1000的值都会存放在此分区，
    # 如果只有一个分区，那么大于1000的值便无法插入。可以指定value=maxvalue
  
  ```
- 查看分区情况：
  
    
    
    
    
    
  所有分区的情况，可以通过 where 条件判断
  
    
    
    
    
    
  ```
    select * from user_table_partitions;  
  
  ```
- 查看分区数据：
  
    
    
    
    
    
  ```
    select * from table partition(r1);
  
  ```
- 修改分区：
  
    
    
    
    
    
  add
  
    
    
    
    
    
  ```
    alter table tablename add partition r2 values less than (maxvalue);
  
  ```
  
    
    
    
  del
  
    
    
    
    
    
  ```
     alter table tablename drop partition r2;
  
  ```
- 跨分区操作
  
    
    
    
    
    
  更新数据时不可以跨分区操作，比如更新之后的内容要从1分区移动到2分区，那么需要设置可移动的分区才可以跨分区查询，否则无法操作。
  
    
    
    
    
    
  ```
    alter table tablename enable row movement;
  
  ```
- 分区索引
  
    
    
    
    
    
  分区之后虽然提高了查询的效率，但仅仅是提高了数据的范围，依然需要建立分区索引，进一步提高效率。
  
    
    
  
  - 分区索引分为两大类 
    - local； 在每个分区建立索引
      
        
        
        
        
        
      语法： create index index\_name on table(field) local;
      
        
        
        
        
        
      查看： select \* from user\_ind\_partitions;
      
        
        
        
        
        
      索引的字段一定是分区的字段
    - global； 全局索引
      
        
        
      
      - 一种是全局建立索引，这种方式分不分区都一样，不建议使用；
      - 前缀索引
        
          
          
          
          
          
        还有一种是自定义索引的区间的索引，即`前缀索引`，这个是非常有意义的。
        
          
          
          
          
          
        语法： 必须指定 maxvalue
        
          
          
          
          
          
        ```
          create index index_name on table(field) global
              partition by range(filed) (
              partition r1 values less than(value),
              partition r2 values less than(maxvalue)
            );
        
        ```

• hash 分区

  
  
  
  
  
实现了均匀的负载值分配，增加 hash 分区可以重新分配数据

  
  
  
  
  
建立

  
  
  
  
  
```
  create table table_name (empno number,ename varchar(20)) 
      partition by hash(empno) (partition p1, partition p2)

```

  
  
  
查看分区结构

  
  
  
  
  
```
  select * from user_tab_partitions where table_name= "table_name";

```

  
  
  
查看分区数据

  
  
  
  
  
```
  select * from table_name partition(p1);

```

• list 分区

  
  
  
  
  
创建

  
  
  
  
  
```
  create table table_name (
      empno number,
      ename varchar(20), 
      city varchar(20)
      partition by list(city) (
          partition east values("shanghai“)
      )
  )

```

• 复合分区 对range分区的再次hash分区

  
  

- 创建
  
    
    
    
    
    
  ```
    create table table_name(
        sno number,
        sname varchar(20)
    )
    partition by range (sno)
    subpartition by hash (sname) # 子分区
    subpartition 4               # 子分区分成4分
    (
        partition p1 values less than(1000),
        partition p2 values less than(maxvalue)
    )
  
  ```

• 间隔分区， 最常用及最好用

  
  
  
  
  
是一种分区自动化的分区，可以指定时间间隔分区，一直是 oracle 引以为荣的一项技术

  
  
  
  
  
语法：

  
  
  
  
  
```
  create table table_name (
      sid int,
      sdata timestamp )
      partition by range(sdata) 
      interval (
          numtoyminterval(1, "MONTH")  # 函数，计算月份
                       )
          (
              partition p1 values less than (TIMESTAMP'2019-02-01 00:00:00')
          )

```

  
  
  
相当于在 2014-02-01 之前的数据，建立一个分区；

  
  
  
  
  
之后的数据每隔一个月建立一个分区。