对Oracle 应用分区表的100W条测试

最新推荐文章于 2022-09-16 16:38:45 发布

liu3098651

最新推荐文章于 2022-09-16 16:38:45 发布

阅读量194

点赞数

分类专栏： Oracle 文章标签： oracle

本文链接：https://blog.youkuaiyun.com/liu3098651/article/details/84178380

版权

Oracle 专栏收录该内容

4 篇文章

订阅专栏

本文通过在Windows XP环境下，使用Oracle 10.2数据库进行100万条数据的插入和检索测试，对比了普通表与分区表的性能差异。结果显示，分区表在检索速度上提升了约10倍。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

为验证分区表对检索数据的性能的提升，今天做了100W条数据级的检索和插入的测试。

测试环境：

1. 操作系统和硬件:windows-XP,CPU3.2 双核，3G内存，硬盘500G左右。
2. 软件环境：hibernate3,structs1,ORACLE 10.2 ,weblogic10，JDK6.0

测试表：

1.无分区表test

create table TEST
(
  STI_ID                     NUMBER(10) not null,
  STI_STUDENT                NUMBER(19),
  STI_STUDENT_NAME           VARCHAR2(20),
  STI_STUDENT_CARD_ID        CHAR(14) not null,
  STI_STUDENT_CARD_PRINT_NUM VARCHAR2(20),
  STI_COACH                  NUMBER(19),
  STI_COACH_NAME             VARCHAR2(20),
  STI_COACH_CARD_ID          CHAR(14) not null,
  STI_COACH_CARD_PRINT_NUM   VARCHAR2(20),
  STI_SCHOOL                 NUMBER(10),
  STI_SCHOOL_NAME            VARCHAR2(50),
  STI_SCHOOL_SHORT_NAME      VARCHAR2(20),
  STI_COUNTY                 CHAR(6),
  STI_COUNTY_NAME            VARCHAR2(20),
  STI_TRAIN_START_TIME       DATE not null,
  STI_TRAIN_END_TIME         DATE not null,
  STI_TOTAL_TIME             NUMBER(5) default 0,
  STI_TOTAL_MILEAGE          NUMBER(11,1) default 0,
  STI_TOTAL_MONEY            NUMBER(7,2) default 0,
  STI_IS_SIGN_OUT            NUMBER(3) not null,
  STI_APPLY_EXAM_SUBJECT     NUMBER(3),
  STI_TRAIN_SUBJECT          NUMBER(3) not null,
  STI_TRAIN_SUBJECT_NAME     VARCHAR2(50),
  STI_TERMINAL_MACHINE       CHAR(14) not null,
  STI_TERMINAL_PRINT_NUM     VARCHAR2(20),
  STI_UP_TIME                DATE default sysdate not null,
  STI_TERMINAL_DATA_ID       VARCHAR2(20),
  STI_IS_SYNCHRONIZE         NUMBER(3) default 0,
  IS_UP                      CHAR(1) default 0,
  UP_TIME                    DATE,
  UP_COUNT                   NUMBER(3) default 0,
  CS_INNER_NUM               NUMBER(10),
  CS_NAME                    VARCHAR2(50),
  STI_TRAIN_PRICE            NUMBER(5),
  IS_COUNT                   NUMBER(1) default 0,
  IS_COUNT_TIME              DATE,
  STI_IS_TO_WEB              NUMBER(1) default 0
)

2.按sti_school分区的表test1

