1分钟oracle 数据库按天插入60万数据和按天删除60万数据

创建一个很简单的表  test_history

create table TEST_HISTORY
(
  id      NUMBER not null,
  money   NUMBER,
  fltdate DATE
)

创建一个循环插入的存储过程，每天插入2万条数据

CREATE OR REPLACE PROCEDURE insert_600thoustand_data(mindate IN DATE ,MAXDATE IN DATE)
AUTHID CURRENT_USER AS
    min_date_char varchar2(10);
    max_date_char varchar2(10);
BEGIN
  SELECT TO_CHAR(mindate, 'yyyymmdd') INTO min_date_char FROM DUAL;
  SELECT TO_CHAR(MAXDATE, 'yyyymmdd') INTO max_date_char FROM DUAL;
    FOR V IN min_date_char..max_date_char LOOP
        FOR i IN 1..20000 LOOP
            INSERT INTO test_history VALUES(1,1,to_date(v,'yyyymmdd'));
            COMMIT;
         END LOOP;
    END LOOP;
END insert_600thoustand_data;

运行 command 命令模式下

exec insert_600thoustand_data(to_date('20150101','yyyymmdd')  ,to_date('20150131','yyyymmdd') )

用时 ：66.379s

按天删除

创建一个按天删除的存储过程

CREATE OR REPLACE PROCEDURE delete_600thoustand_data(mindate IN DATE ,MAXDATE IN DATE)
AUTHID CURRENT_USER AS
    min_date_char varchar2(10);
    max_date_char varchar2(10);
BEGIN
  SELECT TO_CHAR(mindate, 'yyyymmdd') INTO min_date_char FROM DUAL;
  SELECT TO_CHAR(MAXDATE, 'yyyymmdd') INTO max_date_char FROM DUAL;
    FOR V IN min_date_char..max_date_char LOOP
        DELETE FROM  test_history t WHERE t.fltdate=to_date(v,'yyyymmdd') ;
        COMMIT;
    END LOOP;
END delete_600thoustand_data;

运行命令

exec delete_600thoustand_data(to_date('20150101','yyyymmdd')  ,to_date('20150131','yyyymmdd') )

则快速删除