Re: script or spl for update statistics
In article <5ujprh$mcu@cssun.mathcs.emory.edu>,
zeljan@laus.hr (Zeljan Silje) wrote:
>
> Hi,
>
> in Informix Guide To SQL Syntax there is recommendation for updating
> statistics:
> 1) run update statistics with distributions only
^^^^^^
medium, and for all tables.
> 2) run update statistics high for columns with indexes
^^^^^^
for columns that head indexes.
> 3) run update statistics low for multicolumn indexes
^^^^^^^^^
for other index columns not in 2.
>
> I would like to do recommended update statistics regularly (via cron).
> Does anybody has SPL or script (or anything convenient) that is able to
> do such a update.
I have an Informix-4gl program that generates an update statistics script
for version 7.x databases. It goes a little bit beyond the
recommendations in the SQL manuals by also updating high second index
columns if the first column is also the first column of another index. I
have found this to make a big difference in certain situations. The
source is included below for those who want to try it - it's fast and
gives excellent results.
----------------------------------------------------------------------
John H. Frantz Power-4gl: Extending Informix-4gl
frantz@centrum.is http://www.rl.is/~john/pow4gl.html
I4gl update statistics generator follows...
define
txt char(2000),
ndba char(16),
ntid integer,
ntab char(64),
ncno integer,
ncol char(64),
i,j integer
main
-------------------------------------------------
-- Select database and declare cursors.
-------------------------------------------------
let ndba = arg_val(1) clipped
if length(ndba) = 0 then let ndba = fgl_getenv("DATABASE") end if
database ndba
whenever error continue
let txt = "select unique tabid,tabname from systables where tabtype='T'"
let ntab = arg_val(2) clipped
if length(ntab) > 0 then
let txt = txt clipped," and tabname='",ntab clipped,"'"
end if
let txt = txt clipped," order by tabname"
prepare ptab from txt
if sqlca.sqlcode <> 0 then display "error prep ptab" exit program end if
declare tab cursor for ptab
if sqlca.sqlcode <> 0 then display "error decl tab" exit program end if
let txt = "select unique colno,colname from syscolumns where tabid=?"
prepare pcol from txt
if sqlca.sqlcode <> 0 then display "error prep pcol" exit program end if
declare col cursor for pcol
if sqlca.sqlcode <> 0 then display "error decl col" exit program end if
whenever error stop
foreach tab into ntid,ntab
--------------------------------------------------------------
-- Update whole table for medium distributions only.
--------------------------------------------------------------
let txt = "medium for table ",ntab clipped," distributions only"
call updstat()
--------------------------------------------------------------
-- Handle all columns that qualify for high distribution.
-- These are columns that are heads of indexes or second index
-- columns that distinguish the index from other indexes.
--------------------------------------------------------------
foreach col using ntid into ncno,ncol
select count(*) into i from sysindexes
where tabid = ntid and part1 = ncno
if i = 0 then
select count(*) into i from sysindexes si
where tabid = ntid and part2 = ncno
and 1<(select count(*) from sysindexes
where tabid = si.tabid and part1 = si.part1)
end if
if i <> 0 then
let txt = "high for table ",ntab clipped," (",ncol clipped,")"
call updstat()
end if
end foreach
-----------------------------------------------------------------------
-- Handle all index columns that don't qualify for high distribution.
-- These are index columns that are not heads of indexes and are not
-- second index columns that distinguish the index from other indexes.
-----------------------------------------------------------------------
let j = 0
foreach col using ntid into ncno,ncol
select count(*) into i from sysindexes where tabid = ntid
and (part2 = ncno or part3 = ncno or part4 = ncno
or part5 = ncno or part6 = ncno or part7 = ncno or part8 = ncno
or part9 = ncno or part10 = ncno or part11 = ncno or part12 = ncno
or part13 = ncno or part14 = ncno or part15 = ncno or part16 = ncno)
if i > 0 then
select count(*) into i from sysindexes
where tabid = ntid and part1 = ncno
if i = 0 then
select count(*) into i from sysindexes si
where tabid = ntid and part2 = ncno
and 1<(select count(*) from sysindexes
where tabid = si.tabid and part1 = si.part1)
end if
if i = 0 then
if j = 0 then let txt = "low for table ",ntab clipped," (",ncol
else let txt = txt clipped,",",ncol
end if
let j = j + 1
end if
end if
end foreach
if j > 0 then
let txt = txt clipped,")"
call updstat()
end if
end foreach
end main
################################################################################
# Output an update statistics statement.
################################################################################
function updstat()
define o char(80)
----------------------------------------------------------------------
-- Statement to generate time retrieval information (not necessary).
----------------------------------------------------------------------
if length(txt) > 52 then let o = txt[1,49],"..."
else let o = txt
end if
display "select current year to second,'",o clipped
,"' from systables where tabid=1;"
----------------------------------------------------------------------
-- Actual update statistics statement.
----------------------------------------------------------------------
display "update statistics ",txt clipped,";"
end function
-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet