达梦：每周定时任务统计指定模式下的每个表的行，并基于统计的数据计算增量

1、创建临时表存储统计值

CREATE  TABLE T_STAT_TAB(TAB_NAME VARCHAR(30),ROW_NUM INT ,STAT_TIME DATE ) ;

2、创建存储过程计算表的总记录数

CREATE OR REPLACE  PROCEDURE P_STATIC_TAB (SCH_NAME VARCHAR(100))
 AS 
--统计每个表的数据量到TEST表进行记录，并查询出TEST表的结果
DECLARE
COUNT_ROWS INT;
V_SQL VARCHAR(100);
DT  DATE;
BEGIN
COUNT_ROWS:=0;
FOR A IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = SCH_NAME )
LOOP
V_SQL:='SELECT COUNT(*) FROM ' || SCH_NAME ||'.'||A.TABLE_NAME;
EXECUTE IMMEDIATE V_SQL INTO COUNT_ROWS;
INSERT INTO T_STAT_TAB VALUES(A.TABLE_NAME,COUNT_ROWS,SYSDATE()-1);
END LOOP;
SELECT * FROM T_STAT_TAB;
END;

 
3、创建定时每周一执行统计

call SP_CREATE_JOB('static_tab',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('static_tab');
call SP_ADD_JOB_STEP('static_tab', 'static_tab', 0, 'CALL P_STATIC_TAB(''TESTUSER'')', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('static_tab', 'static_tab', 1, 2, 1, 2, 0, '01:00:00', NULL, '2022-09-21 22:04:17', NULL, '');
call SP_JOB_CONFIG_COMMIT('static_tab');

 

4、手动调用存储过程统计指定模式下的表数量

-- 统计SYSDBA模式下表数据量
call  P_STATIC_TAB ('SYSDBA');

 

 5、创建统计增量的临时表

CREATE TABLE "SYSDBA"."T_STAT_TAB_INC"
(
"TAB_NAME" VARCHAR(30) NOT NULL,
"STAT_TIME" DATE NOT NULL,
"TOTAL_ROWS" INT,
"PRV_TOTAL_ROWS" INT,
"INCREMENT_ROWS" INT,
NOT CLUSTER PRIMARY KEY("TAB_NAME", "STAT_TIME")) STORAGE(ON "MAIN", CLUSTERBTR) ;

 

6、创建统计增量的存储过程

CREATE OR REPLACE procedure P_SEL_TAB_SUM (SCH_NAME VARCHAR(100) )
AS
DECLARE
TAB_NAME VARCHAR(30);
STAT_TIME DATE ;
TOTAL_ROWS INT;
PRV_TOTAL_ROWS INT;
INCREMENT_ROWS INT ;
COUNT_ROWS INT;
V_SQL VARCHAR(100);
BEGIN
COUNT_ROWS:=0;
FOR A IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = SCH_NAME )
LOOP
INSERT INTO  T_STAT_TAB_INC (TAB_NAME , STAT_TIME   ,TOTAL_ROWS  ,PRV_TOTAL_ROWS  ,INCREMENT_ROWS )
 SELECT *,IFNULL(TOTAL_ROWS - PRV_TOTAL_ROWS ,0)  as INCREMENT_ROWS
FROM(
    SELECT TAB_NAME,STAT_TIME,IFNULL(TOTAL_ROWS,0) AS TOTAL_ROWS ,IFNULL(LEAD(TOTAL_ROWS) OVER(ORDER BY TAB_NAME,STAT_TIME DESC),0)  PRV_TOTAL_ROWS
    FROM (
        SELECT TAB_NAME,LEFT(STAT_TIME,10) STAT_TIME ,SUM(ROW_NUM) TOTAL_ROWS
        FROM T_STAT_TAB where TAB_NAME =  A.TABLE_NAME
        GROUP BY TAB_NAME , LEFT(STAT_TIME,10)
        order by TAB_NAME , LEFT(STAT_TIME,10)
        )  
    )
-- WHERE  PRV_TOTAL_ROWS IS NOT NULL
ORDER BY TAB_NAME, STAT_TIME ;
COMMIT ;
END LOOP;
END;

 
7、调动存储过程统计增量数据

--先清空历史统计
TRUNCATE TABLE T_STAT_TAB_INC;

--在调用存储过程执行
CALL P_SEL_TAB_SUM('TESTUSER');

8、创建定时任务在每三周执行增量统计

call SP_CREATE_JOB('static_tab_inc',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('static_tab_inc');
call SP_ADD_JOB_STEP('static_tab_inc', 'static_tab_inc', 0, 'TRUNCATE TABLE T_STAT_TAB_INC;
CALL P_SEL_TAB_SUM(''TESTUSER'');', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('static_tab_inc', 'static_tab_inc', 1, 2, 3, 2, 0, '02:00:00', NULL, '2022-09-21 22:11:09', NULL, '');
call SP_JOB_CONFIG_COMMIT('static_tab_inc');