I have a poorly performing query that looks something like....
select x.name, x.title, x.value
from
(select a.name as name, b.book_title as title, c.cost as value from ......) x
where
exists (select ‘found_it’ from get_jobs(x.name) j where j.job = ‘carpenter’);
I did it this way because I was hoping that it would generate all the records in the... (select a.name as name, b.book_title as title, c.cost as value from ......) x first, and then run x.name into the get_jobs stored procedure later. In fact, running... (select a.name as name, b.book_title as title, c.cost as value from ......) x alone runs fast enough, and then running each of the returned x.name values through get_jobs manually runs fast too. But “explain” seems to indicate that it’s “relocating” that get_jobs stored procedure call inside the (select a.name as name, b.book_title as title, c.cost as value from ......) x , which gives terrible performance.
Is there a way I can force it to get the results from (select a.name as name, b.book_title as title, c.cost as value from ......) x before runing into the stored procedure?
BTW, what’s the formal name for the (select a.name as name, b.book_title as title, c.cost as value from ......) x piece of a query like this?
Thanks
-dave