Re: Force index usage

In article <3385bb63.23228280@gate.idg.no>, Nils.Myklebust@idg.no wrote: > ... > I am much more doubtfull to this "feature" request. In Oracle there > are such options. If you look at the resulting SQL it's something I > for sure wouldn't want to have in my application. It would be a total > maintenance nightmare as it indeed is in some Oracle applications as I > understand it. This far it's an advantage for Informix that we avoide > this. > ... I agree fully with Nils's arguments. If a DBMS were perfect you wouldn't even have to create indexes which are in effect performance tuning features. We may have to live with that, for now, but let's not add to the maintenance havoc by having to mention the names of those indexes in our code. Theoretically update statistics should work and in my experience has worked quite well. In that spirit, I'm including a 4gl program that generates an update statistics script for any database. It's based mostly on recommendations from the Informix 7.2 manuals with some deviation, and some ideas came from an article in Tim Schaefers wonderful little e-zine (http://www.inxutil.com). The basic idea is to update every column at least to medium distributions, and any column which distinguishes an index from any other index is updated to high distributions. Using this principle, any column that heads an index is updated high, and if there are two indexes that begin with that column then the second column(s) of the index(es) are also updated high. Here's the source code, unelegantly written, but it works where no other generic update statistic scripts have worked before that I've tried anyway. Brought to you by the makers of Power-4gl (http://www.rl.is/~john/pow4gl.html): ################################################################################ # Update statistics script generator. # Generates script for whole database to stdout. ################################################################################ define out char(400), 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" let ntab = arg_val(2) clipped if length(ntab) > 0 then let txt = txt clipped," where 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 --------------------------------------------------------------------- -- Update statistics specifically for each table column. If the -- column is the first part of an index or the column is the -- second part of an index whose first part appears as the first -- part in more than one index, then the statistics are updated -- with high distributions. Otherwise, the statistics for the -- column are updated with medium distributions. Columns can -- be grouped and dealt with in a single command. --------------------------------------------------------------------- foreach tab into ntid,ntab -------------------------------------------------------------- -- Handle all columns that qualify for medium distribution. -------------------------------------------------------------- let j = 0 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 if j = 9 then call updstat() let j = 0 end if if j = 0 then let out = "medium for table ",ntab clipped," (",ncol else let out = out clipped,",",ncol end if let j = j + 1 end if end foreach if j > 0 then call updstat() end if -------------------------------------------------------------- -- Handle all columns that qualify for high distribution. -------------------------------------------------------------- let j = 0 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 else if j = 9 then call updstat() let j = 0 end if if j = 0 then let out = "high for table ",ntab clipped," (",ncol else let out = out clipped,",",ncol end if let j = j + 1 end if end foreach if j > 0 then call updstat() end if end foreach end main ################################################################################ # Output an update statistics statement (plus timing stuff). ################################################################################ function updstat() define o char(400) let out = out clipped,")" if length(out) > 52 then let o = out[1,49],"..." else let o = out end if display "select current year to second,'",o clipped ,"' from systables where tabid=1;" display "update statistics ",out clipped,";" end function -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet