Re: indexes and SPs

On Mon, 14 Apr 1997 17:19:34 +0200, "Mark D. Stock" <marks@west.co.za> wrote: >Oscar Goldes wrote: >> >> During optimization of a database, I am creating new indexes to try to lower >> response time. >> two related questions: >> >> 1. do I need to recompile the stored procedures that access the tables in >> which the new indexes are created to take advantage of the new indexes? or >> are they used as soon as they exist? > >They should use them as soon as they exist, providing they WANT to. UPDATE STATISTICS FOR procname; Also good when the table size (# rows) has changed. This will update sysprocplan which does NOT automatically get updated. > >> 2. Is it possible to use "set explain on" inside a storep procedure to see >> the search plan? If so, how (I have not been able to do this) > >I thought it was, and the syntax is accepted and it runs...... >But in version 5.x it just produces an empty sqexplain.out file, and >in version 7.2x it goes one better and produces a file with a LF in it. > >So I would say you have discovered a "feature". Speak to Informix >tech support. > >In the mean time, just cut the SQL out and run it in dbaccess with >SET EXPLAIN ON.> >Hope that helps, >-- > Mark. > > +------------------------------------------------------------------+ > |Mark D. Stock - The West Solutions Group http://www.west.co.za | > | The Informix FAQ is at http://www.iiug.org | > |mailto:marks@west.co.za +-----------------------------------------+ > |Tel: +27 11 803 2151 |If it doesn't work, force it! | > |Fax: +27 11 803 2189 |If it breaks, it needed replacing anyway!| > |Cell: +27 83 250 2325 |Well, that's how I code anyway! | > +------------------------+-----------------------------------------+ > Chris Burton cburton@gte.net