db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: [jira] Commented: (DERBY-1109) lang/predicatePushdown.sql fails with wsdd5.6
Date Tue, 14 Mar 2006 18:37:50 GMT
Hopefully someone with some language expertise can comment, I am not 
sure how query processing is doing the "distinct" scan result set.  I
know that store provides a way to return a result set as a hash table
and that hash table is just the standard java provided hash table for
whatever is the current jvm, with special support for duplicate items - 
if this is what "Distinct Scan ResultSet" is using then it is expected 
that ordering may differ
based on OS/JVM.

It is nice to understand what is going on, but in general I would always
lean toward adding an order by for any test that needs to guarantee a
specific ordering of results to be successful.  Note that even just
inserting rows into a store heap and then opening a scan on them does
not guarantee a specific order now or in the future -- for instance
if there is post commit space reclamation going on later inserts may
end up on pages earlier than previous inserts.

When we first verified the system against the ibm wsd jvm we added a 
number of order by's to the queries because of the different sorting
in hash algorithm of the jvm.

Army wrote:
> Mike Matrigali wrote:
>> Is there any hash joins in this plan? That is the standard reason to 
>> see different ordering on different jvm/machine if plans look the same.
> Ah, that makes sense--thanks for the suggestion, Mike.
> I checked the query plan and there aren't any hash joins per se, but the 
> info that is printed for the Distinct Scans does include a "hash table 
> size", ex:
>     Left result set:
>         Distinct Scan ResultSet for T1 using index 
> 140d4147-0109-f966-69cd-00000b4f213f at read committed isolation level 
> using instantaneous share row locking:
>         Number of opens = 1
> ==>        Hash table size = 5
>         Distinct columns are column numbers (0,1)
>         Rows seen = 5
>         Rows filtered = 0
>             constructor time (milliseconds) = 0
>             open time (milliseconds) = 0
>             next time (milliseconds) = 0
>             close time (milliseconds) = 0
>             optimizer estimated row count:           10.00
>             optimizer estimated cost:           35.34
>             next time in milliseconds/row = 0
> Is it possible that the disinct scan is using an underlying hash table 
> that could in turn cause different ordering on differing JVMs?  Or does 
> that only apply to explicit hash joins?
> Thanks again for the feedback--I appreciate it!
> Army

View raw message