Re: Bad plan when join on function

On 01/17/2011 02:03 AM, Zotov wrote:

> select c.id from OneRow c join abstract a on a.id=AsInteger(c.id)
>
> OneRow Contains only one row,
> abstract contains 22 953 500 rows
>
> AsInteger is simple function on Delphi
> it just return input value

Ok... there has to be some kind of misunderstanding, here. First of all,
if you're trying to cast a value to an integer, there are so many
built-in ways to do this, I can't imagine why you'd call a C function.
The most common for your example would be ::INT.

Second, you need to understand how the optimizer works. It doesn't know
what the function will return, so it has to apply the function to every
row in your 'abstract' table. You can get around this by applying an
index to your table with the result of your function, but to do that,
you'll have to mark your function as STABLE or IMMUTABLE instead of
VOLATILE.

Joining on the result of a function will always do this. The database
can't know what your function will return. If you can avoid using a
function in your join clause, do so.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas(at)peak6(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email