统计表每日修改量，表记录数

最新推荐文章于 2025-01-04 22:11:57 发布

转载最新推荐文章于 2025-01-04 22:11:57 发布 · 122 阅读

本文分享了SQL查询语句的使用，包括计算修改总量、分析表大小等操作，并提供了数据库统计技术的应用实例。

SELECT A.TABLE_NAME,
       round(A.修改总量 /
       TO_NUMBER(TO_DATE(TO_CHAR(A.修改结束日期, 'YYYYMMDD'), 'YYYYMMDD') -
                 TO_DATE(TO_CHAR(B.修改开始日期, 'YYYYMMDD'), 'YYYYMMDD') + 1),2)
FROM (SELECT TABLE_NAME,
               SUM(INSERTS + UPDATES + DELETES) 修改总量,
               TIMESTAMP 修改结束日期
          FROM USER_TAB_MODIFICATIONS
         WHERE TABLE_NAME IN
               ('AA02', 'AA10_TABLE', 'AA52', 'AA53', 'AA54', 'AA55', 'AB01',
                'AB02', 'AB07', 'AB09', 'AB15', 'AC01', 'AC02', 'AC04',
                'AC05', 'AC06', 'AC43', 'AE03', 'AZ01', 'IC03', 'AC62',
                'AC69', 'AC87', 'AC88', 'AC89', 'AC93', 'AC94', 'AC95',
                'AC96', 'AC97', 'AC98', 'AE50', 'AE55', 'AE56', 'AE57',
                'AE58', 'IC02', 'IC10', 'IC40', 'IC63', 'LB05', 'LC01',
                'LC10', 'LC30', 'LC31', 'MC01', 'MC02', 'ZA31', 'ZA32',
                'KA02', 'KA03', 'KA04', 'KA52', 'KA53', 'KB01', 'KC04',
                'KC21', 'KC22', 'KC24', 'KC33', 'KC41', 'KC45', 'KC47',
                'KC51', 'KC60', 'KF40', 'AB14')
         GROUP BY TABLE_NAME, TIMESTAMP) A,
       (SELECT TABLE_NAME, LAST_ANALYZED 修改开始日期
          FROM USER_TAB_STATISTICS
         WHERE TABLE_NAME IN
               ('AA02', 'AA10_TABLE', 'AA52', 'AA53', 'AA54', 'AA55', 'AB01',
                'AB02', 'AB07', 'AB09', 'AB15', 'AC01', 'AC02', 'AC04',
                'AC05', 'AC06', 'AC43', 'AE03', 'AZ01', 'IC03', 'AC62',
                'AC69', 'AC87', 'AC88', 'AC89', 'AC93', 'AC94', 'AC95',
                'AC96', 'AC97', 'AC98', 'AE50', 'AE55', 'AE56', 'AE57',
                'AE58', 'IC02', 'IC10', 'IC40', 'IC63', 'LB05', 'LC01',
                'LC10', 'LC30', 'LC31', 'MC01', 'MC02', 'ZA31', 'ZA32',
                'KA02', 'KA03', 'KA04', 'KA52', 'KA53', 'KB01', 'KC04',
                'KC21', 'KC22', 'KC24', 'KC33', 'KC41', 'KC45', 'KC47',
                'KC51', 'KC60', 'KF40', 'AB14')) B
WHERE A.TABLE_NAME = B.TABLE_NAME
ORDER BY 1;

SELECT TABLE_NAME, NUM_ROWS
FROM USER_TAB_STATISTICS
WHERE TABLE_NAME IN
       ('AA02', 'AA10_TABLE', 'AA52', 'AA53', 'AA54', 'AA55', 'AB01', 'AB02',
        'AB07', 'AB09', 'AB15', 'AC01', 'AC02', 'AC04', 'AC05', 'AC06',
        'AC43', 'AE03', 'AZ01', 'IC03', 'AC62', 'AC69', 'AC87', 'AC88',
        'AC89', 'AC93', 'AC94', 'AC95', 'AC96', 'AC97', 'AC98', 'AE50',
        'AE55', 'AE56', 'AE57', 'AE58', 'IC02', 'IC10', 'IC40', 'IC63',
        'LB05', 'LC01', 'LC10', 'LC30', 'LC31', 'MC01', 'MC02', 'ZA31',
        'ZA32', 'KA02', 'KA03', 'KA04', 'KA52', 'KA53', 'KB01', 'KC04',
        'KC21', 'KC22', 'KC24', 'KC33', 'KC41', 'KC45', 'KC47', 'KC51',
        'KC60', 'KF40', 'AB14')
ORDER BY TABLE_NAME;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-665052/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-665052/