Hello. Execution of OUTER joins cannot swap it sides in H2, H2 does not have hash joins yet. That's why only [A, J, X] and [J, X, A] plans are considered. For LEFT OUTER join H2 iterates over left side and lookups right side. Index on PROP(ID, OWNER_PK) may be wanted here if there are many rows with the same OWNER_PK but different ID value.
I don't know, however, why H2 evaluates the cost of [J, X, A] as infinite (with empty tables). It may be a bug in cost calculation (H2 definitely have some). Also I'm not sure why [J, A, X] is not considered (with inner join between), may be it's not really valid in H2 for the same reason, or may be it's just a missing feature in optimizer. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
