- 浏览: 41401 次
文章分类
最新评论
为验证分区表对检索数据的性能的提升,今天做了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表所有的表空间文件为自增长。
相关推荐
Oracle分区表详解 大家可以参考下 网上找的资料共享一下
oracle分区表总结oracle分区表总结oracle分oracle分区表总结区表总结oracle分区表总结
Oracle数据库分区表操作方法Oracle数据库分区表操作方法
主要介绍了oracle普通表转化为分区表的方法,官方给出了四种操作方法,本文主要对第四种方法进行详细分析,需要的朋友可以参考下。
Hash分区是Oracle实现表分区的三种基本分区方式之一。对于那些无法有效划分分区范围的大表,或者出于某些特殊考虑的设计,需要使用Hash分区,下面介绍使用方法
oracle表分区详解
oracle 分区表管理oracle 分区表管理oracle 分区表管理oracle 分区表管理oracle 分区表管理
在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。 以system身份登陆数据库,查看 v$option视图,如果其中Partition为TRUE,则支持分区 功能;否则不支持。Partition有基于范围、哈希、综...
导入导出 Oracle 分区表数据
Oracle 表分区技术详解: 1.表空间及分区表的概念 2.表分区的具体作用 3.表分区的优缺点 4.表分区的几种类型及操作方法 5.对表分区的维护性操作.
ORACLE分区表操作大全 ORACLE分区表操作大全
公司内部培训ORACLE分区表使用的文档
oracle expdp impdp 分区表重映射导出导入 数据迁移方案,以SI01用户为例子,将用户分区表导出后,将分区表重映射到新的表空间,完成数据迁移和检查。照方案例子按步去做,一定能成功。
ORACLE 分区表 分区索引 索引分区 实例讲解
Oracle 分区表全揭秘 ,非常详细,oracle dba可以看看
分区表类型介绍+各种类型分区表的创建举例
虽然存储介质和数据处理技术的发展也很快,但是仍然不能满足用户的需求,为了使用户的大量的数据在读写操作和查询中速度更快,Oracle提供了对表和索引进行分区的技术,以改善大型应用系统的性能。
文件是本人oracle10g分区表自动按时间创建、删除分区的存储过程,测试代码,通过job调用存储过程,每天午夜12点运行一次。妥妥!跟大家分享下!
本文档详细的讲解了在oracle中如何常见分区表以及遇到的几种问题,希望对大家用帮助
ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区ORACLE分区