表分区

===================================范围分区(range)=====================================

1.新建分区表student

create table student
(
studentid integer not null,
studentname varchar2(20),
score integer
)
partition by range(score)
(
partition p1 values less than(60),
partition p2 values less than(75),
partition p3 values less than(85),
partition p4 values less than(maxvalue)
);

2.插入数据
scott>desc student;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------------

 STUDENTID                                 NOT NULL NUMBER(38)
 STUDENTNAME                                        VARCHAR2(20)
 SCORE                                              NUMBER(38)

scott>insert into student values (&stun, '&name', &score);
输入 stun 的值:  10
输入 name 的值:  user1
输入 score 的值:  52
原值    1: insert into student values (&stun, '&name', &score)
新值    1: insert into student values (10, 'user1', 52)

已创建 1 行。

scott>/
输入 stun 的值:  11
输入 name 的值:  sz
输入 score 的值:  85
原值    1: insert into student values (&stun, '&name', &score)
新值    1: insert into student values (11, 'sz', 85)

已创建 1 行。

==================================散列分区(hash)====================================
create table sodi
(
deptno int,
deptname varchar2(14)
)
partition by hash (deptno)
(partition p1,
partition p2
)

=================================复合分区(range hash)=====================================
create table branchdetails1
(
branch_id varchar2(5) not null,
branch_name varchar2(10) not null,
address varchar2(10)
)
partition by range (branch_id)
subpartition by hash (branch_name)
subpartitions 6
(
partition s1 values less than ('b005'),
partition s2 values less than ('b010'),
partition s3 values less than ('b015'),
partition s4 values less than ('b020')
);

=================================列表分区(list)=====================================
CREATE TABLE Employee
(
    Emp_ID number (4),
    Emp_Name varchar2 (14),
    Emp_Address varchar2 (15)
)
PARTITION BY LIST (Emp_Address)
(
    Partition north values ('芝加哥'),
    Partition west values ('旧金山', '洛杉矶'),
    Partition south values ('亚特兰大', '达拉斯', '休斯顿'),
    Partition east values ('纽约', '波斯顿')
);

insert into employee values (110, 'july', '芝加哥');
insert into employee values (111, 'claduo', '旧金山');
insert into employee values (112, 'apple', '洛杉矶');
insert into employee values (113, 'wind', '亚特兰大');
insert into employee values (114, 'snow', '达拉斯');
insert into employee values (115, 'buick', '休斯顿');
insert into employee values (116, 'toyoto', '纽约');
insert into employee values (117, 'bush', '波斯顿');
insert into employee values (119, 'casy', '芝加哥');
insert into employee values (120, 'cool', '纽约');

 

====================================分区维护操作=======================================
1.添加分区(范围)
 ALTER TABLE table_name ADD PARTITION XX VALUES LESS THAN (XXX);
2.删除分区
 ALTER TABLE table_name DROP PARTITION XX;
3.截断分区
 ALTER TABLE table_name TRUNCATE PARTITION XX;
4.合并分区
 ALTER TABLE talbe_name MERGE PARTITIONS partitions_name,partitions_name INTO PARTITION partition_name;
5.拆分分区
 ALTER TABLE table_name SPLIT PARTITION partitioni_name AT (value) INTO (PARTITION partition1, PARTITION partition2);
 
重命名分区
 ALTER TABLE table_name RENAME PARTITION partition1 TO partition2;