按月拆分数据库表--oracle

凤舞飘伶

已于 2022-07-04 17:10:47 修改

阅读量833

点赞数

分类专栏： oracle 文章标签： oracle 数据库 sql

于 2021-06-10 13:51:37 首次发布

本文链接：https://blog.csdn.net/woshaguayi/article/details/117777598

版权

oracle 专栏收录该内容

64 篇文章 3 订阅

订阅专栏

生产有一张日志表，数据量很大，需要按月进行存储，存储过程如下：

CREATE OR REPLACE PROCEDURE NEWLOG4_SUB_TABLE 
IS
    table_name1              VARCHAR2(50);
    create_table_sql         VARCHAR2(4000);
  insert_data_sql      VARCHAR2(4000);
  delete_data_sql      VARCHAR2(4000);
--  v_exists INT:=0;
   v_exists  NUMBER (10, 0);
BEGIN

  SELECT 'NEWLOG4_' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') INTO table_name1 FROM DUAL;

  select count(1) into v_exists from user_tables where table_name=UPPER(table_name1);
  --dbms_output.put_line(sname);
  --dbms_output.put_line(table_name1);
 if (v_exists <1)
   then
 -- dbms_output.put_line(sname);
  create_table_sql := 'create table ' || table_name1 || ' (
                             autudt          TIMESTAMP(6),
                  authentype      VARCHAR2(2000),
                  userid          VARCHAR2(2000),
                  orgid           VARCHAR2(2000),
                  org2id          VARCHAR2(2000),
                  realname        VARCHAR2(2000),
                  success         VARCHAR2(2000),
                  idpname         VARCHAR2(2000),
                  idpip           VARCHAR2(2000),
                  vistorip        VARCHAR2(2000),
                  vistorbrowser   VARCHAR2(2000),
                  spid            VARCHAR2(2000),
                  spurl           VARCHAR2(2000),
                  info            VARCHAR2(2000),
                  autdesc         VARCHAR2(2000),
                  taketime        VARCHAR2(2000),
                  orgnamefullpath VARCHAR2(2000),
                  ines            INTEGER default 0,
                  logid           VARCHAR2(32),
                  inputaccount    VARCHAR2(2000),
                  channel         NUMBER(32),
                  pushstate       NUMBER(2) default 0,
                   appid           VARCHAR2(50)
                      )';
  EXECUTE IMMEDIATE create_table_sql;
   commit;
  end if;

    --将FATHER_TABLE表中取上月记录 添加到新创建的分表中。
  insert_data_sql := 'INSERT INTO ' || table_name1 || ' SELECT * FROM NEWLOG4 WHERE autudt <(systimestamp - NUMTODSINTERVAL(30,''day'')) ';
  EXECUTE IMMEDIATE insert_data_sql;


  --删除FATHER_TABLE表中时间在上个月范围内的所有数据
  delete_data_sql := 'DELETE FROM NEWLOG4 WHERE autudt <(systimestamp-NUMTODSINTERVAL(30,''day''))';
  EXECUTE IMMEDIATE delete_data_sql;

    COMMIT;
 --EXCEPTION
  --WHEN OTHERS THEN
  --   ROLLBACK;
END NEWLOG4_SUB_TABLE;

create or replace procedure pro_exelog2 is
datetime INTEGER :=30; --Storage duration
v_name varchar2(40);
v_lastmouth varchar2(40);
v_count number:=0;
v_tbrecordname varchar2(10) :='newlog2_';
v_table varchar2(4000);
v_exists INT:=0;
begin
  select tb_name,v_tbrecordname||to_char(sysdate-datetime,'yyyymm') into v_name,v_lastmouth from tb_record where type =2 and status=1;
  select count(1) into v_count from tb_record where type =2 and tb_name=UPPER(v_lastmouth);
  if v_count=0 then
      begin
        update tb_record set status=0 where type =2;  -- erase status
        insert into tb_record(type,tb_name,status) values(2,UPPER(v_lastmouth),1); --insert new table record
        end;
  end if;
  select count(1) into v_exists from user_tables where table_name=UPPER(v_lastmouth);
  --create new table when time > 30 days
 if v_exists!=1 then
    begin
    v_table:='create table '||v_name||' (AUTUDT TIMESTAMP(6),AUTHENTYPE VARCHAR2(2000),USERID VARCHAR2(2000),ORGID VARCHAR2(800),ORG2ID VARCHAR2(800),REALNAME VARCHAR2(800),SUCCESS VARCHAR2(20),IDPNAME VARCHAR2(2000),IDPIP VARCHAR2(2000),VISTORIP VARCHAR2(2000),VISTORBROWSER VARCHAR2(2000),SPID VARCHAR2(2000),SPURL VARCHAR2(2000),INFO VARCHAR2(2000),AUTDESC VARCHAR2(2000),TAKETIME VARCHAR2(2000),ORGNAMEFULLPATH VARCHAR2(2000))';
    EXECUTE IMMEDIATE v_table;
    commit;
     end;
  end if;
  v_table:='insert into '||v_lastmouth||' select * from newlog2 where autudt <(sysdate-'||datetime||')'; --insert new data from old table
  EXECUTE IMMEDIATE v_table;
  commit;
  delete from newlog2 where autudt <(sysdate-datetime); --delete old data
  commit;
