Oracle数据库：如何高效对比表与前一日的行数差异？

1. 利用Oracle自带统计信息 #

• 查询DBA_TABLES/ALL_TABLES视图：Oracle默认每日运行的自动统计信息收集任务会更新NUM_ROWS字段，结合LAST_ANALYZED可获取表的最新行数及统计时间。
  示例查询：

  SELECT table_name, num_rows, last_analyzed
  FROM dba_tables
  WHERE owner = 'YOUR_SCHEMA'
    AND table_name IN ('TABLE1', 'TABLE2');
  

  若统计频率不符合需求，可手动调用DBMS_STATS.GATHER_TABLE_STATS更新，或通过DBMS_STATS.SET_SCHEMA_PREFS调整自动收集策略。

2. 借助AWR（自动工作负载仓库）分析历史数据 #

AWR会定期快照数据库对象统计信息，可通过DBA_HIST_TABLE_STAT查询表的历史行数变化，适合做长期趋势分析：

SELECT t.table_name, s.num_rows, snap.end_interval_time
FROM dba_hist_table_stat s
JOIN dba_hist_snapshot snap ON s.snap_id = snap.snap_id
JOIN dba_tables t ON s.obj# = t.object_id AND s.owner = t.owner
WHERE t.owner = 'YOUR_SCHEMA'
  AND t.table_name IN ('TABLE1', 'TABLE2')
ORDER BY snap.end_interval_time DESC;

默认AWR每小时快照一次、保留7天，可通过DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS调整快照频率与保留周期。

3. 自定义监控表+定时任务（可控性更强的规范方案） #

若需要精确控制收集时间、自定义波动阈值告警，可采用以下规范实现：

• 创建监控表存储每日行数：

  CREATE TABLE TABLE_ROW_COUNT_MONITOR (
    schema_name VARCHAR2(30) NOT NULL,
    table_name VARCHAR2(30) NOT NULL,
    count_date DATE NOT NULL,
    row_count NUMBER NOT NULL,
    CONSTRAINT pk_row_count_monitor PRIMARY KEY (schema_name, table_name, count_date)
  );
  

• 编写存储过程批量收集行数：

  CREATE OR REPLACE PROCEDURE COLLECT_TABLE_ROW_COUNTS(p_schema IN VARCHAR2, p_tables IN VARCHAR2) AS
    v_sql VARCHAR2(1000);
    v_count NUMBER;
    v_table VARCHAR2(30);
    v_start_pos NUMBER := 1;
    v_end_pos NUMBER;
  BEGIN
    LOOP
      v_end_pos := INSTR(p_tables, ',', v_start_pos);
      v_table := TRIM(CASE WHEN v_end_pos = 0 THEN SUBSTR(p_tables, v_start_pos) ELSE SUBSTR(p_tables, v_start_pos, v_end_pos - v_start_pos) END);
      
      v_sql := 'SELECT COUNT(*) FROM ' || p_schema || '.' || v_table;
      EXECUTE IMMEDIATE v_sql INTO v_count;
  
      MERGE INTO TABLE_ROW_COUNT_MONITOR m
      USING (SELECT p_schema AS schema_name, v_table AS table_name, TRUNC(SYSDATE) AS count_date, v_count AS row_count FROM DUAL) s
      ON (m.schema_name = s.schema_name AND m.table_name = s.table_name AND m.count_date = s.count_date)
      WHEN MATCHED THEN UPDATE SET m.row_count = s.row_count
      WHEN NOT MATCHED THEN INSERT (schema_name, table_name, count_date, row_count) VALUES (s.schema_name, s.table_name, s.count_date, s.row_count);
  
      EXIT WHEN v_end_pos = 0;
      v_start_pos := v_end_pos + 1;
    END LOOP;
    COMMIT;
  END;
  /
  

• 创建定时任务每日执行收集：

  BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
      job_name        => 'COLLECT_DAILY_ROW_COUNTS',
      job_type        => 'STORED_PROCEDURE',
      job_action      => 'COLLECT_TABLE_ROW_COUNTS',
      number_of_arguments => 2,
      start_date      => TRUNC(SYSDATE) + 23/24, -- 每日23点执行
      repeat_interval => 'FREQ=DAILY;INTERVAL=1',
      enabled         => TRUE,
      comments        => '每日收集指定表行数'
    );
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('COLLECT_DAILY_ROW_COUNTS', 1, 'YOUR_SCHEMA');
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('COLLECT_DAILY_ROW_COUNTS', 2, 'TABLE1,TABLE2');
  END;
  /
  

• 查询对比每日行数变化：

  SELECT m1.table_name,
         m1.row_count AS today_count,
         m2.row_count AS yesterday_count,
         ROUND((m1.row_count - m2.row_count)/m2.row_count*100, 2) AS change_percent
  FROM TABLE_ROW_COUNT_MONITOR m1
  JOIN TABLE_ROW_COUNT_MONITOR m2
    ON m1.schema_name = m2.schema_name
    AND m1.table_name = m2.table_name
    AND m1.count_date = TRUNC(SYSDATE)
    AND m2.count_date = TRUNC(SYSDATE) - 1
  WHERE m1.schema_name = 'YOUR_SCHEMA';
  

总结 #

• 粗略行数对比：优先用DBA_TABLES统计信息，无需额外开发；
• 历史趋势分析：直接使用AWR的快照数据；
• 精确监控与告警：采用自定义监控表+定时任务的规范方案，可灵活扩展阈值告警逻辑。

内容的提问来源于stack exchange，提问作者Javi Torre