------------------------------------------------------------------
-- update_stat_need_only.sql
--
-- This is an SQL script to generate UPDATE STATISTICS commands 
-- by the difference between real number rows (systabinfo) and known one (systables) 
--      difference > 10% 
-- 
--      IDS 7.3+ 9.2+
--
--     %DBA%\bat\update_stat_opt.bat  db_name  update_stat_need_only
--
-- V.Shulzhenko  DBA_Tools  
------------------------------------------------------------------
--  Отобрать только таблицы, у которых кол-во строк изменилось на 10%
------------------------------------------------------------------
select   s.tabid                        table_id
        ,s.tabname[ 1 , 32 ]                table_name
        ,abs(sum(i.ti_nrows)-s.nrows)   difference
--        ,sum(i.ti_nrows)                real_rows
        ,s.nrows                        known_rows
from  sysmaster:systabinfo i,systables s
where i.ti_partnum = s.partnum
       and s.tabid >  99  and s.tabtype='T'
group by  1 , 2 , 4 
having abs(sum(i.ti_nrows)-s.nrows) > s.nrows* 0 . 1 
into temp _temp_dbatools with no log;
------------------------------------------------------------------
--select * from _temp_dbatools;
select count(*) updated_tables from _temp_dbatools;
------------------------------------------------------------------
--  MEDIUM для всех столбцов, кроме являющихся первыми в индексах
------------------------------------------------------------------
output to '_update_stat_opt.sql' without headings
SELECT UNIQUE 'UPDATE STATISTICS MEDIUM FOR TABLE ', TRIM(s.tabname)||';', '--'|| 2 
FROM systables s, _temp_dbatools t
WHERE t.table_id = s.tabid

------------------------------------------------------------------
--  HIGH для столбцов, являющихся первыми в индексах пользовательских таблиц
------------------------------------------------------------------
UNION
SELECT UNIQUE 'UPDATE STATISTICS HIGH FOR TABLE ', TRIM(s.tabname) || '(' ||
       TRIM(colname) || ');', '--'|| 4 
FROM systables s, syscolumns c, sysindexes i, _temp_dbatools t
WHERE s.tabid = c.tabid
AND i.tabid = s.tabid
AND i.part1 = c.colno
and t.table_id=s.tabid

order by  3 ;
------------------------------------------------------------------