万以下小表做性能优化没有多大意义，因此我需要创建大表；
创建大表有三种方法，一种是insert into table selec..connect by.的方式，它最快但是数据要么是连续值，要么是随机值或是系统值，并不好用，而且总量上受到限制；另一种方法是用程序，借助Oracle的批量插值语法插入数据，它好在数据可以用程序掌控，总量也没有限制，缺点是速度慢；还有一种方法是有一张大表，把它的数据倒腾进来，语法是 insert into newtable select * from oldtable，这种方法暂时不在我的考虑之列。
为了节约时间，我采用了两种结合的办法，即用第一种方式先大量建立数据，再用程序修改其中一部分。
下面进入正题：
百万级别的表建表语句是这样的：
CREATE TABLE bigtable ( id NUMBER not null primary key, name NVARCHAR2(60) not null, score NUMBER(4,0) NOT NULL, createtime TIMESTAMP (6) not null )
给它塞入百万数据可以这样做：
Insert into bigtable select rownum,dbms_random.string('*',dbms_random.value(6,20)),dbms_random.value(0,20),sysdate from dual connect by level<=1000000 order by dbms_random.random
使用上面这个方法，在我的T440p机器上实验，一次性创建的记录数大约在两百万到三百万之间，再多就会报“
第 1 行出现错误:
ORA-30009: CONNECT BY 操作内存不足
”看来机器性能在制约我的研究。
看看它的运行数据如何：
已创建1000000行。 已用时间: 00: 00: 39.87
近四十秒创建百万数据，还不错。
十万级别的表结构如下：
create table smalltable ( id NUMBER not null primary key, name NVARCHAR2(60) not null, createtime TIMESTAMP (6) not null )
同样的方式给它塞入数据：
Insert into smalltable select rownum,dbms_random.string('*',dbms_random.value(6,20)),sysdate from dual connect by level<=100000 order by dbms_random.random
发现4秒不到就搞定了：
已创建100000行。 已用时间: 00: 00: 03.71
当然这样的数据还不够，于是下面的程序登场了，它的作用是将某表中百分之一记录的name字段改写成设定数组中的随机值：
package com.ufo; public class DBParam { public final static String Driver = "oracle.jdbc.driver.OracleDriver"; public final static String DbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; public final static String User = "ufo"; public final static String Pswd = "1234"; }
package com.ufo.bigsmall; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.DecimalFormat; import java.util.Random; import java.util.Set; import java.util.TreeSet; import com.ufo.DBParam; public class RecordChanger { public boolean changeOnePencent(String table) { Connection conn = null; Statement stmt = null; try{ Class.forName(DBParam.Driver).newInstance(); conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd); stmt = conn.createStatement(); long startMs = System.currentTimeMillis(); int totalCount=fetchExistCount(table,stmt); System.out.println("There are "+toEastNumFormat(totalCount)+" records in the table:'"+table+"'."); int changeCount=totalCount/100; System.out.println("There are "+toEastNumFormat(changeCount)+" records should be changed."); Set<Integer> idSet=fetchIdSet(totalCount,changeCount,table,stmt); System.out.println("There are "+toEastNumFormat(idSet.size())+" records in idSet."); int changed=updateRecords(idSet,table,stmt); System.out.println("There are "+toEastNumFormat(changed)+" records have been changed."); long endMs = System.currentTimeMillis(); System.out.println("It takes "+ms2DHMS(startMs,endMs)+" to update 1% records of table:'"+table+"'."); } catch (Exception e) { e.printStackTrace(); } finally { try { stmt.close(); conn.close(); } catch (SQLException e) { System.out.print("Can't close stmt/conn because of " + e.getMessage()); } } return false; } private int updateRecords(Set<Integer> idSet,String tableName,Statement stmt) throws SQLException{ int updated=0; for(int id:idSet) { String sql="update "+tableName+" set name='"+getRNDName()+"' where id='"+id+"' "; updated+= stmt.executeUpdate(sql); } return updated; } private String getRNDName() { String[] arr= {"Andy","Bill","Cindy","张三","张飞","张好古","李四","王五","赵六","孙七","钱八","岳飞","关羽","刘备","曹操","张辽","虚竹","王语嫣"}; int index=getRandom(0,arr.length); return arr[index]; } // fetch a set of id which should be changed private Set<Integer> fetchIdSet(int totalCount,int changeCount,String tableName,Statement stmt) throws SQLException{ Set<Integer> idSet=new TreeSet<Integer>(); while(idSet.size()<changeCount) { int id=getRandom(0,totalCount); if(idSet.contains(id)==false && isIdExist(id,tableName,stmt)) { idSet.add(id); } } return idSet; } private boolean isIdExist(int id,String tableName,Statement stmt) throws SQLException{ String sql="select count(*) as cnt from "+tableName+" where id='"+id+"' "; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int cnt = rs.getInt("cnt"); return cnt==1; } rs.close(); return false; } // get a random num between min and max private static int getRandom(int min, int max){ Random random = new Random(); int s = random.nextInt(max) % (max - min + 1) + min; return s; } // fetch exist record count of a table private int fetchExistCount(String tableName,Statement stmt) throws SQLException{ String sql="select count(*) as cnt from "+tableName+""; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int cnt = rs.getInt("cnt"); return cnt; } rs.close(); return 0; } // 将整数在万分位以逗号分隔表示 public static String toEastNumFormat(long number) { DecimalFormat df = new DecimalFormat("#,####"); return df.format(number); } // change seconds to DayHourMinuteSecond format private static String ms2DHMS(long startMs, long endMs) { String retval = null; long secondCount = (endMs - startMs) / 1000; String ms = (endMs - startMs) % 1000 + "ms"; long days = secondCount / (60 * 60 * 24); long hours = (secondCount % (60 * 60 * 24)) / (60 * 60); long minutes = (secondCount % (60 * 60)) / 60; long seconds = secondCount % 60; if (days > 0) { retval = days + "d" + hours + "h" + minutes + "m" + seconds + "s"; } else if (hours > 0) { retval = hours + "h" + minutes + "m" + seconds + "s"; } else if (minutes > 0) { retval = minutes + "m" + seconds + "s"; } else { retval = seconds + "s"; } return retval + ms; } public static void main(String[] args) { RecordChanger rc=new RecordChanger(); rc.changeOnePencent("bigtable"); } }
以下是修改两个表的运行结果反馈：
There are 10,0000 records in the table:'smalltable'. There are 1000 records should be changed. There are 1000 records in idSet. There are 1000 records have been changed. It takes 2s276ms to update 1% records of table:'smalltable'. There are 100,0000 records in the table:'bigtable'. There are 1,0000 records should be changed. There are 1,0000 records in idSet. There are 1,0000 records have been changed. It takes 20s251ms to update 1% records of table:'bigtable'.
全部运行时间加起来一分钟出点头，还是可以的。
--END-- 2020年1月5日09点32分