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/-/U2XC9mU4E-8J.
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.