end pro_exelog2;

create or replace procedure pro_exelog1 is
datetime INTEGER :=30;
v_name varchar2(40);
v_lastmouth varchar2(40);
v_count int:=0;
v_table varchar2(4000);
v_exists INT:=0;
v_tbrecordname varchar2(10) :='newlog1_';
begin
  select tb_name ,v_tbrecordname||to_char(sysdate-datetime,'yyyymm') into v_name,v_lastmouth from tb_record where type =1 and status=1;
  select count(1) into v_count from tb_record where type =1 and tb_name=UPPER(v_lastmouth);
  if v_count=0 then
      begin
        update tb_record set status=0 where type=1;  -- erase status
        insert into tb_record(type,tb_name,status) values(1,UPPER(v_lastmouth),1); --insert new table record
        end;
  end if;
  
  select count(1) into v_exists from user_tables where table_name=UPPER(v_lastmouth);
  --create new table when time > 30 days
 if v_exists!=1 then
    begin
    v_table:='create table '||v_lastmouth||' (OPDT TIMESTAMP(6),OPUSERID VARCHAR2(32),OPUSERIP VARCHAR2(20),OPTYPE VARCHAR2(20),MAINOBJECTID VARCHAR2(32), MAINOBJECTTYPE VARCHAR2(20),MAINACTION VARCHAR2(50),BEFOREACTIONOBJECTJSONSTRING CLOB,AFTERACTIONOBJECTJSONSTRING CLOB,EXCCUTESTATUS VARCHAR2(2000),ERRORDESC VARCHAR2(2000),MAINOBJECTORG VARCHAR2(32),LOG_ID VARCHAR2(32) not null,PLATFORM VARCHAR2(20))';
    EXECUTE IMMEDIATE v_table;
    commit;
     end;
  end if;
  v_table:='insert into '||v_lastmouth||' select * from newlog1 where opdt <(sysdate-'||datetime||')'; --insert new data from old table
  EXECUTE IMMEDIATE v_table;
  commit;
 delete from newlog1 where opdt <(sysdate-datetime); --delete old data
 commit;
end pro_exelog1;

create or replace 
PROCEDURE PROC_CREATE_SUB_TABLE 
IS
    table_name              VARCHAR2(50);
    create_table_cursor     NUMBER(10);
    create_table_sql         VARCHAR2(1000);
  insert_data_sql      VARCHAR2(1000);
  delete_data_sql      VARCHAR2(1000);
  v_exists INT:=0;
