Re: IDS Optimizer Query Plan, declaring and naming indexes

Yea, because depending on the data distribution, the optimizer may decide it is easier to scan the table than to use the index. You have update stats high, so you'd think the optimizer would make the correct decision, but if the query is slower, I guess it didn't. You can force the use of the index with an optimizer directive. -----Original Message----- From: NCC1701BOB@gmail.com [mailto:NCC1701BOB@gmail.com] Sent: Friday, June 15, 2007 10:44 AM To: informix-list@iiug.org Subject: Re: IDS Optimizer Query Plan, declaring and naming indexes On Jun 15, 9:00 am, "Obnoxio The Clown" <obno...@serendipita.com> wrote: > NCC1701...@gmail.com said: > > > On Jun 15, 8:50 am, Clive Eisen <c...@serendipita.com> wrote: > >> NCC1701...@gmail.com wrote: > >> > I'm seeing differences in performance for the same SQL being executed > >> > on two slightly different databases. Using Set Explain, the difference > >> > is when database1 exectues part of the WHERE, it goes down the Index > >> > Path for that particular table and it's fast. On database2, the query > >> > plan does a Sequenial Scan of that particular table instead of the > >> > Index Path. This is IDS 9.4. > > >> When and how have you run update statistics on either database? > > > I'm not sure what you mean by "how", it was run yesterday. I'm a > > developer and our DBA is assisting in this and he ran the update > > statistics through SQL Editor. > > I think he means "which type of statistics did he get: high, medium or low? > -- > Bye now, > Obnoxio > > "I'm astonished anyone pays real money for this crap." > -- Cosmo > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. "Do you have the same data in both tables ? Do both tables have good statistics in sysdistrib ?" Both tables have about the same number of rows, but not the same data exactly. I don't know about the sysdistrib. I'll try to look and have our DBA look when he gets in. It was update statistics on HIGH. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list