Re: Hints in INFORMIX (Newbie)

Here's another hint. If the optimizer is making a bad choice, it's highly likely the statistics are non-existant, inaccurate, or inadequately detailed. Or the SQL is just plain garbage. Once in a great while you might legitimately need a hint, but it should be very rare. The feature is probably in there to appease Oradudes who can't believe you rarely if ever should need them. Remember, Oracle 8 was the first release where the Cost Based Optimizer was borderline fit to use. Informix has been putting in a lot of features to reduce resistance in porting apps written for Oracle to Informix, as well as to make it easier and quicker. greg manoj_sasidharan@my-deja.com wrote: > Hello Informix Gurus, > > How can we specify optimizer hints in Informix? > > Thanks > > MS > > Sent via Deja.com http://www.deja.com/ > Before you buy.

Greg, don't be so cynical! There are indeed situations where the Optimizer Directives are required and nothing you can do with UPDATE STATS will fix the problem in a reasonable way. One example: We have a load job that runs daily and at the beginning of each month loading new records into the database and updating the existing rows. The job is completely restartable and can also be used to update the rows when new data comes in or revisions are needed. The job will update the existing rows during every run except at the beginning of the month when a new monthly row is inserted so following the rule that if updates are the norm then try an update first and if it does not find a row perform the insert that is what the job does. However, at the beginning of the month there are no rows at all at first so the optimizer correctly selects an index based on the criteria beginning with the load month since this makes for a good filter when there are zero rows in the stats with the new month. When the run starts that's fine and the inserts progress quickly as the updates very quickly determine there are no rows to update. However, as the load progresses there are more and more rows that pass the month filter and that causes the failing updates to progressively slow down until the load is dragging. The tables are large enough that UPDATE STATISTICS takes nearly an hour to run properly and it would have to be run frequently during the job run to keep up. The only answer before hints was to drop or disable the offending index which would slow interactive queries during the load. Optimizer directives solved the problem with a simple {+ AVOID INDEX... directive. Yes Informix's optimizer is smarter that O's and directives are rarely needed compared with that environment where they are a fact of life and job. However, do not write them off as strictly a nod to porting needs. Art S. Kagel Greg wrote: > > Here's another hint. If the optimizer is making a bad choice, it's > highly likely the statistics are non-existant, inaccurate, or > inadequately detailed. Or the SQL is just plain garbage. Once in a > great while you might legitimately need a hint, but it should be very > rare. > > The feature is probably in there to appease Oradudes who can't believe > you rarely if ever should need them. Remember, Oracle 8 was the first > release where the Cost Based Optimizer was borderline fit to use. > > Informix has been putting in a lot of features to reduce resistance in > porting apps written for Oracle to Informix, as well as to make it > easier and quicker. > > greg > > manoj_sasidharan@my-deja.com wrote: > > > Hello Informix Gurus, > > > > How can we specify optimizer hints in Informix? > > > > Thanks > > > > MS > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy.

Agreed - I was generalizing. But I would argue your case is more the exception than the rule. That's a pretty long chain of circumstances that produces a good use for a directive. I think its fine that Informix is facing market reality and trying to reduce the obstacles between using Ifmx or Oracle, which unfortunately most people write to first (or SQL7 - ick). Microsoft and Oracle are proof positive that there's not much correlation between superior products and business success. Pretending to be above the fray hasn't worked out so well market share wise. It can only help. greg "Art S. Kagel" wrote: > Greg, don't be so cynical! There are indeed situations where the Optimizer > Directives are required and nothing you can do with UPDATE STATS will fix > the problem in a reasonable way. One example: > > We have a load job that runs daily and at the beginning of each month > loading new records into the database and updating the existing rows. The > job is completely restartable and can also be used to update the rows when > new data comes in or revisions are needed. The job will update the existing > rows during every run except at the beginning of the month when a new > monthly row is inserted so following the rule that if updates are the norm > then try an update first and if it does not find a row perform the insert > that is what the job does. However, at the beginning of the month there are > no rows at all at first so the optimizer correctly selects an index based > on the criteria beginning with the load month since this makes for a good > filter when there are zero rows in the stats with the new month. When the > run starts that's fine and the inserts progress quickly as the updates very > quickly determine there are no rows to update. However, as the load > progresses there are more and more rows that pass the month filter and that > causes the failing updates to progressively slow down until the load is > dragging. The tables are large enough that UPDATE STATISTICS takes nearly > an hour to run properly and it would have to be run frequently during the > job run to keep up. The only answer before hints was to drop or disable > the offending index which would slow interactive queries during the load. > > Optimizer directives solved the problem with a simple {+ AVOID INDEX... > directive. > > Yes Informix's optimizer is smarter that O's and directives are rarely > needed compared with that environment where they are a fact of life and job. > However, do not write them off as strictly a nod to porting needs. > > Art S. Kagel > > Greg wrote: > > > > Here's another hint. If the optimizer is making a bad choice, it's > > highly likely the statistics are non-existant, inaccurate, or > > inadequately detailed. Or the SQL is just plain garbage. Once in a > > great while you might legitimately need a hint, but it should be very > > rare. > > > > The feature is probably in there to appease Oradudes who can't believe > > you rarely if ever should need them. Remember, Oracle 8 was the first > > release where the Cost Based Optimizer was borderline fit to use. > > > > Informix has been putting in a lot of features to reduce resistance in > > porting apps written for Oracle to Informix, as well as to make it > > easier and quicker. > > > > greg > > > > manoj_sasidharan@my-deja.com wrote: > > > > > Hello Informix Gurus, > > > > > > How can we specify optimizer hints in Informix? > > > > > > Thanks > > > > > > MS > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy.