9.40 query optimiser problem

A client has IDS 9.40.FC1 on HP-UX with: OPTCOMPIND 0 # Nested loop joins preferred OPT_GOAL 0 # FIRST_ROWS UPDATE STATISTICS (no distributions) has been run. According to "sqexplain.out", queries involving several tables, with a fixed condition on one table for which an index exists and indexed joins to the other tables, will not start with the first table without optimizer directives, whereas this was fine in 9.30! Any ideas? I consider that this would be a bug in 9.40 if it cannot be made to work correctly without distributions. -- Regards, Doug Lawry www.douglawry.webhop.org

On Tue, 13 Jul 2004 10:58:44 -0400, Doug Lawry wrote: Sorry but the optimizer guys have been rewriting the optimizer logic from version to version since 5.4 days. If you do not follow the rules you cannot depend on the default behavior being the same from version to version. You can only depend on sane behavior from the optimizer if you create usable data distributions. If you find creating distributions painful, get my dostats utility which has many options to make this painless, easy, and to minimize the impact on the server and users. Dostats is part of the package utils2_ak. Art S. Kagel > A client has IDS 9.40.FC1 on HP-UX with: > > OPTCOMPIND 0 # Nested loop joins preferred OPT_GOAL 0 # FIRST_ROWS> > UPDATE STATISTICS (no distributions) has been run.> > According to "sqexplain.out", queries involving several tables, with a fixed > condition on one table for which an index exists and indexed joins to the > other tables, will not start with the first table without optimizer > directives, whereas this was fine in 9.30! Any ideas? I consider that this > would be a bug in 9.40 if it cannot be made to work correctly without > distributions. > > -- > Regards, > Doug Lawry > www.douglawry.webhop.org

"Doug Lawry" <lawry@nildram.co.uk> wrote in message news:cd0tb4$qev$1@nntp0.reith.bbc.co.uk... > A client has IDS 9.40.FC1 on HP-UX with: > > OPTCOMPIND 0 # Nested loop joins preferred > OPT_GOAL 0 # FIRST_ROWS> > UPDATE STATISTICS (no distributions) has been run.> > According to "sqexplain.out", queries involving several tables, with a fixed > condition on one table for which an index exists and indexed joins to the other > tables, will not start with the first table without optimizer directives, > whereas this was fine in 9.30! Any ideas? I consider that this would be a bug in > 9.40 if it cannot be made to work correctly without distributions. > Nope you need distributions.. > -- > Regards, > Doug Lawry > www.douglawry.webhop.org > >