create table TEST1
(
  STI_ID                     NUMBER(10) not null,
  STI_STUDENT                NUMBER(19),
  STI_STUDENT_NAME           VARCHAR2(20),
  STI_STUDENT_CARD_ID        CHAR(14) not null,
  STI_STUDENT_CARD_PRINT_NUM VARCHAR2(20),
  STI_COACH                  NUMBER(19),
  STI_COACH_NAME             VARCHAR2(20),
  STI_COACH_CARD_ID          CHAR(14) not null,
  STI_COACH_CARD_PRINT_NUM   VARCHAR2(20),
  STI_SCHOOL                 NUMBER(10),
  STI_SCHOOL_NAME            VARCHAR2(50),
  STI_SCHOOL_SHORT_NAME      VARCHAR2(20),
  STI_COUNTY                 CHAR(6),
  STI_COUNTY_NAME            VARCHAR2(20),
  STI_TRAIN_START_TIME       DATE not null,
  STI_TRAIN_END_TIME         DATE not null,
  STI_TOTAL_TIME             NUMBER(5) default 0,
  STI_TOTAL_MILEAGE          NUMBER(11,1) default 0,
  STI_TOTAL_MONEY            NUMBER(7,2) default 0,
  STI_IS_SIGN_OUT            NUMBER(3) not null,
  STI_APPLY_EXAM_SUBJECT     NUMBER(3),
  STI_TRAIN_SUBJECT          NUMBER(3) not null,
  STI_TRAIN_SUBJECT_NAME     VARCHAR2(50),
  STI_TERMINAL_MACHINE       CHAR(14) not null,
  STI_TERMINAL_PRINT_NUM     VARCHAR2(20),
  STI_UP_TIME                DATE default sysdate not null,
  STI_TERMINAL_DATA_ID       VARCHAR2(20),
  STI_IS_SYNCHRONIZE         NUMBER(3) default 0,
  IS_UP                      CHAR(1) default 0,
  UP_TIME                    DATE,
  UP_COUNT                   NUMBER(3) default 0,
  CS_INNER_NUM               NUMBER(10),
  CS_NAME                    VARCHAR2(50),
  STI_TRAIN_PRICE            NUMBER(5),
  IS_COUNT                   NUMBER(1) default 0,
  IS_COUNT_TIME              DATE,
  STI_IS_TO_WEB              NUMBER(1) default 0
)
 partition by range (sti_school)
 (
     partition cus_part1 values less than (33030082) ,
     partition cus_part2 values less than (33030122),
     partition cus_part3 values less than (33030142),
     partition cus_part4 values less than (33030162),
     partition cus_part5 values less than (33030242)
 )

插入数据库脚本(分两次插入)

package com.www.test.service.impl;

import java.util.Date;

import com.www.test.model.StudentTrainInfo;
import com.www.test.service.testService;
import com.www.util.CommonUtil;

