oracle超大数据库表分区只哈希分区表详解
oracle超大数据库表分区只哈希分区表详解
散列分区
散列分区是根据字段的hash值进行均匀分布,尽可能的实现各分区所散列的数据相等。
还是刚才那个表,只不过把范围分区改换为散列分区,语法如下(删除表之后重建):
Sql代码:
create table graderecord02
(
sno varchar2(10),
sname varchar2(20) ,
dormitory varchar2(3) ,
grade int
)
partition by hash(sno)
(
partition p1 tablespace test01,
partition p2 tablespace test02,
partition p3 tablespace test03
);
/
插入实验数据,与范围分区实验插入的数据相同。
Insert into graderecord02 values('511601','魁','229',92);
insert into graderecord02 values('511602','凯','229',62);
insert into graderecord02 values('511603','东','229',26);
insert into graderecord02 values('511604','亮','228',77);
insert into graderecord02 values('511605','敬','228',47);
insert into graderecord02(sno,sname,dormitory) values('511606','峰','228');
insert into graderecord02 values('511607','明','240',90);
insert into graderecord02 values('511608','楠','240',100);
insert into graderecord02 values('511609','涛','240',67);
insert into graderecord02 values('511610','博','240',75);
insert into graderecord02 values('511611','铮','240',60);
然后查询分区数据:
Sql代码:
select * from graderecord02 partition(p1);
select * from graderecord02 partition(p2);
select * from graderecord02 partition(p3);
p1分区的数据:
SQL> select * from graderecord02 partition(p1);
SNO SNAME DOR GRADE
---------- -------------------- --- ----------
511603 ??? 229 26
511608 ??? 240 100
SQL> select * from graderecord02 partition(p2);
SNO SNAME DOR GRADE
---------- -------------------- --- ----------
511602 ??? 229 62
511604 ??? 228 77
511605 ??? 228 47
511606 ??? 228
511607 ??? 240 90
511609 ??? 240 67
6 rows selected.
SQL> select * from graderecord02 partition(p3);
SNO SNAME DOR GRADE
---------- -------------------- --- ----------
511601 ??? 229 92
511610 ??? 240 75
511611 ??? 240 60
SQL> select * from graderecord02;
SNO SNAME DOR GRADE
---------- -------------------- --- ----------
511603 ??? 229 26
511608 ??? 240 100
511602 ??? 229 62
511604 ??? 228 77
511605 ??? 228 47
511606 ??? 228
511607 ??? 240 90
511609 ??? 240 67
511601 ??? 229 92
511610 ??? 240 75
511611 ??? 240 60
11 rows selected.