ORACLE百万及上亿测试数据表创建

用的CTAS语句。

CREATE TABLE [TABLE_NAME]  AS 

[SELECT ...]

数据来源为dual伪表，用到了oracle特性connect by进行数据高度筛选。

首先是百万数据创建测试：

CREATE TABLE BHL_TESTTABLE AS
SELECT ROWNUM AS ID, TO_CHAR(SYSDATE + ROWNUM/24/3600,‘yyyy-mm-dd hh24:mi:ss‘) as inc_datetime,
TRUNC(DBMS_RANDOM.VALUE(0,100)) AS RANDOM_ID,
DBMS_RANDOM.STRING(‘x‘,20) RANDOM_STRING
FROM DUAL
CONNECT BY LEVEL <= 1000000;

 耗时23秒。

其次是上亿测试数据表创建

CREATE TABLE BHL_TESTTABLE AS 
SELECT ROWNUM AS ID, TO_CHAR(SYSDATE + ROWNUM/24/3600,‘yyyy-mm-dd hh24:mi:ss‘) as inc_datetime,
TRUNC(DBMS_RANDOM.VALUE(0,100)) AS RANDOM_ID,
DBMS_RANDOM.STRING(‘x‘,20) RANDOM_STRING
FROM DUAL
CONNECT BY LEVEL <= 100000000;

 可以看到在8分24秒时，oracle抛出了ORA-30009：CONNECT BY 操作内存错误。度娘查证后发现一亿数据量过大，sga内存不足，解决方案为另起炉灶，使用oracle 10g后支持的xmltable。

CREATE TABLE BHL_TESTTABLE_NEW AS
SELECT ROWNUM AS ID, TO_CHAR(SYSDATE + ROWNUM/24/3600,‘yyyy-mm-dd hh24:mi:ss‘) as inc_datetime,
TRUNC(DBMS_RANDOM.VALUE(0,100)) AS RANDOM_ID,
DBMS_RANDOM.STRING(‘x‘,20) RANDOM_STRING
FROM xmltable(‘1 to 100000000‘);

 一亿数据表创建完成！，耗时38分32秒。

呃呃，有点略长。

附：已有表追加数据表达式使用

INSERT INTO [TABLE_NAME]  [SELECT ...] 

ORACLE百万及上亿测试数据表创建

原文：https://www.cnblogs.com/ssdba/p/15165737.html