public class testServiceImpl extends BaseService implements
		testService {


	public Integer test() {
		
		System.out.println("=====开始给33030121付值======"+CommonUtil.convertDateToString(new Date(), "yyyy-MM-dd hh:mm:ss"));
		for(int i=0;i<100000;i++){
			StudentTrainInfo studentTrainInfo = new StudentTrainInfo();
			studentTrainInfo.setStudentInnerNum(Long.parseLong("03010000000000"));
			studentTrainInfo.setStudentName("测试");
			studentTrainInfo.setStudentCardInnerNum("03020000000000");
			studentTrainInfo.setStudentCardPrintNum("03030000000000");
			studentTrainInfo.setCoachInnerNum(Long.parseLong("3303000000"));
			studentTrainInfo.setCoachName("测试教练");
			studentTrainInfo.setCoachCardInnerNum("03040000000000");
			studentTrainInfo.setCoachCardPrintNum("03050000000000");
			studentTrainInfo.setSchoolInnerNum(Integer.parseInt("33030121"));
			studentTrainInfo.setSchoolName("温州交通技术学校");
			studentTrainInfo.setCountyCode("330301");
			studentTrainInfo.setCountyName("市辖区");
			studentTrainInfo.setTrainStartTime(new Date());
			studentTrainInfo.setTrainEndTime(new Date());
			studentTrainInfo.setTotalTime(0);
			studentTrainInfo.setTotalMileage(0);
			studentTrainInfo.setTotalMoney(0);
			studentTrainInfo.setIsSignOut(1);
			studentTrainInfo.setApplyExamSubjectInnerNum(2);
			studentTrainInfo.setTrainSubjectInnerNum(61);
			studentTrainInfo.setTrainSubjectName("科目二");
			studentTrainInfo.setTerminalMachineInnerNum("33031100000000");
			studentTrainInfo.setTerminalMachinePrintNum("33031100000000");
			studentTrainInfo.setUploadTime(new Date());
			
			this.getStudentTrainInfoDao().create(studentTrainInfo);
			
		}
		System.out.println("======开始给33030081付值======="+CommonUtil.convertDateToString(new Date(), "yyyy-MM-dd hh:mm:ss"));
		for(int i=0;i<100000;i++){
		 	StudentTrainInfo studentTrainInfo = new StudentTrainInfo();
			studentTrainInfo.setStudentInnerNum(Long.parseLong("03010000000001"));
			studentTrainInfo.setStudentName("测试1");
			studentTrainInfo.setStudentCardInnerNum("03020000000001");
			studentTrainInfo.setStudentCardPrintNum("03030000000001");
			studentTrainInfo.setCoachInnerNum(Long.parseLong("3303000001"));
			studentTrainInfo.setCoachName("测试教练1");
			studentTrainInfo.setCoachCardInnerNum("03040000000001");
			studentTrainInfo.setCoachCardPrintNum("03050000000001");
			studentTrainInfo.setSchoolInnerNum(Integer.parseInt("33030081"));
			studentTrainInfo.setSchoolName("温州娄桥机动车驾驶学校有限公司");
			studentTrainInfo.setCountyCode("330301");
			studentTrainInfo.setCountyName("市辖区");
			studentTrainInfo.setTrainStartTime(new Date());
			studentTrainInfo.setTrainEndTime(new Date());
			studentTrainInfo.setTotalTime(0);
			studentTrainInfo.setTotalMileage(0);
			studentTrainInfo.setTotalMoney(0);
			studentTrainInfo.setIsSignOut(1);
			studentTrainInfo.setApplyExamSubjectInnerNum(2);
			studentTrainInfo.setTrainSubjectInnerNum(61);
			studentTrainInfo.setTrainSubjectName("科目二");
			studentTrainInfo.setTerminalMachineInnerNum("33031100000001");
			studentTrainInfo.setTerminalMachinePrintNum("33031100000001");
			studentTrainInfo.setUploadTime(new Date());
			this.getStudentTrainInfoDao().create(studentTrainInfo);
			
		}
		System.out.println("==================开始给33030161付值=================="+CommonUtil.convertDateToString(new Date(), "yyyy-MM-dd hh:mm:ss"));
		for(int i=0;i<100000;i++){
			StudentTrainInfo studentTrainInfo = new StudentTrainInfo();
			studentTrainInfo.setStudentInnerNum(Long.parseLong("03010000000002"));
			studentTrainInfo.setStudentName("测试2");
			studentTrainInfo.setStudentCardInnerNum("03020000000002");
			studentTrainInfo.setStudentCardPrintNum("03030000000002");
			studentTrainInfo.setCoachInnerNum(Long.parseLong("3303000002"));
			studentTrainInfo.setCoachName("测试教练2");
			studentTrainInfo.setCoachCardInnerNum("03040000000002");
			studentTrainInfo.setCoachCardPrintNum("03050000000002");
			studentTrainInfo.setSchoolInnerNum(Integer.parseInt("33030161"));
			studentTrainInfo.setSchoolName("温州公交集团机动车驾驶员培训学校");
			studentTrainInfo.setCountyCode("330301");
			studentTrainInfo.setCountyName("市辖区");
			studentTrainInfo.setTrainStartTime(new Date());
			studentTrainInfo.setTrainEndTime(new Date());
			studentTrainInfo.setTotalTime(0);
			studentTrainInfo.setTotalMileage(0);
			studentTrainInfo.setTotalMoney(0);
			studentTrainInfo.setIsSignOut(1);
			studentTrainInfo.setApplyExamSubjectInnerNum(2);
			studentTrainInfo.setTrainSubjectInnerNum(61);
			studentTrainInfo.setTrainSubjectName("科目二");
			studentTrainInfo.setTerminalMachineInnerNum("33031100000002");
			studentTrainInfo.setTerminalMachinePrintNum("33031100000002");
			studentTrainInfo.setUploadTime(new Date());
			this.getStudentTrainInfoDao().create(studentTrainInfo);
		}
		System.out.println("=======开始给33030141付值======"+CommonUtil.convertDateToString(new Date(), "yyyy-MM-dd hh:mm:ss"));
		for(int i=0;i<100000;i++){
			StudentTrainInfo studentTrainInfo = new StudentTrainInfo();
			studentTrainInfo.setStudentInnerNum(Long.parseLong("03010000000003"));
			studentTrainInfo.setStudentName("测试3");
			studentTrainInfo.setStudentCardInnerNum("03020000000003");
			studentTrainInfo.setStudentCardPrintNum("03030000000003");
			studentTrainInfo.setCoachInnerNum(Long.parseLong("3303000003"));
			studentTrainInfo.setCoachName("测试教练3");
			studentTrainInfo.setCoachCardInnerNum("03040000000003");
			studentTrainInfo.setCoachCardPrintNum("03050000000003");
			studentTrainInfo.setSchoolInnerNum(Integer.parseInt("33030141"));
			studentTrainInfo.setSchoolName("温州冶金汽车驾驶服务有限公司");
			studentTrainInfo.setCountyCode("330301");
			studentTrainInfo.setCountyName("市辖区");
			studentTrainInfo.setTrainStartTime(new Date());
			studentTrainInfo.setTrainEndTime(new Date());
			studentTrainInfo.setTotalTime(0);
			studentTrainInfo.setTotalMileage(0);
			studentTrainInfo.setTotalMoney(0);
			studentTrainInfo.setIsSignOut(1);
			studentTrainInfo.setApplyExamSubjectInnerNum(2);
			studentTrainInfo.setTrainSubjectInnerNum(61);
			studentTrainInfo.setTrainSubjectName("科目二");
			studentTrainInfo.setTerminalMachineInnerNum("33031100000003");
			studentTrainInfo.setTerminalMachinePrintNum("33031100000003");
			studentTrainInfo.setUploadTime(new Date());
			this.getStudentTrainInfoDao().create(studentTrainInfo);
		}
		System.out.println("==================开始给33030241付值=================="+CommonUtil.convertDateToString(new Date(), "yyyy-MM-dd hh:mm:ss"));
		for(int i=0;i<100000;i++){
			StudentTrainInfo studentTrainInfo = new StudentTrainInfo();
			studentTrainInfo.setStudentInnerNum(Long.parseLong("03010000000004"));
			studentTrainInfo.setStudentName("测试4");
			studentTrainInfo.setStudentCardInnerNum("03020000000004");
			studentTrainInfo.setStudentCardPrintNum("03030000000004");
			studentTrainInfo.setCoachInnerNum(Long.parseLong("3303000004"));
			studentTrainInfo.setCoachName("测试教练4");
			studentTrainInfo.setCoachCardInnerNum("03040000000004");
			studentTrainInfo.setCoachCardPrintNum("03050000000004");
			studentTrainInfo.setSchoolInnerNum(Integer.parseInt("33030241"));
			studentTrainInfo.setSchoolName("浙江交通技师学院");
			studentTrainInfo.setCountyCode("330301");
			studentTrainInfo.setCountyName("市辖区");
			studentTrainInfo.setTrainStartTime(new Date());
			studentTrainInfo.setTrainEndTime(new Date());
			studentTrainInfo.setTotalTime(0);
			studentTrainInfo.setTotalMileage(0);
			studentTrainInfo.setTotalMoney(0);
			studentTrainInfo.setIsSignOut(1);
			studentTrainInfo.setApplyExamSubjectInnerNum(2);
			studentTrainInfo.setTrainSubjectInnerNum(61);
			studentTrainInfo.setTrainSubjectName("科目二");
			studentTrainInfo.setTerminalMachineInnerNum("33031100000004");
			studentTrainInfo.setTerminalMachinePrintNum("33031100000004");
			studentTrainInfo.setUploadTime(new Date());
			this.getStudentTrainInfoDao().create(studentTrainInfo);
		}
		System.out.println("========提交完成======="+CommonUtil.convertDateToString(new Date(), "yyyy-MM-dd hh:mm:ss"));

		return null;
	}

}

