db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: (DERBY-4908) Instability in CheckConstraintTest.testBuiltInFunctions
Date Fri, 19 Nov 2010 22:29:07 GMT
dag.wanvik@oracle.com (Dag H. Wanvik) writes:

> Bryan Pendleton <bpendleton.derby@gmail.com> writes:
> <good explanation of optimizer workings>
> Doesn't this still leave the question why the test is unstable?: What
> could make the optimizer choice vary from test run to test run? I can
> think of the maximum time the optimizer would spend before it gives up
> looking for further plans, and that this could lead to different choice
> depending on machine performance, load etc. Does this test involve a
> large join? 

I don't think the join is large, but the query that gets the unexpected
result is indeed a join:

        rs = st.executeQuery(
            " select  c.type from "
            + "sys.sysconstraints c, sys.systables t where "
            + "c.tableid = t.tableid and tablename='T1'");

I'm not 100% sure the failure was caused by wrong ordering, though,
since assertFullResultSet only reports the first mismatch it
finds. Perhaps it would be a good improvement to get assertFullResultSet
to dump the full result on a mismatch?

> If not, what else could make the optimizer choice non-predictable?

I think the memory footprint and the available memory are also used to
determine the cost of a hash scan (if it doesn't fit in main memory, it
will be considered more expensive).

Knut Anders

View raw message