Re: Optimizer ODS 7.23

Frido van Orden <FridoO@faapartners.com> wrote: :We have performance problems with the following statement (a.o.) : :SELECT f.ind_glob_db_access, cf.dml_action :FROM functionx f, OUTER col_funcx cf :WHERE f.sys_nm = cf.sys_nm AND f.func_cd = cf.func_cd : AND f.sys_nm = "FAAIX" AND f.func_cd = "API_AFD_ST" AND cf.rt_nm = :"AFDELING" and cf.col_nm IS NULL AND cf.dml_action = "I" : :Unique indexes are on functionx(sys_nm, func_cd) and col_funcx(sys_nm, :rt_nm, col_nm, func_cd, dml_action) :Duplicate index is on col_funcx(sys_nm, func_cd) : :Set explain gives an estimated cost of 2, using 2 index paths and a hash :join A hash join seems a little strange. Have you run update statistics properly? Please post the output of the set explain and the size of each table. How many duplicates are there typically in the duplicate index you mention? That index should normally be used for the join. How many rows are typically eliminated by the references to the col_funcx (cf) table in the where clause? Another issue altogether, but hopefully you are aware of how your where part refering to values in the col_funcx (cf) table work. They can be viewed logically as creating a subset of this table before the outer join is done. This should however not be how the actual join is executed by the engine. :If the OUTER is removed, set explain gives an estimated cost of 3, using :2 index paths : :............ :HOWEVER :............ : :The query without the OUTER is many times faster than the one with it. That is not unpresedented, but should probably not be an issue here. :Possible causes: :* the optimizer can't deal with OUTER joins :* the optimizer can't deal with NULL restrictions on an index column *You have not run all the update statistics that is required :System is ODS 7.23 on an NT server (2x PPro 200) : :Anyone got a hint? Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org