Thanks Peter for the information. I've submitted a but report with your
example and will see if I can work around it for now.
On 02/09/2011 7:19 PM, Peter Yuill wrote:
However, if the function relied on a value from the outer
query, something like:
SELECT a.id, a.value FROM outer a WHERE a.id IN (SELECT inner_id FROM
test_function(a.value) )
then I think the database will need to evaluate the function for each
row returned as the results.
Except that
a) The plan showed the query was a PK lookup on the outer table, so it
recognised that there was no correlation (the plan would have shown a
full table scan for a correlated subquery)
b) When I tried running a correlated function parameter a parse
exception was thrown
I think this definitely a bug.
I tried making the test function you provided a DETERMINISTIC
function. I'm not sure this is the correct use of deterministic, but
by doing so the function is called only once (not once for each row
returned) and some performance improvements are had:
Declaring a function DETERMINISTIC means that the result is expected to
be 'determined' only by the function parameters, so the first result for
each parameter set is cached. An MBR_INTERSECTS would then always return
the same values for a given bounding rectangle, no matter what changes
were made to the underlying spatial data. This would be fine for a
read-only database, but a serious error for a writeable database.
Maybe I'll just have to settle with re-writing my queries and avoiding
the use of IN.
For the time being I think that is the best plan.
Regards,
Peter
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
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.
