在Oracle中十分钟内创建一张千万级别的表

本文初稿：https://www.cnblogs.com/xiandedanteng/p/12169527.html

小表不会产生性能问题，大表才会。要练习SQL调优，还非得有大表不可。但数据不会自然产生，没有数据时如何创建一张千万级别的大表呢？

之前，我想用Oracle的批量插入语法去插入数据，此语法如下：

INSERT ALL 
   INTO firsttb(NAME, age,createdtime) values('E1','22',sysdate)
   INTO firsttb(NAME, age,createdtime) values('E2','32',sysdate)
   INTO firsttb(NAME, age,createdtime) values('E3','42',sysdate)
   ...
   INTO firsttb(NAME, age,createdtime) values('E250','42',sysdate)
select * from dual

通过Java程序，可以把Insert all 和 select * from dual 之间插入大批数据，然后一次性送给数据库去执行。

但是，这种方法是有限制的，在我的T440p机器上，Insert all 和 select * from dual 之间大约就能插250行左右，那么提交一千条数据需要四次IO。

而百万级插入需要十分钟，千万级就到了一个小时...太耽误事了，因此我只得寻求其它方案。

后来，我想出了以下步骤供大家参考：

先创建一张基础表，这张表结构应与你最终想建的表一致：

CREATE TABLE HY_million
(
    id NUMBER not null primary key,
    name NVARCHAR2(60) not null,
    score NUMBER(4,0) NOT NULL,
    createtime TIMESTAMP (6) not null
)

然后往这张表里插入两百万数据：

 Insert into HY_million
 select rownum,dbms_random.string('*',dbms_random.value(6,20)),dbms_random.value(0,20),sysdate from dual
 connect by level<=2000000
 order by dbms_random.random

注意200，0000这个值是根据机器来的，性能好的可以调大，说不定可以直接创建出千万数据，那么下面的步骤就省了，如果你不幸和我一样机器性能受限，那么还得继续下面的步骤。

先commit一次，接下来连表结构带数据创建目标表：

create table hy_million2 as select * from HY_million

查一下hy_million2有多少数据：

select count(*) from hy_million2

发现是两百万，目前这个表结构和HY_million是一样的，但没有约束，这正适合往里插入数据。

将以下语句执行四遍。

insert into hy_million2 select * from HY_million

执行完成hy_million2就有一千万数据了。

再执行下面语句把id规整一下：

update hy_million2 set id=rownum where 1=1

再看看规整得怎么样：

 select count(distinct id) from hy_million2

不出意外的话，结果应该是一千万。

然后再次commit。

最后给表设上主键：

 ALTER TABLE hy_million2 ADD CONSTRAINT constraint_name PRIMARY KEY (id);

之后，就可以开始使用这张千万级别的表了。

我的执行记录：

SQL> CREATE TABLE HY_million
  2  (
  3      id NUMBER not null primary key,
  4      name NVARCHAR2(60) not null,
  5      score NUMBER(4,0) NOT NULL,
  6      createtime TIMESTAMP (6) not null
  7  );

表已创建。

SQL> Insert into HY_million
  2   select rownum,dbms_random.string('*',dbms_random.value(6,20)),dbms_random.value(0,20),sysdate from dual
  3   connect by level<=2000000
  4   order by dbms_random.random;

已创建2000000行。

SQL> commit;

提交完成。

SQL> create table hy_million2 as select * from HY_million;

表已创建。

SQL> select count(*) from hy_million2;

  COUNT(*)
----------
   2000000

SQL> insert into hy_million2 select * from HY_million;

已创建2000000行。

SQL> insert into hy_million2 select * from HY_million;

已创建2000000行。

SQL> insert into hy_million2 select * from HY_million;

已创建2000000行。

SQL> insert into hy_million2 select * from HY_million;

已创建2000000行。

SQL> update hy_million2 set id=rownum where 1=1;

已更新10000000行。

SQL> select count(distinct id) from hy_million2;

COUNT(DISTINCTID)
-----------------
         10000000

SQL>

SQL> ALTER TABLE hy_million2 ADD CONSTRAINT constraint_name PRIMARY KEY (id);

表已更改。

SQL> commit;

提交完成。

我总的执行时间十分钟不到，你应该能做得更好。

--END-- 2020年1月12日08点35分