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...@xxxxxxxxx> wrote:
fandelau wrote:
On Dec 9 2009, 2:53 pm, Vagner <pontes.vag...@xxxxxxxxx> 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...@xxxxxxxxx> 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
.

Relevant Pages