25分钟创建一千六百万大表全记录

2020年3月14日19点16分开始

创建两百万中间表：

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

表已创建。

已用时间:  00: 00: 00.18

给两百万中间表充值：

SQL> insert into tb_2million
  2  select rownum,
  3          dbms_random.string('*',dbms_random.value(6,20)),
  4          dbms_random.value(1000,50000),
  5  sysdate
  6  from dual
  7  connect by level<=2000000
  8  order by dbms_random.random;

已创建2000000行。

已用时间:  00: 03: 09.16

提交一次：

SQL> commit;

提交完成。

已用时间:  00: 00: 00.01

接下来以两百万中间表作为大表的基础：

SQL> create table tb_16million as select * from tb_2million;

表已创建。

已用时间:  00: 00: 05.89

然后往自身插值三次：

SQL> insert into tb_16million select * from tb_16million;

已创建2000000行。

已用时间:  00: 00: 46.96
SQL> insert into tb_16million select * from tb_16million;

已创建4000000行。

已用时间:  00: 00: 34.35
SQL> insert into tb_16million select * from tb_16million;

已创建8000000行。

已用时间:  00: 01: 08.59

看看有多少数据：

SQL> select count(*) from tb_16million;

  COUNT(*)
----------
  16000000

已用时间:  00: 00: 06.67

再次提交：

SQL> commit;

提交完成。

已用时间:  00: 00: 00.01

至此为19点25分，可以说一千六百万数据表已经建成，用时9分钟。但剩下还有一部分任务需要做，那就是设上主键。

由于我们反复插值，导致大表id部分是重复的，我们需要把它整理成递增序列.

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

已更新16000000行。

已用时间:  00: 13: 27.04

用时十三分钟，这比前面反复建表都长。

再次提交：

SQL> commit;

提交完成。

已用时间:  00: 00: 00.00

最后设上主键：

SQL> ALTER TABLE tb_16million ADD CONSTRAINT tb_16million_pk PRIMARY KEY (id);

表已更改。

已用时间:  00: 00: 39.54

至此为19点41分，总计用时25分。

从上面语句耗时情况看，最为耗时的是一条一条去设置值，典型代表是上面给两百万中间表充值和更新大表id字段；其次耗时的是批量数据的拷贝，典型代表是tb_16million自身往自身插值，与前面相比它耗时已经少很多了；再次为查询数量，与前两者相比已经很少了；至于设主键，提交，之类，耗时几乎可以忽略不计。

这便是一个一千六百万大表的创建过程，全部sql语句如下：

CREATE TABLE tb_2million
(
    id NUMBER not null primary key,
    name NVARCHAR2(60) not null,
    salary int NOT NULL,
    createtime TIMESTAMP (6) not null
)

insert into tb_2million
select rownum,
        dbms_random.string('*',dbms_random.value(6,20)),
        dbms_random.value(1000,50000),
    sysdate
from dual
connect by level<=2000000
order by dbms_random.random

create table tb_16million as select * from tb_2million;

insert into tb_16million select * from tb_16million;  --4m--
insert into tb_16million select * from tb_16million;  --8m--
insert into tb_16million select * from tb_16million;  --16m--

select count(*) from tb_16million;

update tb_16million set id=rownum where 1=1;

ALTER TABLE tb_16million ADD CONSTRAINT tb_16million_pk PRIMARY KEY (id);

我的环境：

#	类别	版本
1	操作系统	Win10
2	数据库	Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
3	硬件环境	T440p
4	内存	8G

没有实际经验支撑的理念是空洞和经不起推敲的，所以工程师需要反复实验以接近真理。

--2020年3月14日--