Re: Question on Update Statistics
[This followup was posted to comp.databases.informix and a copy was sent
to the cited author.]
In article <19971107041400.XAA11881@ladder01.news.aol.com>, kponn@aol.com
says...
> Hi
>
> If I want to run update statistics on busy tables, can I do the following:
>
> 1. select tabname,iswrites,isreads from sysptprof order by 1,2
> 2. Then look at indexes for each table
> 3. Update statistics (high or medium) on that table or on column
>
> or
>
> Just do steps 1 and 3
>
> or
>
> ???
>
> Thanks in advance for your comments
>
> Keith
>
I use the following to produce the update status I hope this is useful
{
$Source: /proj/gl/Warehouse/RCS/updstats.sql,v $
$Author: gladmin $
$Date: 97/08/25 09:18:35 $
$Revision: 1.2 $
$Locker: $
-------------------------------------------------------------------------
--
$Log: updstats.sql,v $
Revision 1.2 97/08/25 09:18:35 09:18:35 gladmin (Michael Erickson)
Move Update Stats of Procedures to the end
Revision 1.1 97/08/22 09:04:47 09:04:47 gladmin (Michael Erickson)
Initial revision
}
create temp table work (
order integer,
tabname char(18),
seq integer,
update_stmnt varchar(255))
with no log in rootdbs;
create temp table tab (
tabid integer,
tabname char(18))
with no log in rootdbs;
insert into tab
select tabid,tabname
from systables
where tabid > 99
and tabtype = "T";
-- insert into work values (
-- 0,
-- "",
-- 0,
-- "set isolation to dirty read");
insert into work values (
0,
"",
1,"set pdqpriority 25");
insert into work
select 99, "", 0, "update statistics for procedure " || trim(procname)
from sysprocedures;
insert into work
select 1,
tabname,
1,
"update statistics medium for table " || trim(tabname)
from tab;
insert into work
select distinct
1,
tabname,
2,"update statistics high for table " || trim(tabname) ||
" (" || trim(c.colname) || ")"
from tab,
sysindexes i,
syscolumns c
where i.tabid = tab.tabid
and c.tabid = tab.tabid
and c.colno = i.part1;
-- insert into work
-- select distinct
-- 1,
-- tabname,
-- 3,
-- "update statistics low for table " || trim(tabname) ||
-- " (" || trim(c.colname) || ")"
-- from tab,
-- sysindexes i,
-- syscolumns c
-- where i.tabid = tab.tabid
-- and c.tabid = tab.tabid
-- and ( c.colno = i.part2
-- or c.colno = i.part3
-- or c.colno = i.part4
-- or c.colno = i.part5
-- or c.colno = i.part6
-- or c.colno = i.part7
-- or c.colno = i.part8
-- or c.colno = i.part9
-- or c.colno = i.part10
-- or c.colno = i.part11
-- or c.colno = i.part12
-- or c.colno = i.part13
-- or c.colno = i.part14
-- or c.colno = i.part15
-- or c.colno = i.part16);
create cluster index work_ndx on work(order, tabname, seq);
--unload to "/DBTEMP/reorg/update_statistics.sql" delimiter ";"
unload to "update_statistics.sql" delimiter ";"
select update_stmnt from work;
drop table work;
drop table tab;
Michael J. Erickson