为了准备测试环境，近日创建了一个包含ORACLE全部内置类型的表，并填充了300万条测试数据。
ORACLE安装在8核48G内存的虚拟机上，全部采用默认配置，生成全部数据消耗约30min。
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX test_id_date';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX test_id_varchar2';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX test_id_number';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE test_all_types';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
CREATE TABLE test_all_types (
id_date DATE,
id_varchar2 VARCHAR2(23),
id_number NUMBER(21),
fd_varchar2_1 VARCHAR2(8 BYTE),
fd_varchar2_2 VARCHAR2(8 CHAR),
fd_varchar2_3 VARCHAR2(8),
fd_nvarchar2 NVARCHAR2(8),
fd_number_1 NUMBER(8),
fd_number_2 NUMBER(8,2),
fd_float_1 FLOAT,
fd_float_2 FLOAT(8),
fd_long LONG,
fd_date DATE,
fd_binary_float BINARY_FLOAT,
fd_binary_double BINARY_DOUBLE,
fd_timestamp_1 TIMESTAMP,
fd_timestamp_2 TIMESTAMP(8),
fd_timestamp_3 TIMESTAMP WITH TIME ZONE,
fd_timestamp_4 TIMESTAMP WITH LOCAL TIME ZONE,
fd_interval_1 INTERVAL YEAR TO MONTH,
fd_interval_2 INTERVAL YEAR(8) TO MONTH,
fd_interval_3 INTERVAL DAY TO SECOND,
fd_interval_4 INTERVAL DAY(8) TO SECOND(8),
fd_raw RAW(8),
fd_char_1 CHAR,
fd_char_2 CHAR(8),
fd_nchar_1 NCHAR,
fd_nchar_2 NCHAR(8),
fd_clob CLOB,
fd_nclob NCLOB,
fd_blob BLOB,
fd_bfile BFILE
);
CREATE INDEX test_id_date ON test_all_types (id_date DESC);
CREATE INDEX test_id_varchar2 ON test_all_types (id_varchar2 DESC);
CREATE INDEX test_id_number ON test_all_types (id_number DESC);
-- on sqlplus
SET timing ON;
SET serveroutput ON;
DELETE FROM test_all_types;
COMMIT;
DECLARE
i NUMBER(12);
n NUMBER(12);
t DATE;
ct DATE;
dt NUMBER(12); --ms
BEGIN
n:=3000000;
t:=TO_DATE('2017-08-10 12:30:45','yyyy-mm-dd hh24:mi:ss');
dt:=30000;
<< outer_loop >>
FOR i IN 1..n LOOP
ct:=t-i*dt/86400000;
INSERT INTO test_all_types (
id_date,
id_varchar2,
id_number,
fd_varchar2_1,
fd_varchar2_2,
fd_varchar2_3,
fd_nvarchar2,
fd_number_1,
fd_number_2,
fd_float_1,
fd_float_2,
fd_long,
fd_date,
fd_binary_float,
fd_binary_double,
fd_timestamp_1,
fd_timestamp_2,
fd_timestamp_3,
fd_timestamp_4,
fd_interval_1,
fd_interval_2,
fd_interval_3,
fd_interval_4,
fd_raw,
fd_char_1,
fd_char_2,
fd_nchar_1,
fd_nchar_2,
fd_clob,
fd_nclob,
fd_blob,
fd_bfile
) VALUES (
ct,
TO_CHAR(ct,'yyyy-mm-dd hh24:mi:ss'),
TO_NUMBER(TO_CHAR(ct,'yyyymmddhh24miss')),
TO_CHAR(Mod(i,10000000))||'A',
TO_CHAR(Mod(i,10000000))||'B',
TO_CHAR(Mod(i,10000000))||'C',
TO_NCHAR(Mod(i,10000000))||'N',
Mod(i,100000000),
Mod(i,100000000)/100,
i/1000,
i/100,
i,
ct,
i/1000,
i/100,
ct,
ct-10*365,
ct-20*365,
ct-30*365,
NUMTOYMINTERVAL(MOD(i,100), 'MONTH'),
NUMTOYMINTERVAL(MOD(i+12,100), 'MONTH'),
TO_DSINTERVAL(TO_CHAR(ct-10,'dd hh24:mi:ss')),
TO_DSINTERVAL(TO_CHAR(ct-20,'dd hh24:mi:ss')),
HEXTORAW(TO_CHAR(i+100)),
CHR(MOD(i,26)+65),
TO_CHAR(Mod(i,10000000))||'D',
CHR(MOD(i,26)+97),
TO_CHAR(Mod(i,10000000))||'d',
TO_CLOB(TO_CHAR(i+200)),
TO_NCLOB(TO_CHAR(i+300)),
TO_BLOB(HEXTORAW(TO_CHAR(i+400))),
NULL
);
IF MOD(i, 10000) = 0 THEN
dbms_output.put_line('## i is: ' || i);
COMMIT;
END IF;
END LOOP outer_loop;
END;
/
COMMIT;