测试结果：
1.插入数据结果：50W条数据插入时间为2分30秒，其中“==提交完成=”前花费1分30秒，后（hibernate3向数据库提交）时间为1分钟。

2.test和test1单条记录检索时间对比。其中partition(CUS_PART3)存放的是sti_school=33030141的数据，经实测，结果无分区的有分区的结果相差时间为10倍，分区表的检索性能提升显著。

select *  from test1 partition(CUS_PART3)   where  STI_ID=1709850  --首次检索时间：0.234秒

SELECT * FROM test t where t.sti_id=1709850 and t.sti_school=33030141 --首次检索时间：2.454秒

3.对test1查询语句中带partition(CUS_PART3)，与不带的性能测试。经实测，说明SQL不带partition(CUS_PART3)，用sti_school=33030141的检索速度差不多，用sti_school查询也走分区查询。

select *  from test1 partition(CUS_PART3)   where  STI_ID=1709850  --多次检索后时间：0.062秒

SELECT * FROM test1 t where t.sti_id=1709850 and t.sti_school=33030141 --多次检索后时间：0.063秒

测度出现的问题
1.发现一次性循环61W次以上时会出现weblogic内存溢出的问题。weblogic内存配置（-Xms512m -Xmx512m -XX:CompileThreshold=8000 -XX:PermSize=512m -XX:MaxPermSize=1024m -Xverify:none -da）；内存配置项，其他三个512内存项配为1024M,会出现weblogic启动不起来的问题。不知为何？

2.当表分区空间没有定义自增长时，对超过10W条记录的插入会出现：oracle 关于无法通过128 表无法扩展相关错误提示，解决方法：修改test表所有的表空间文件为自增长。