[email protected] (Dag H. Wanvik) writes:
> Bryan Pendleton <[email protected]> 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
