数据类型(ORACLE)

为了准备测试环境，近日创建了一个包含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;