Oracle表分区
一、表分区的概念：
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
二、表分区的优点以及分类
表分区的优点：
01.改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
02.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
03.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
04.均衡I/O：可以把不同的分区映射到不同的磁盘以平衡I/O，改善整个系统性能。
表分区的分类：
1.范围分区 （range分区）就是根据数据库表中某一个字段值的范围来划分分区
0.1每一个分区都必须有一个values less than 子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
0.2所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
0.3在最高的分区中，maxvalue被定义。maxvalue代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的values less than的值，同时包括控制null。
--表分区 不能在Normal中 创建 切换到sysdba连接 或者使用system管理员用户名 create tablespace partition1 datafile 'E:\oraclespace\partition1.dbf' size 20m autoextend on next 20 maxsize unlimited; create tablespace partition2 datafile 'E:\oraclespace\partition2.dbf' size 20m autoextend on next 20 maxsize unlimited; create tablespace partition3 datafile 'E:\oraclespace\partition3.dbf' size 20m autoextend on next 20 maxsize unlimited;
--查询所有分区的信息 select * from all_tab_partitions;
创建表的时候创建范围分区
create table DEPT ( deptno NUMBER(2) not null, dname VARCHAR2(20), loc VARCHAR2(13) ); alter table DEPT add constraint PK_DEPT primary key (DEPTNO); create table TEACHER ( tno NUMBER(4) not null, tname VARCHAR2(30) not null, tid CHAR(18), gendar CHAR(3), birthdate DATE, job VARCHAR2(20), hiredate DATE, sal NUMBER(7,2), deptno NUMBER(2), mgrno NUMBER(4), comm NUMBER(7,2) )
//创建范围分区 上面TEACHER表不能以分号结尾 partition by range(tno) ( partition part1 values less than (1030) tablespace partition1， partition part2 values less than (1070) tablespace partition2， --如果新增的数据TNO 超过1070都放在part3中 partition part3 values less than (Maxvalue) tablespace partition3 );
添加测试数据
insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1002, '赵辉', '110101760609001 ', '男', to_date('09-06-1976', 'dd-mm-yyyy'), '考试专员', to_date('23-05-2006 02:40:40', 'dd-mm-yyyy hh24:mi:ss'), 29370.95, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1003, '杨利乐', '110101198705256045', '女', to_date('25-05-1987', 'dd-mm-yyyy'), '考试专员', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 7134.32, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1004, '王益辉', '110101199108154770', '男', to_date('15-08-1991', 'dd-mm-yyyy'), '营销专员', to_date('16-01-2012 03:12:43', 'dd-mm-yyyy hh24:mi:ss'), 30737.36, 20, null, 2300.00); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1006, '秦璐璐', '110101199004153068', '女', to_date('14-04-1990', 'dd-mm-yyyy'), '运维工程师', to_date('20-06-2012 00:47:09', 'dd-mm-yyyy hh24:mi:ss'), 9013.25, 10, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1007, '梁改焕', '110101196911206548', '女', to_date('20-11-1969', 'dd-mm-yyyy'), '讲师', to_date('13-06-2006 23:07:02', 'dd-mm-yyyy hh24:mi:ss'), 8664.16, 50, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1008, '冯爱仙', '110101196804021521', '女', to_date('02-04-1968', 'dd-mm-yyyy'), '讲师', to_date('06-06-2006 13:41:50', 'dd-mm-yyyy hh24:mi:ss'), 29936.29, 30, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1009, '秦亚杰', '110101196312163531', '男', to_date('16-12-1963', 'dd-mm-yyyy'), '岗位分析师', to_date('13-07-2005 06:32:30', 'dd-mm-yyyy hh24:mi:ss'), 24991.44, 30, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1010, '刘浩星', '110101198806283516', '男', to_date('28-06-1988', 'dd-mm-yyyy'), '考试专员', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 22645.92, null, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1011, '秦春叶', '110101197103033540', '女', to_date('03-03-1971', 'dd-mm-yyyy'), '网络营销师', to_date('07-07-2007 16:02:04', 'dd-mm-yyyy hh24:mi:ss'), 31653.23, 50, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1012, '陈水花', '110101192811043026', '女', to_date('04-11-1928', 'dd-mm-yyyy'), '考试专员', to_date('29-01-2005 23:21:59', 'dd-mm-yyyy hh24:mi:ss'), 29773.37, 20, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1013, '马付妮', '110101195806133522', '女', to_date('13-06-1958', 'dd-mm-yyyy'), '研发', to_date('17-11-2004 08:54:04', 'dd-mm-yyyy hh24:mi:ss'), 17041.60, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1015, '刘得安', '110101195210083518', '男', to_date('08-10-1952', 'dd-mm-yyyy'), '考试专员', to_date('13-03-2005 23:25:37', 'dd-mm-yyyy hh24:mi:ss'), 30356.99, 20, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1016, '高利芹', '110101198307276020', '女', to_date('27-07-1983', 'dd-mm-yyyy'), '岗位分析师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 25751.36, null, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1017, '刘志刚', '110101197806164493', '男', to_date('16-06-1978', 'dd-mm-yyyy'), '运维工程师', to_date('10-05-2008 13:46:33', 'dd-mm-yyyy hh24:mi:ss'), 22344.73, 20, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1018, '高心立', '110101194102076011', '男', to_date('07-02-1941', 'dd-mm-yyyy'), '网络营销师', to_date('03-03-2005 15:17:07', 'dd-mm-yyyy hh24:mi:ss'), 19981.02, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1019, '徐丽', '11010119790809354X', '男', to_date('09-08-1979', 'dd-mm-yyyy'), '研发', to_date('09-12-2009 18:37:22', 'dd-mm-yyyy hh24:mi:ss'), 10950.33, 30, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1021, '刘志红', '110101196504284452', '男', to_date('28-04-1965', 'dd-mm-yyyy'), '岗位分析师', to_date('20-09-2011 10:51:33', 'dd-mm-yyyy hh24:mi:ss'), 7122.60, 20, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1022, '孔鹏飞', '110101198603304014', '男', to_date('30-03-1986', 'dd-mm-yyyy'), '人力资源师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 7045.28, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1023, '李建坡', '110101731107301 ', '男', to_date('07-11-1973', 'dd-mm-yyyy'), '网络营销师', to_date('04-07-2009 06:28:20', 'dd-mm-yyyy hh24:mi:ss'), 21133.91, 50, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1024, '冯爱敏', '110101196907283840', '女', to_date('18-07-1969', 'dd-mm-yyyy'), '营销专员', to_date('12-09-2005 22:41:06', 'dd-mm-yyyy hh24:mi:ss'), 3360.91, 20, null, 50000.00); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1025, '薛栓成', '110101400710051 ', '男', to_date('10-07-1940', 'dd-mm-yyyy'), '网络营销师', to_date('03-09-2007 14:55:06', 'dd-mm-yyyy hh24:mi:ss'), 12136.65, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1026, '牛伟伟', '11010119851120602X', '女', to_date('20-11-1985', 'dd-mm-yyyy'), '岗位分析师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 6622.66, 30, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1027, '郭香云', '110101196010154525', '女', to_date('15-10-1960', 'dd-mm-yyyy'), '网络营销师', to_date('03-05-2005 13:01:22', 'dd-mm-yyyy hh24:mi:ss'), 26436.18, 50, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1028, '朱花枝', '110101195605154028', '女', to_date('15-05-1956', 'dd-mm-yyyy'), '研发', to_date('11-09-2007 07:41:01', 'dd-mm-yyyy hh24:mi:ss'), 10095.22, 50, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1029, '李岗', '110101196505163095', '男', to_date('16-05-1965', 'dd-mm-yyyy'), '岗位分析师', to_date('09-11-2007 17:26:38', 'dd-mm-yyyy hh24:mi:ss'), 23928.84, 30, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1030, '李军会', '110101197706152524', '男', to_date('15-06-1977', 'dd-mm-yyyy'), '营销专员', to_date('12-05-2007 19:48:21', 'dd-mm-yyyy hh24:mi:ss'), 3492.19, 20, null, 25000.00); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1031, '孙龙举', '110101198908306037', '男', to_date('30-08-1989', 'dd-mm-yyyy'), '运维工程师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 4323.84, 30, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1032, '韩朝旭', '110101199003270513', '男', to_date('27-03-1900', 'dd-mm-yyyy'), '人力资源师', to_date('11-09-2010 17:54:30', 'dd-mm-yyyy hh24:mi:ss'), 13740.92, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1033, '孙少光', '110101198809274535', '男', to_date('27-09-1988', 'dd-mm-yyyy'), '网络营销师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 19778.62, 50, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1035, '邓香敏', '110101196607163109', '女', to_date('16-07-1966', 'dd-mm-yyyy'), '人力资源师', to_date('12-04-2007 17:47:14', 'dd-mm-yyyy hh24:mi:ss'), 29463.38, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1037, '霍振方', '110101198411070014', '男', to_date('07-11-1984', 'dd-mm-yyyy'), '讲师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 23731.31, 40, 1087, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1038, '许双鸽', '110101781217004 ', '女', to_date('17-12-1978', 'dd-mm-yyyy'), '网络营销师', to_date('05-09-2007 18:45:00', 'dd-mm-yyyy hh24:mi:ss'), 16512.52, 30, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1040, '张春红', '110101197806176089', '女', to_date('17-06-1978', 'dd-mm-yyyy'), '运维工程师', to_date('21-11-2004 06:52:30', 'dd-mm-yyyy hh24:mi:ss'), 31728.83, 10, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1041, '卢玉莲', '110101510606004 ', '女', to_date('06-06-1951', 'dd-mm-yyyy'), '人力资源师', to_date('30-03-2009 17:05:45', 'dd-mm-yyyy hh24:mi:ss'), 17596.43, 40, null, nulinsert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1042, '李军会', '110101197706152524', '男', to_date('15-06-1977', 'dd-mm-yyyy'), '讲师', to_date('13-06-2008 03:24:47', 'dd-mm-yyyy hh24:mi:ss'), 8443.59, 50, 1008, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1043, '朱水娃', '110101195210033512', '男', to_date('03-10-1952', 'dd-mm-yyyy'), '讲师', to_date('11-10-2009 03:40:53', 'dd-mm-yyyy hh24:mi:ss'), 9123.09, 30, 1008, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1045, '高大荣', '110101195504073528', '女', to_date('07-04-1955', 'dd-mm-yyyy'), '考试专员', to_date('07-12-2005 14:56:05', 'dd-mm-yyyy hh24:mi:ss'), 4000.00, 10, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1046, '秦付根', '110101195301034035', '男', to_date('03-01-1953', 'dd-mm-yyyy'), '讲师', to_date('10-12-2004 22:51:51', 'dd-mm-yyyy hh24:mi:ss'), 16420.33, 30, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1047, '樊增妮', '110101410715352 ', '女', to_date('15-07-1941', 'dd-mm-yyyy'), '网络营销师', to_date('02-12-2005 14:41:54', 'dd-mm-yyyy hh24:mi:ss'), 13316.01, 20, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1048, '宋素敏', '11010119720111458X', '女', to_date('11-02-1972', 'dd-mm-yyyy'), '岗位分析师', to_date('26-09-2004 12:15:57', 'dd-mm-yyyy hh24:mi:ss'), 2142.92, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1049, '李记', '110101194803256012', '男', to_date('25-03-1948', 'dd-mm-yyyy'), '网络营销师', to_date('23-03-2006 19:12:45', 'dd-mm-yyyy hh24:mi:ss'), 13866.09, 50, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1050, '秦单风', '110101198911153625', '女', to_date('15-11-1989', 'dd-mm-yyyy'), '考试专员', to_date('21-10-2012 05:31:34', 'dd-mm-yyyy hh24:mi:ss'), 21419.38, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1051, '秦占豪', '110101198509034011', '男', to_date('03-09-1985', 'dd-mm-yyyy'), '讲师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 31564.79, 40, 1087, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1054, '张利娟', '110101197901173062', '女', to_date('17-01-1979', 'dd-mm-yyyy'), '网络营销师', to_date('08-05-2011 22:37:13', 'dd-mm-yyyy hh24:mi:ss'), 7419.99, 50, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1055, '陈向琼', '11010119871010354X', '男', to_date('10-10-1987', 'dd-mm-yyyy'), '讲师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 4000.00, 10, 1087, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1056, '杨晓磊', '110101199105106079', '男', to_date('10-05-1991', 'dd-mm-yyyy'), '研发', to_date('05-04-2012 22:16:05', 'dd-mm-yyyy hh24:mi:ss'), 20812.41, 30, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1057, '黄芳菊', '110101196611133520', '女', to_date('13-11-1966', 'dd-mm-yyyy'), '网络营销师', to_date('04-07-2008 17:36:20', 'dd-mm-yyyy hh24:mi:ss'), 12711.46, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1058, '李红勤', '110101197209084047', '女', to_date('08-09-1972', 'dd-mm-yyyy'), '运维工程师', to_date('01-03-2007 05:57:34', 'dd-mm-yyyy hh24:mi:ss'), 21119.95, 30, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1060, '薛栓成', '110101400710051 ', '男', to_date('10-07-1940', 'dd-mm-yyyy'), '考试专员', to_date('22-08-2007 08:06:47', 'dd-mm-yyyy hh24:mi:ss'), 24544.66, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1061, '高发娃', '110101192905226032', '男', to_date('22-05-1929', 'dd-mm-yyyy'), '运维工程师', to_date('12-12-2004 22:49:04', 'dd-mm-yyyy hh24:mi:ss'), 23458.34, 10, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1062, '李新昌', '110101196403106013', '男', to_date('10-03-1964', 'dd-mm-yyyy'), '研发', to_date('01-04-2011 18:00:33', 'dd-mm-yyyy hh24:mi:ss'), 18605.46, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1065, '赵永刚', '110101197507310014', '男', to_date('31-07-1975', 'dd-mm-yyyy'), '岗位分析师', to_date('05-12-2009 15:00:29', 'dd-mm-yyyy hh24:mi:ss'), 3155.96, 20, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1067, '冯世伟', '110101198811154532', '男', to_date('15-11-1988', 'dd-mm-yyyy'), '讲师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 17854.88, 40, 1087, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1068, '张延付', '110101197005233514', '男', to_date('23-05-1970', 'dd-mm-yyyy'), '岗位分析师', to_date('21-05-2009 10:16:16', 'dd-mm-yyyy hh24:mi:ss'), 24294.43, 30, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1070, '陈粉', '110101195801206040', '女', to_date('20-01-1958', 'dd-mm-yyyy'), '营销专员', to_date('29-06-2011 21:27:04', 'dd-mm-yyyy hh24:mi:ss'), 17708.56, 20, null, 600.00); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1071, '张根祥', '110101194412126513', '男', to_date('12-12-1944', 'dd-mm-yyyy'), '岗位分析师', to_date('06-10-2006 12:14:34', 'dd-mm-yyyy hh24:mi:ss'), 8802.52, null, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1074, '李秋彩', '110101197411186020', '女', to_date('18-11-1974', 'dd-mm-yyyy'), '考试专员', to_date('09-04-2005 01:11:29', 'dd-mm-yyyy hh24:mi:ss'), 19315.97, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1075, '李建玲', '110101198110164423', '女', to_date('16-10-1981', 'dd-mm-yyyy'), '运维工程师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 9023.25, 50, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1076, '张仙', '110101530824316 ', '女', to_date('24-08-1953', 'dd-mm-yyyy'), '考试专员', to_date('09-05-2011 11:15:29', 'dd-mm-yyyy hh24:mi:ss'), 8654.83, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1077, '朱金典', '110101196810023531', '男', to_date('02-10-1968', 'dd-mm-yyyy'), '营销专员', to_date('11-01-2008 11:13:31', 'dd-mm-yyyy hh24:mi:ss'), 6687.97, 20, null, 2700.00); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1079, '孙玉环', '110101195006056047', '女', to_date('05-06-1950', 'dd-mm-yyyy'), '考试专员', to_date('17-11-2008 12:48:12', 'dd-mm-yyyy hh24:mi:ss'), 7490.24, 20, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1080, '邢中景', '110101198208284519', '男', to_date('28-08-1982', 'dd-mm-yyyy'), '运维工程师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 24990.57, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1081, '韩巧真', '110101198404190026', '女', to_date('19-04-1984', 'dd-mm-yyyy'), '人力资源师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 9880.26, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1084, '杜巧琴', '110101196201036089', '女', to_date('03-01-1962', 'dd-mm-yyyy'), '岗位分析师', to_date('13-01-2011 16:11:23', 'dd-mm-yyyy hh24:mi:ss'), 21655.36, 20, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1085, '席妞娃', '110101194601154026', '女', to_date('15-01-1946', 'dd-mm-yyyy'), '运维工程师', to_date('15-08-2008 06:36:11', 'dd-mm-yyyy hh24:mi:ss'), 24615.12, 20, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1087, '薛素粉', '110101196701044528', '女', to_date('04-01-1967', 'dd-mm-yyyy'), '讲师', to_date('10-09-2007 06:07:14', 'dd-mm-yyyy hh24:mi:ss'), 12357.56, null, 1008, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1088, '王雪英', '110101193311264521', '女', to_date('26-11-1933', 'dd-mm-yyyy'), '岗位分析师', to_date('18-02-2006 23:37:15', 'dd-mm-yyyy hh24:mi:ss'), 23670.53, 10, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1089, '刘秀菊', '110101530414306 ', '女', to_date('14-04-1953', 'dd-mm-yyyy'), '研发', to_date('11-09-2004 18:17:49', 'dd-mm-yyyy hh24:mi:ss'), 22608.20, null, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1090, '刘芳芳', '110101198912216025', '女', to_date('21-12-1989', 'dd-mm-yyyy'), '网络营销师', to_date('10-10-2012 00:24:11', 'dd-mm-yyyy hh24:mi:ss'), 9749.45, 30, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1091, '郝方方', '110101198605165580', '女', to_date('16-05-1986', 'dd-mm-yyyy'), '运维工程师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 5944.72, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1092, '赵利萍', '110101196712063564', '女', to_date('06-12-1967', 'dd-mm-yyyy'), '岗位分析师', to_date('01-08-2006 17:26:08', 'dd-mm-yyyy hh24:mi:ss'), 4575.09, 30, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1093, '杨联星', '110101195312284013', '男', to_date('28-12-1953', 'dd-mm-yyyy'), '考试专员', to_date('27-10-2009 08:41:47', 'dd-mm-yyyy hh24:mi:ss'), 24545.93, 40, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1094, '冯焕', '110101194205093524', '女', to_date('09-05-1942', 'dd-mm-yyyy'), '运维工程师', to_date('27-09-2010 18:25:55', 'dd-mm-yyyy hh24:mi:ss'), 21391.42, 50, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1096, '杨晓雨', '110101198910070043', '女', to_date('07-10-1989', 'dd-mm-yyyy'), '营销专员', to_date('07-08-2013 05:49:14', 'dd-mm-yyyy hh24:mi:ss'), 4161.31, 20, null, 2322.00); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1097, '柴帅友', '110101198510254038', '男', to_date('25-10-1985', 'dd-mm-yyyy'), '网络营销师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 7805.94, 50, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1098, '韩国强', '110101621016003 ', '男', to_date('16-10-1962', 'dd-mm-yyyy'), '研发', to_date('01-09-2007 12:30:52', 'dd-mm-yyyy hh24:mi:ss'), 13755.87, null, null, null); insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM) values (1099, '王参妮', '11010119490831352X', '女', to_date('31-08-1949', 'dd-mm-yyyy'), '岗位分析师', to_date('11-10-2005 13:17:18', 'dd-mm-yyyy hh24:mi:ss'), 26627.94, 30, null, null); commit;
--查询teacher表中所有数据 select * from teacher
分别查看3个分区中的数据
-- 查询指定表的分区 select * from teacher partition(part1) select * from teacher partition(part2) select * from teacher partition(part3)
共77条测试数据 根据条件分到三个分区中 范围分区
2.列表分区（list分区）特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。创建一个按字段数据列表固定可枚举值分区的表。插入记录分区字段的值必须在列表中，否则不能被插入。
比如性别：男 女
在范围分区基础上修改 先执行两条删除语句将表删除
--真正删除 Drop table teacher purge; --清空回收站中指定的表 Purge table teacher;
创建表的时候创建范围分区
create table DEPT ( deptno NUMBER(2) not null, dname VARCHAR2(20), loc VARCHAR2(13) ); alter table DEPT add constraint PK_DEPT primary key (DEPTNO); create table TEACHER ( tno NUMBER(4) not null, tname VARCHAR2(30) not null, tid CHAR(18), gendar CHAR(3), birthdate DATE, job VARCHAR2(20), hiredate DATE, sal NUMBER(7,2), deptno NUMBER(2), mgrno NUMBER(4), comm NUMBER(7,2) ) partition by list(gendar) ( partition part1 values ('男') tablespace partition1， partition part2 values ('女') tablespace partition2 );
表是新生成的 需要从新添加测试数据 这里还用77条数据为例
-- 查询指定表的分区 select * from teacher partition(part1) select * from teacher partition(part2)
共77条测试数据 根据条件分到二个分区中 列表分区
3.散列分区（Hash分区）
hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。
create table DEPT ( deptno NUMBER(2) not null, dname VARCHAR2(20), loc VARCHAR2(13) ); alter table DEPT add constraint PK_DEPT primary key (DEPTNO); create table TEACHER ( tno NUMBER(4) not null, tname VARCHAR2(30) not null, tid CHAR(18), gendar CHAR(3), birthdate DATE, job VARCHAR2(20), hiredate DATE, sal NUMBER(7,2), deptno NUMBER(2), mgrno NUMBER(4), comm NUMBER(7,2) ) partition by hash(tno，tname) ( partition part1 tablespace partition1， partition part2 tablespace partition2 );
表是新生成的 需要从新添加测试数据 这里还用77条数据为例
-- 查询指定表的分区 select * from teacher partition(part1) select * from teacher partition(part2)
共77条测试数据 根据条件分到二个分区中 hash分区
4.复合分区(分为两种：1.range-list范围-列表分区，2.range-hash范围-hash分区)
注意点：列表分区不支持多列，但是范围分区和hash分区支持多列。
01.range-list（范围-列表分区）
基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。
创建表的时候创建范围-列表分区
create table DEPT ( deptno NUMBER(2) not null, dname VARCHAR2(20), loc VARCHAR2(13) ); alter table DEPT add constraint PK_DEPT primary key (DEPTNO); create table TEACHER ( tno NUMBER(4) not null, tname VARCHAR2(30) not null, tid CHAR(18), gendar CHAR(3), birthdate DATE, job VARCHAR2(20), hiredate DATE, sal NUMBER(7,2), deptno NUMBER(2), mgrno NUMBER(4), comm NUMBER(7,2) ) partition by range (tno) subpartition by list(gendar) ( partition part1 values less than(1040) tablespace partition1 ( subpartition part1sub1 values('男') tablespace partition1, subpartition part1sub2 values('女') tablespace partition1 ), partition part2 values less than(1070) tablespace partition2 ( subpartition part2sub1 values('男') tablespace partition2, subpartition part2sub2 values('女') tablespace partition2 ), partition part3 values less than(maxvalue) tablespace partition3 ( subpartition part3sub1 values('男') tablespace partition3, subpartition part3sub2 values('女') tablespace partition3 ) );
表是新生成的 需要从新添加测试数据 这里仅以TNO 1040以下 GENDAR男、女作为举例
--查询指定表中的子分区 select * from teacher subpartition(part1sub1) select * from teacher subpartition(part1sub2)
02.range-hash(范围-hash分区)
创建表的时候创建范围-hash分区
create table DEPT ( deptno NUMBER(2) not null, dname VARCHAR2(20), loc VARCHAR2(13) ); alter table DEPT add constraint PK_DEPT primary key (DEPTNO); create table TEACHER ( tno NUMBER(4) not null, tname VARCHAR2(30) not null, tid CHAR(18), gendar CHAR(3), birthdate DATE, job VARCHAR2(20), hiredate DATE, sal NUMBER(7,2), deptno NUMBER(2), mgrno NUMBER(4), comm NUMBER(7,2) ) partition by range (tno) subpartition by hash(tname) ( partition part1 values less than(1040) tablespace partition1 ( subpartition part1sub1 tablespace partition1, subpartition part1sub2 tablespace partition1 ), partition part2 values less than(1070) tablespace partition2 ( subpartition part2sub1 tablespace partition2, subpartition part2sub2 tablespace partition2 ), partition part3 values less than(maxvalue) tablespace partition3 ( subpartition part3sub1 tablespace partition3, subpartition part3sub2 tablespace partition3 ) );
表是新生成的 需要从新添加测试数据 这里仅以TNO 1040-1070 TNO1070以上 TNAME 作为条件分区
--查询指定表中的子分区
select * from teacher subpartition(part2sub1)
select * from teacher subpartition(part3sub2)
查询、删除sql语句
--查询teacher表中所有数据 select * from teacher -- 查询指定表的分区 select * from teacher partition(part1) select * from teacher partition(part2) select * from teacher partition(part3) --查询所有分区的信息 select * from all_tab_partitions; --删除表分区包含数据 Alter table teacher drop partition part1 --删除表分区包含数据 但是表分区还在 Alter table teacher truncate partition part2 --真正删除 Drop table teacher purge; --清空回收站中指定的表 Purge table teacher; --查询指定表中的子分区 select * from teacher subpartition(part1sub1) select * from teacher subpartition(part1sub2)
