Re: Performance of Star-Join Queries / Optimizer Hints

[Steve McLeod]

To clarify: are you asking specifically about the query you listed? Or are you asking about the more general case, where you'd also have a WHERE clause in the query?

On Monday, 3 December 2012 18:26:50 UTC+8, Eric Harney wrote:

Hi,

it seems that h2 has trouble with finding the ideal join order for star-join queries - e.g. stuff like

select count(*)

from vf_drugprescription

                natural join rh_doctor_location

                natural join rh_doctor_medsection

                natural join rh_drug_atc

                natural join rh_drug_generic

                natural join rh_drugstore

                natural join rh_insurant

                natural join rh_insurer

                natural join rh_time;

where vf_druprescription has a foreign key of every of the joined rh_tables, and the rh_tables themselves have no foreign keys. (vf_ druprescription has 1.7m entries, the rh_tables have 1k - 50k entries each) - h2's cache is set to 512MB, so it should fit the queried data comfortably.

Oracle 11g R2 manages to run this query in ~70s, with H2 I stopped the execution after about 5 minutes. Using explain I can see that the join order is quite different from the one Oracle picked..

However I'm still interested in using h2 for my application, since it has some nice features that oracle doesn't provide (like unlimited column/table name length, pure-java pl/sql, etc.). Is there any way of setting an explicit join order in H2 or allowing the query optimizer more run-time (Oracles optimizer runs for a good 60s whereas h2's returns immediately)?

Thanks!