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;