VLDB超大数据库分区表之列表分区表详解
列表分区
列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。
Sql代码:
create table graderecord03
(
sno varchar2(10),
sname varchar2(20),
dormitory varchar2(3),
grade int
)
partition by list(dormitory)
(
partition d229 values('229') tablespace test01,
partition d228 values('228') tablespace test02,
partition d240 values('240') tablespace test03
)
以上根据宿舍来进行列表分区，插入与范围分区实验相同的数据，做查询如下：
Sql代码:
Insert into graderecord03 values('511601','魁','229',92);
insert into graderecord03 values('511602','凯','229',62);
insert into graderecord03 values('511603','东','229',26);
insert into graderecord03 values('511604','亮','228',77);
insert into graderecord03 values('511605','敬','228',47);
insert into graderecord03(sno,sname,dormitory) values('511606','峰','228');
insert into graderecord03 values('511607','明','240',90);
insert into graderecord03 values('511608','楠','240',100);
insert into graderecord03 values('511609','涛','240',67);
insert into graderecord03 values('511610','博','240',75);
insert into graderecord03 values('511611','铮','240',60);
select * from graderecord03 ;
select * from graderecord03 partition(d229);
select * from graderecord03 partition(d228);
select * from graderecord03 partition(d240);
d229分区所得数据如下:
SQL> select * from graderecord03 ;
SNO SNAME DOR GRADE
---------- -------------------- --- ----------
511601 ??? 229 92
511602 ??? 229 62
511603 ??? 229 26
511601 ??? 229 92
511602 ??? 229 62
511603 ??? 229 26
511604 ??? 228 77
511605 ??? 228 47
511606 ??? 228
511604 ??? 228 77
511605 ??? 228 47
SNO SNAME DOR GRADE
---------- -------------------- --- ----------
511606 ??? 228
511607 ??? 240 90
511608 ??? 240 100
511609 ??? 240 67
511610 ??? 240 75
511611 ??? 240 60
511607 ??? 240 90
511608 ??? 240 100
511609 ??? 240 67
511610 ??? 240 75
511611 ??? 240 60
22 rows selected.
SQL> select * from graderecord03 partition(d229);
SNO SNAME DOR GRADE
---------- -------------------- --- ----------
511601 ??? 229 92
511602 ??? 229 62
511603 ??? 229 26
511601 ??? 229 92
511602 ??? 229 62
511603 ??? 229 26
6 rows selected.
SQL> select * from graderecord03 partition(d228);
SNO SNAME DOR GRADE
---------- -------------------- --- ----------
511604 ??? 228 77
511605 ??? 228 47
511606 ??? 228
511604 ??? 228 77
511605 ??? 228 47
511606 ??? 228
6 rows selected.
SQL> select * from graderecord03 partition(d240);
SNO SNAME DOR GRADE
---------- -------------------- --- ----------
511607 ??? 240 90
511608 ??? 240 100
511609 ??? 240 67
511610 ??? 240 75
511611 ??? 240 60
511607 ??? 240 90
511608 ??? 240 100
511609 ??? 240 67
511610 ??? 240 75
511611 ??? 240 60
10 rows selected.