BEGIN
  --生成分表的表名。
  SELECT 'NEWLOG4_' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') INTO table_name FROM DUAL;
  
  select count(1) into v_exists from user_tables where table_name=UPPER(table_name);
  if v_exists!=1  then 
  create_table_cursor := DBMS_SQL.OPEN_CURSOR;--打开游标
  --拼出创建表的SQL语句，并执行。
  create_table_sql := 'create table ' || table_name || ' (
													   autudt          TIMESTAMP(6),
								  authentype      VARCHAR2(2000),
								  userid          VARCHAR2(2000),
								  orgid           VARCHAR2(2000),
								  org2id          VARCHAR2(2000),
								  realname        VARCHAR2(2000),
								  success         VARCHAR2(2000),
								  idpname         VARCHAR2(2000),
								  idpip           VARCHAR2(2000),
								  vistorip        VARCHAR2(2000),
								  vistorbrowser   VARCHAR2(2000),
								  spid            VARCHAR2(2000),
								  spurl           VARCHAR2(2000),
								  info            VARCHAR2(2000),
								  autdesc         VARCHAR2(2000),
								  taketime        VARCHAR2(2000),
								  orgnamefullpath VARCHAR2(2000),
								  ines            INTEGER default 0,
								  logid           VARCHAR2(32),
								  inputaccount    VARCHAR2(2000),
								  channel         NUMBER(32),
								  pushstate       NUMBER(2) default 0
                      )';
    DBMS_SQL.PARSE(create_table_cursor, create_table_sql, DBMS_SQL.V7);
    DBMS_SQL.CLOSE_CURSOR(create_table_cursor);
   end if;

    --将FATHER_TABLE表中取上月记录 添加到新创建的分表中。
  insert_data_sql := 'INSERT INTO ' || table_name || ' SELECT * FROM NEWLOG4 WHERE autudt <(systimestamp + NumToYMInterval(-1, 'MONTH')) ';
           v_table:='insert into '||v_lastmouth||' select * from newlog2 where autudt <(sysdate-'||datetime||')';
  EXECUTE IMMEDIATE insert_data_sql;


  --删除FATHER_TABLE表中时间在上个月范围内的所有数据
  delete_data_sql := 'DELETE FROM NEWLOG4 WHERE autudt <(systimestamp + NumToYMInterval(-1, 'MONTH')) ';
  EXECUTE IMMEDIATE delete_data_sql;

    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
END PROC_CREATE_SUB_TABLE;

CREATE OR REPLACE PROCEDURE NEWLOG4_history_table
IS
  -- table_name1              VARCHAR2(50);
  -- create_table_sql         VARCHAR2(4000);
  -- insert_data_sql      VARCHAR2(4000);
  -- delete_data_sql      VARCHAR2(4000);
--  v_exists INT:=0;
  -- v_exists  NUMBER (10, 0);
   CURSOR cur IS
    select * from NEWLOG4 t where autudt <(systimestamp-NUMTODSINTERVAL(30,'day'))and pushstate='3';
    TYPE rec IS TABLE OF NEWLOG4%ROWTYPE;
    recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 5000;
FORALL i IN 1 .. recs.COUNT
INSERT INTO NEWLOG4_DAY_INTERVAL_PARTITION VALUES recs (i);
 COMMIT;
 EXIT WHEN cur%NOTFOUND;
 END LOOP;
 CLOSE cur;
 --EXCEPTION
  --WHEN OTHERS THEN
  --   ROLLBACK;
END NEWLOG4_history_table;

CREATE OR REPLACE PROCEDURE NEWLOG4_day_TABLE (delete_date in varchar2)
IS
  insert_data_sql      VARCHAR2(4000);
  delete_data_sql      VARCHAR2(4000);
   CURSOR cur IS
select * from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = delete_date and pushstate='3';
TYPE rec IS TABLE OF NEWLOG4%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 5000;
FORALL i IN 1 .. recs.COUNT
--EXECUTE IMMEDIATE insert_data_sql;
INSERT INTO NEWLOG4_DAY_INTERVAL_PARTITION VALUES recs (i);
 COMMIT;
 EXIT WHEN cur%NOTFOUND;
 END LOOP;
 CLOSE cur;

 --EXCEPTION
  --WHEN OTHERS THEN
  --   ROLLBACK;
END NEWLOG4_day_TABLE;

CREATE OR REPLACE PROCEDURE NEWLOG4_SUB_test (delete_date in varchar2)
IS
   -- table_name1              VARCHAR2(50);
   -- create_table_sql         VARCHAR2(4000);
  insert_data_sql      VARCHAR2(4000);
  delete_data_sql      VARCHAR2(4000);
--  v_exists INT:=0;
 --  v_exists  NUMBER (10, 0);
   CURSOR cur IS
select * from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = delete_date and pushstate='3';
TYPE rec IS TABLE OF NEWLOG4%ROWTYPE;
recs rec;
BEGIN
    --将FATHER_TABLE表中取上月记录 添加到新创建的分表中。
 -- insert_data_sql := 'INSERT INTO ' || table_name1 || ' SELECT * FROM NEWLOG4 WHERE autudt <(systimestamp - NUMTODSINTERVAL(30,''day'')) and pushstate=''3''';
 -- EXECUTE IMMEDIATE insert_data_sql;
 --insert_data_sql :='INSERT INTO '||history_table|| 'VALUES recs (i)'
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 5000;
FORALL i IN 1 .. recs.COUNT
--EXECUTE IMMEDIATE insert_data_sql;
INSERT INTO NEWLOG4_202103_10 VALUES recs (i);
 COMMIT;
 EXIT WHEN cur%NOTFOUND;
 END LOOP;
 CLOSE cur;

