--范围分区
create table person( id int, name varchar2(20), birth date, sex char(2))partition by range (birth) --根据年龄分区( partition p1 values less than (to_date('19950517','yyyymmdd')), partition p2 values less than (to_date('20000517','yyyymmdd')), partition p3 values less than (to_date('20100517','yyyymmdd')), partition p4 values less than (maxvalue));--列表分区create table person1( id int, name varchar2(20), birth date, sex char(2))partition by list (sex) --根据性别分区( partition l1 values('男')， partition l2 values('女')， partition l3 values(default));--hash分区--不指定分区名create table person2( id int, name varchar2(20), birth date, sex char(2))partition by hash (id)partitions 2 store in (system,users);--指定分区名create table person3( id int, name varchar2(20), birth date, sex char(2))partition by hash (id)( partition h1， partition h2， partition h3);--复合分区--范围——列表create table person4( id int, name varchar2(20), birth date, sex char(2))partition by range (birth)subpartition by list (sex)subpartition template( subpartition sp1 values ('男'), subpartition sp2 values ('女'), subpartition sp3 values (default))( partition p5 values less than (to_date(19950517,'yyyyMMdd')), partition p6 values less than (to_date(20000517,'yyyyMMdd')), partition p7 values less than (to_date(20100517,'yyyyMMdd')), partition p8 values less than (maxvalue));--范围——hash
create table person5( id int, name varchar2(20), birth date, sex char(2))partition by range (birth)subpartition by hash(id)subpartition template( subpartition sp4, subpartition sp5, subpartition sp6 )( partition p9 values less than (to_date(19950517,'yyyyMMdd')), partition p10 values less than (to_date(20000517,'yyyyMMdd')), partition p11 values less than (to_date(20100517,'yyyyMMdd')), partition p12 values less than (maxvalue) );--查询
--相关字典表:--select * from user_objects where object_name ='表名';--select * from user_tables where table_name = '表名';--select * from user_tab_partitions where table_name = '表名';--select * from user_tab_subpartitions where table_name = '表名';--查询分区
select * from person partition (p2);select * from person1 partition (l2);select * from person2 partition (SYS_P548);select * from person3 partition (h2);select * from person4 subpartition (p7_sp2);select * from person5 subpartition (p11_sp5);--删除表分区
--alter table 表名 drop partition 分区名;--创建序列
create sequence seq_partition minvalue 1 maxvalue 500 increment by 1;--插入数据insert into person values (seq_partition.nextval,'吕布',to_date('19900517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'赵云',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'关羽',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'黄忠',to_date('19900517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'张辽',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'张飞',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'高顺',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'马超',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'甘宁',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'太史慈',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'孙策',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'黄盖',to_date('19900517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'典韦',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'许褚',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'周泰',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'凌统',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'郭嘉',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'贾诩',to_date('19900517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'庞统',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'周瑜',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'戏忠',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'田丰',to_date('19900517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'沮涭',to_date('19900517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'陈宫',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'诸葛亮',to_date('19950517','yyyymmdd'),'男');insert into person values (seq_partition.nextval,'貂蝉',to_date('20000517','yyyymmdd'),'女');insert into person values (seq_partition.nextval,'蔡琰',to_date('20000517','yyyymmdd'),'女');insert into person values (seq_partition.nextval,'甄宓',to_date('20000517','yyyymmdd'),'女');insert into person values (seq_partition.nextval,'孙仁',to_date('20000517','yyyymmdd'),'女');insert into person values (seq_partition.nextval,'黄月英',to_date('20000517','yyyymmdd'),'女');insert into person values (seq_partition.nextval,'甘倩',to_date('20000517','yyyymmdd'),'女');insert into person values (seq_partition.nextval,'乔莹',to_date('20000517','yyyymmdd'),'女');insert into person values (seq_partition.nextval,'乔霜',to_date('20000517','yyyymmdd'),'女');insert into person values (seq_partition.nextval,'伏寿',to_date('20000517','yyyymmdd'),'女');--插入数据insert into person1 values (seq_partition.nextval,'吕布',to_date('19900517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'赵云',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'关羽',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'黄忠',to_date('19900517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'张辽',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'张飞',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'高顺',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'马超',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'甘宁',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'太史慈',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'孙策',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'黄盖',to_date('19900517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'典韦',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'许褚',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'周泰',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'凌统',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'郭嘉',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'贾诩',to_date('19900517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'庞统',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'周瑜',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'戏忠',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'田丰',to_date('19900517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'沮涭',to_date('19900517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'陈宫',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'诸葛亮',to_date('19950517','yyyymmdd'),'男');insert into person1 values (seq_partition.nextval,'貂蝉',to_date('20000517','yyyymmdd'),'女');insert into person1 values (seq_partition.nextval,'蔡琰',to_date('20000517','yyyymmdd'),'女');insert into person1 values (seq_partition.nextval,'甄宓',to_date('20000517','yyyymmdd'),'女');insert into person1 values (seq_partition.nextval,'孙仁',to_date('20000517','yyyymmdd'),'女');insert into person1 values (seq_partition.nextval,'黄月英',to_date('20000517','yyyymmdd'),'女');insert into person1 values (seq_partition.nextval,'甘倩',to_date('20000517','yyyymmdd'),'女');insert into person1 values (seq_partition.nextval,'乔莹',to_date('20000517','yyyymmdd'),'女');insert into person1 values (seq_partition.nextval,'乔霜',to_date('20000517','yyyymmdd'),'女');insert into person1 values (seq_partition.nextval,'伏寿',to_date('20000517','yyyymmdd'),'女');--插入数据
insert into person2 values (seq_partition.nextval,'吕布',to_date('19900517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'赵云',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'关羽',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'黄忠',to_date('19900517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'张辽',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'张飞',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'高顺',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'马超',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'甘宁',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'太史慈',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'孙策',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'黄盖',to_date('19900517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'典韦',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'许褚',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'周泰',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'凌统',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'郭嘉',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'贾诩',to_date('19900517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'庞统',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'周瑜',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'戏忠',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'田丰',to_date('19900517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'沮涭',to_date('19900517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'陈宫',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'诸葛亮',to_date('19950517','yyyymmdd'),'男');insert into person2 values (seq_partition.nextval,'貂蝉',to_date('20000517','yyyymmdd'),'女');insert into person2 values (seq_partition.nextval,'蔡琰',to_date('20000517','yyyymmdd'),'女');insert into person2 values (seq_partition.nextval,'甄宓',to_date('20000517','yyyymmdd'),'女');insert into person2 values (seq_partition.nextval,'孙仁',to_date('20000517','yyyymmdd'),'女');insert into person2 values (seq_partition.nextval,'黄月英',to_date('20000517','yyyymmdd'),'女');insert into person2 values (seq_partition.nextval,'甘倩',to_date('20000517','yyyymmdd'),'女');insert into person2 values (seq_partition.nextval,'乔莹',to_date('20000517','yyyymmdd'),'女');insert into person2 values (seq_partition.nextval,'乔霜',to_date('20000517','yyyymmdd'),'女');insert into person2 values (seq_partition.nextval,'伏寿',to_date('20000517','yyyymmdd'),'女');--插入数据
insert into person3 values (seq_partition.nextval,'吕布',to_date('19900517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'赵云',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'关羽',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'黄忠',to_date('19900517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'张辽',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'张飞',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'高顺',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'马超',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'甘宁',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'太史慈',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'孙策',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'黄盖',to_date('19900517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'典韦',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'许褚',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'周泰',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'凌统',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'郭嘉',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'贾诩',to_date('19900517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'庞统',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'周瑜',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'戏忠',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'田丰',to_date('19900517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'沮涭',to_date('19900517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'陈宫',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'诸葛亮',to_date('19950517','yyyymmdd'),'男');insert into person3 values (seq_partition.nextval,'貂蝉',to_date('20000517','yyyymmdd'),'女');insert into person3 values (seq_partition.nextval,'蔡琰',to_date('20000517','yyyymmdd'),'女');insert into person3 values (seq_partition.nextval,'甄宓',to_date('20000517','yyyymmdd'),'女');insert into person3 values (seq_partition.nextval,'孙仁',to_date('20000517','yyyymmdd'),'女');insert into person3 values (seq_partition.nextval,'黄月英',to_date('20000517','yyyymmdd'),'女');insert into person3 values (seq_partition.nextval,'甘倩',to_date('20000517','yyyymmdd'),'女');insert into person3 values (seq_partition.nextval,'乔莹',to_date('20000517','yyyymmdd'),'女');insert into person3 values (seq_partition.nextval,'乔霜',to_date('20000517','yyyymmdd'),'女');insert into person3 values (seq_partition.nextval,'伏寿',to_date('20000517','yyyymmdd'),'女');--插入数据
insert into person4 values (seq_partition.nextval,'吕布',to_date('19900517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'赵云',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'关羽',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'黄忠',to_date('19900517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'张辽',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'张飞',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'高顺',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'马超',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'甘宁',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'太史慈',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'孙策',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'黄盖',to_date('19900517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'典韦',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'许褚',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'周泰',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'凌统',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'郭嘉',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'贾诩',to_date('19900517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'庞统',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'周瑜',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'戏忠',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'田丰',to_date('19900517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'沮涭',to_date('19900517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'陈宫',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'诸葛亮',to_date('19950517','yyyymmdd'),'男');insert into person4 values (seq_partition.nextval,'貂蝉',to_date('20000517','yyyymmdd'),'女');insert into person4 values (seq_partition.nextval,'蔡琰',to_date('20000517','yyyymmdd'),'女');insert into person4 values (seq_partition.nextval,'甄宓',to_date('20000517','yyyymmdd'),'女');insert into person4 values (seq_partition.nextval,'孙仁',to_date('20000517','yyyymmdd'),'女');insert into person4 values (seq_partition.nextval,'黄月英',to_date('20000517','yyyymmdd'),'女');insert into person4 values (seq_partition.nextval,'甘倩',to_date('20000517','yyyymmdd'),'女');insert into person4 values (seq_partition.nextval,'乔莹',to_date('20000517','yyyymmdd'),'女');insert into person4 values (seq_partition.nextval,'乔霜',to_date('20000517','yyyymmdd'),'女');insert into person4 values (seq_partition.nextval,'伏寿',to_date('20000517','yyyymmdd'),'女');--插入数据
insert into person5 values (seq_partition.nextval,'吕布',to_date('19900517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'赵云',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'关羽',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'黄忠',to_date('19900517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'张辽',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'张飞',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'高顺',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'马超',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'甘宁',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'太史慈',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'孙策',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'黄盖',to_date('19900517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'典韦',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'许褚',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'周泰',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'凌统',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'郭嘉',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'贾诩',to_date('19900517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'庞统',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'周瑜',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'戏忠',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'田丰',to_date('19900517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'沮涭',to_date('19900517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'陈宫',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'诸葛亮',to_date('19950517','yyyymmdd'),'男');insert into person5 values (seq_partition.nextval,'貂蝉',to_date('20000517','yyyymmdd'),'女');insert into person5 values (seq_partition.nextval,'蔡琰',to_date('20000517','yyyymmdd'),'女');insert into person5 values (seq_partition.nextval,'甄宓',to_date('20000517','yyyymmdd'),'女');insert into person5 values (seq_partition.nextval,'孙仁',to_date('20000517','yyyymmdd'),'女');insert into person5 values (seq_partition.nextval,'黄月英',to_date('20000517','yyyymmdd'),'女');insert into person5 values (seq_partition.nextval,'甘倩',to_date('20000517','yyyymmdd'),'女');insert into person5 values (seq_partition.nextval,'乔莹',to_date('20000517','yyyymmdd'),'女');insert into person5 values (seq_partition.nextval,'乔霜',to_date('20000517','yyyymmdd'),'女');insert into person5 values (seq_partition.nextval,'伏寿',to_date('20000517','yyyymmdd'),'女');