RE: update statistics
Hi David
I use the next script to run update statistics. I run <update statistics
HIGH> for every column in a index, but it's only my opinion and my decission
over my environment:
=========================================================================
create temp table update_stats
( tabla smallint,
columna smallint,
grado smallint) with no log;
create index tmp_upstat on update_stats (tabla,columna);begin work;
insert into update_stats
select t.tabid,c.colno,1
from systables t, syscolumns c, sysindexes i
where t.tabid=c.tabid
and c.tabid=i.tabid
and c.colno=i.part1
and t.tabid>99 and tabname not like 'sys%'
and t.tabtype="T";commit work;
begin work;
insert into update_stats
select t.tabid,c.colno,2
from systables t, syscolumns c, sysindexes i
where t.tabid=c.tabid
and c.tabid=i.tabid
and c.colno=i.part2
and t.tabid>99 and tabname not like 'sys%'
and t.tabtype="T"
and not exists (select 1 from update_stats where tabla=t.tabid andc.colno=columna and grado<=2);
commit work;
begin work;
insert into update_stats
select t.tabid,c.colno,2
from systables t, syscolumns c, sysindexes i
where t.tabid=c.tabid
and c.tabid=i.tabid
and c.colno=i.part3
and t.tabid>99 and tabname not like 'sys%'
and t.tabtype="T"
and not exists (select 1 from update_stats where tabla=t.tabid andc.colno=columna and grado<=2);
commit work;
... .. . ...
begin work;
insert into update_stats
select t.tabid,c.colno,2
from systables t, syscolumns c, sysindexes i
where t.tabid=c.tabid
and c.tabid=i.tabid
and c.colno=i.part16
and t.tabid>99 and tabname not like 'sys%'
and t.tabtype="T"
and not exists (select 1 from update_stats where tabla=t.tabid andc.colno=columna and grado<=2);
commit work;
begin work;
insert into update_stats
select t.tabid,t.colno,2
from syscoldepend t
where
t.tabid>99
and not exists (select 1 from update_stats where tabla=t.tabid andt.colno=columna and grado<=2);
commit work;
unload to "upstatres.sql" delimiter ";"
select unique "select current from parametros ", "--0"
from systables t
where t.tabid=1
UNION
select unique "update statistics high for table "|| trim(tabname)||
"("||trim(colname)|| ")", "--1"
from systables t, syscolumns c, update_stats u
where t.tabid=c.tabid
and c.tabid=u.tabla
and c.colno=u.columna
and u.grado = 1
UNION
select unique "update statistics high for table "|| trim(tabname)||
"("||trim(colname)|| ")", "--2"
from systables t, syscolumns c, update_stats u
where t.tabid=c.tabid
and c.tabid=u.tabla
and c.colno=u.columna
and u.grado = 2
UNION
select unique "update statistics low for table ", "--8"
from systables t
where t.tabid=1
UNION
select unique "update statistics for procedure ", "--8"
from systables t
where t.tabid=1
UNION
select unique " select current from parametros ", "--9"
from systables t
where t.tabid=1
order by 2,1;
drop table update_stats;=========================================================================
Pablo F. Herrero
GIJON (Spain)
Sent via Deja.com http://www.deja.com/
Before you buy.