Oracle表分区处理及全局索引和本地索引

发表于 | 分类于

创建分区表

1.按年创建分区：

*注：粗体部分可以自行定义(即用双星号标志的地方)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

create table **tableName**(

​	**ID NUMBER(20) NOT NULL,**

​	**REMARK VARCHAR(100),**

​	**CREATE_TIME DATE**

)

PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'year'))

//按月创建分区表

//PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))

//按天创建分区表

//PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtodsinterval(1, 'day'))

//按周创建分区表

//PARTITION BY RANGE (CREATE_TIME) INTERVAL （numtodsinterval(7, 'day')）

(partition **part_name** values less than (**to_date('2019-06-27', 'yyyy-mm-dd')**));

已存在的表创建分区

1.给已存在的表进行重命名

1
2
3

alter table **tableName** rename to **newTableName**

2.创建新表和以前要做分区表的名称一致

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

create table **tableName**(

​	**ID NUMBER(20) NOT NULL,**

​	**REMARK VARCHAR(100),**

​	**CREATE_TIME DATE**

)

PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))

(PARTITION **PART_NAME** values less than(to_date('2019-06-27','yyyy-mm-dd')));

3.将重命名表的数据导入到新分区表中

1
2
3

insert into **tableName** select * from **tableName**

4.删除重命名的表

1
2
3

drop table **tableName**

分区表创建全局索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

//第一步创建一个分区表

create table **tableName**(

​	**ID NUMBER(20) NOT NULL,**

​	**REMARK VARCHAR(100),**

​	**CREATE_TIME DATE**

)

PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))

(PARTITION **PART_NAME** values less than(to_date('2019-06-27','yyyy-mm-dd')));



//创建全局索引

create  index **indexName** on **tableName(column)**

global partition by range (**column**)(

partition **parName1** values less than (**column**) ,

partition **parName2** values less than (maxvalue) 

);



//全局索引和表没有直接的关联，必须显式的指定maxvalue值。假如表中新加了分区，不会在全局索引中自动增加新的分区，必须手工添加相应的分区。  

//手动添加分区

alter talbe **tableName** add partition **parName3** values less than(**column**);

创建本地索引

使用本地索引，不需要指定分区范围因为索引对于表而言是本地的，当本地索引创建时，Oracle会自动为表中的每个分区创建独立的索引分区。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

create  index **indexName** on **tableName(column)**

local (

partition **parName1**,

partition **parName2**  

);

//手动添加分区

alter talbe **tableName** add partition **parName3** values less than(**column**);

– 查询当前表有多少分区

1
2
3

****select table_name,partition_name from user_tab_partitions where table_name = 'T_TEST_PART'

–查询这个表的某个分区表里的数据

1
2
3

select * from T_TEST_PART partition(分区表名);

本地索引和全局索引有一个显著的差别，本地索引可以创建成本地非前缀型，而全局索引只能是前缀型。

清空区分表数据、删除分区表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

//用来删除分区，元数据和数据将一并删除

alter table drop partition;



//全部删除

alter table tableName drop partition partitionName;



//清数据

alter table tableName truncate partition partitionName;