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! > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/LCE9guMgI8UJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
