Re: indexes and SPs

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. > 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! | +------------------------+-----------------------------------------+