oracle高级 列表分区 复合分区

--列表分区create table student(       sid number,       sname varchar2(20),       province varchar2(20))partition by list(province)(          partition north values('河南','安徽','河北','山东'),          partition south values('广东','海南','广西','江西'),          partition west values('新疆','青海','宁夏','山西'),          partition east values('上海','浙江','江苏'),          partition p1 values('湖北','湖南','重庆','四川'))insert into student (sid,sname,province)values(1,'wenwen','河南');insert into student (sid,sname,province)values(2,'hehe','河北');insert into student (sid,sname,province)values(3,'qiuqiu','河南');insert into student (sid,sname,province)values(4,'tuanzi','重庆');insert into student (sid,sname,province)values(5,'yaoguai','湖南');insert into student (sid,sname,province)values(6,'方圆','青海');insert into student (sid,sname,province)values(7,'娥娥','海南');insert into student (sid,sname,province)values(8,'哥哥','河南');select * from student partition(north);

--复合分区--先按成绩范围分区  0-60， 60-80  散列分区create table grades(       sno number,       sname varchar2(20),       grade number)partition by range(grade)subpartition by hash(sno,sname)(          partition p1 values less than (60)(                    subpartition sp1,                    subpartition sp2          ),          partition p2 values less than (80)(                    subpartition sp3,                    subpartition sp4                    ));insert into grades(sno,sname,grade)values(1,'wenwen',70);insert into grades(sno,sname,grade)values(2,'huahua',70);insert into grades(sno,sname,grade)values(3,'baibai',30);insert into grades(sno,sname,grade)values(4,'hehe',59);insert into grades(sno,sname,grade)values(5,'jiayou',57);insert into grades(sno,sname,grade)values(6,'keai',29);insert into grades(sno,sname,grade)values(7,'bajie',60);insert into grades(sno,sname,grade)values(8,'jiujiu',70);insert into grades(sno,sname,grade)values(9,'wowo',37);insert into grades(sno,sname,grade)values(10,'haha',78);insert into grades(sno,sname,grade)values(11,'dudu',54);insert into grades(sno,sname,grade)values(12,'heihei',70);insert into grades(sno,sname,grade)values(13,'eee',64);--insert into grades(sno,sname,grade)values(14,'manman',99);select * from grades;select * from grades partition(p1);select * from grades partition(p2);

--查询子分区的信息select * from grades subpartition(sp1);select * from grades subpartition(sp2);--查询当前用户所有的表分区信息select * from user_tab_partitions;--查询子分区信息select * from user_tab_subpartitions;--发现值没有对应的范围分区  新增一个分区alter table grades add partition p3 values less than (100);alter table grades add partition p4 values less than (maxvalue);--分区里面没有值  需要删除此分区alter table grades drop partition p4;--分区里面的数据没有意义，不删除分区，只删除数据alter table grades  truncate partition p3;