--insert_data_sql :='alter session enable parallel dml';
  --EXECUTE IMMEDIATE insert_data_sql;
   -- delete_data_sql :='delete /*+parallel(12)*/  from NEWLOG4  nologging  where TO_CHAR(autudt,''YYYY-MM-DD'') =''2021-03-10''and pushstate=''3''';

   --delete_data_sql :='delete /*+parallel(12)*/  from NEWLOG4  nologging  where TO_CHAR(autudt,''YYYY-MM-DD'') =' ||delete_date|| 'and pushstate=''3''';
  --EXECUTE IMMEDIATE delete_data_sql;
   --COMMIT;
 --EXCEPTION
  --WHEN OTHERS THEN
  --   ROLLBACK;
END NEWLOG4_SUB_test;

2.alter table tab1 nologging;
　　insert /*+ append */ into tab1 select * from tab2;
　　commit;
　　alter table tab1 logging;


　　该方法会使得产生arch大大减少，并且在一定程度上提高时间，根据经验，千万级的数据可在45分钟内完成。但是请注意，该方法适合单进程的串行方式，如果当有多个进程同时运行时，后发起的进程会有enqueue的等待。注意此方法千万不能dataguard上用(不过要是在database已经force logging那也是不怕的，呵呵)!!



3.　insert into tab1 select /*+ parallel */ * from tab2;
　　commit;


　　对于select之后的语句是全表扫描的情况，我们可以加parallel的hint来提高其并发，这里需要注意的是最大并发度受到初始化参数parallel_max_servers的限制，并发的进程可以通过v$px_session查看，或者ps -ef |grep ora_p查看。


4.　alter session enable parallel dml;
　　insert /*+ parallel */ into tab1 select * from tab2;
　　commit;


　　与方法2相反，并发的insert，尚未比较和方法2哪个效率更高(偶估计是方法2快)，有测试过的朋友欢迎补充。

5.insert into tab1 select * from tab2 partition (p1);
　　insert into tab1 select * from tab2 partition (p2);
　　insert into tab1 select * from tab2 partition (p3);
　　insert into tab1 select * from tab2 partition (p4);


　　对于分区表可以利用tab1进行多个进程的并发insert，分区越多，可以启动的进程越多。我曾经试过insert 2.6亿行记录的一个表，8个分区，8个进程，如果用方法2，单个进程完成可能要40分钟，但是由于是有8个分区8个进程，后发进程有enqueue，所以因此需要的时间为40分钟×8;但是如果用方法5，虽然单个进程需要110分钟，但是由于能够并发进程执行，所以总共需要的时间就约为110分钟了。

6.DECLARE
　　TYPE dtarray IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
　　v_col1 dtarray;
　　v_col2 dtarray;
　　v_col3 dtarray;
　　BEGIN
　　SELECT col1, col2, col3 BULK COLLECT
　　INTO v_col1, v_col2, v_col3
　　FROM tab2;
　　FORALL i IN 1 .. v_col1.COUNT
　　insert into tab1 WHERE tab1.col1 = v_col1;
　　END;


　　用批量绑定(bulk binding)的方式。当循环执行一个绑定变量的sql语句时候，在PL/SQL 和SQL引擎(engines)中，会发生大量的上下文切换(context switches)。使用bulk binding，能将数据批量的从plsql引擎传到sql引擎，从而减少上下文切换过程，提升效率。该方法比较适合于在线处理，不必停机
————————————————

7.sqlplus -s user/pwd< runlog.txt
　　set copycommit 2;
　　set arraysize 5000;
　　copy from user/pwd@sid -
　　to user/pwd@sid -
　　insert tab1 using select * from tab2;
　　exit
　　EOF


　　用copy的方法进行插入，注意此处insert没有into关键字。该方法的好处是可以设置copycommit和arrarysize来一起控制commit的频率，上面的方法是每10000行commit一次

凤舞飘伶

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
打赏
1
评论
按月拆分数据库表--oracle

生产有一张日志表，数据量很大，需要按月进行存储，存储过程如下：CREATE OR REPLACE PROCEDURE NEWLOG4_SUB_TABLE IS table_name1 VARCHAR2(50); create_table_sql VARCHAR2(4000); insert_data_sql VARCHAR2(4000); delete_data_sql VARCHAR2(4000);-- v_
复制链接

扫一扫