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

时间：2021-10-14 23:33:21

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

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

INSERT ALL

   INTO firsttb(NAME, age,createdtime) values('E1','',sysdate)

   INTO firsttb(NAME, age,createdtime) values('E2','',sysdate)

   INTO firsttb(NAME, age,createdtime) values('E3','',sysdate)

   ...

   INTO firsttb(NAME, age,createdtime) values('E250','',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_million2 PRIMARY KEY (id);

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

我的执行记录：

Table HY_MILLION created.

2,000,000 rows inserted.

Commit complete.

Table HY_MILLION2 created.

Commit complete.

2,000,000 rows inserted.

2,000,000 rows inserted.

2,000,000 rows inserted.

2,000,000 rows inserted.

10,000,000 rows updated.

Commit complete.

Table HY_MILLION2 altered.

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

参考资料：

https://blog.csdn.net/paullinjie/article/details/80615295

附：MySQL批量插入语法

Mysql batch-insert grammar:
insert into emp(name,age,cdate)
values
('A' , 20, '2019-10-13 00:00:00'),
('B' , 21, '2019-10-13 01:00:00'),
('C' , 22, '2019-10-13 05:00:00')

MySql批量插入远比Oracle快，采用这种语法仅用程序就可以达到高速，具体实验请看：

https://www.cnblogs.com/xiandedanteng/p/11666743.html

--END-- 2020-01-09 09:51

相关文章