Re: Found a possible bug in IDS 11.x... and a nasty one at that... has anyone suffered this?

On Jan 28, 2:55 am, Fernando Nunes <domusonl...@gmail.com> wrote: > fandelau wrote: > > On Dec 9 2009, 2:53 pm, Vagner <pontes.vag...@gmail.com> wrote: > >> Hi, > > >> The RA_PAGES parameter is the same in version 11.10 and 11.50? > > Both instances are configured the same. > > >> What is the definition of your DBSPACE ? Did you try 8KB? > > >> This issue is interesting, where is the bootleneck? Do you have WSTATS > >> parameter enabled? Run your query  and sysmaster:sysseswts for your > >> session. > > >> Vagner > > I'll try it and post... > > > On Dec 7 2009, 4:50 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > > <SNIP> > >> But well... The PMR is progressing and the OP will be kept informed of > >> any conclusions. > > >> Regards > > > Indeed the PMR is progressing... this is what I got from IBM: the > > developer said that there is work being done regardles of this issue > > to, in this particular case, force the optimizer to choose a light > > scan instead of going for an Index Path. > > I asked the support engineer to ask the following to the developer: > > > "What escapes my understanding is why not an index path? I wish the > > developer could explain me why not. I know that light scans are fast, > > but I doubt that they would be faster than an index in a larger scale > > (few hundred/thousand out of a few billion records). As far as I > > understand B-tree indexing, all the null values would be eliminated > > from the index search, as it wouldn’t be in the list(s) being > > searched, leaving the balanced cardinality data to reach. That’s why > > when I count the indexed column, it finds the qualified index records > > and counts them within seconds. The issue, the way I see it, is > > somewhere between the record physical address retrieval from the index > > and the retrieval of the record itself from disk or buffer. > > > Now, I wouldn’t be stressing this issue so much if it wasn’t that the > > application development team set up a vanilla Oracle database, created > > the table without any bells or whistles, loaded the same data with the > > same indexes, and the data came back within seconds, having Oracle’s > > QO gone for an index path, according to my sources. Yes, I would like > > to see it with my own eyes too (I mean, Oracle outperforming > > Informix), and that’s why I’m in the process of setting up a test of > > my own. I’d be surprised if you guys don’t have an Oracle database > > laying around somewhere... I’m sure you could setup a test to see what > > I’m talking about. In consequence, another problem I have with this is > > that now there is that ominous cloud floating around... Should we > > migrate to Oracle? > > > Last but not least, the biggest reason why I’m pushing this issue is > > because this scenario (index on a column or columns with 80% of the > > values being null, and the other 20% with proper cardinality) is the > > norm in my environment." > > > Lets see what the developer has to say... I'll keep y'all posted. > > > Ramon. > > The issue here is not the number of nulls (although Oracle does not > index nulls, so the index will be much smaller). > The issue here is the physical layout of the data. The tests you're > making with Oracle should have this in consideration. > I wrote before, if you alter your Informix index to cluster, the query > becomes pretty fast, without changing anything else. > > I have created a test case that shows this and I've sent it to the > support team. I can try to push this a bit further... > What your situation shows is that we can optimize the way we do index > searches when we retrieve a reasonable large number of rows. The > performance gains will vary significantly accordingly to the physical > layout of the data, the hardware used etc. > But in any case, I disagree with the proposed solution of choosing a > full scan. That seems to be an easy and blind solution for a specific > situation/environment that can cause problems in others. I will transmit > this thoughts to R&D/support, but in the end it's their decision. > > Just please bare in mind that the physical layout of the data (and even > the block/page size) is very relevant in this situation. > > Thanks for reporting this and insisting. The software products in > general would be much better if customers do this more often. Of course > that should not be necessarily... Fernando, I hear you about the cluster index, but the problem is, in various degrees of performance degradation, with all 11 single-column indexes in the table. As far as physical layout, well, call me lazy, but it is a single 16K-page dbspace holding both the data and the index... again this particular table is not that large, only 13 million records, with a column size 402 bytes, and with 13 single-column indexes all on either integers or smallfloat columns. Anyway, thanks for your support. Hopefully they'll listen to you better than they do listen to me! :o) Ramón