Subject Re: [ib-support] Re: Can someone explain this PLAN !
Author Ann W. Harrison
At 11:12 PM 11/2/2001 +0000, radovan.antloga@... wrote:

>In my production database I have one table with
>more than 800.000 records and another with more than 500.000
>records. If I write query that uses PLAN with simple JOIN
>than query will give result records immediately but when
>PLAN uses SORT I can wait a lot depend on query.

You're not going to be able to convince the optimizer to do
something it doesn't understand and it doesn't understand
that it can use an index to order an outer join. It may sometime,
but it doesn't now.

What you can do is use a procedure like this:

create procedure xyz (returns ...)
declare variable emp_id;
declare variable i integer;
for select <list>, e.emp_id from employee e
order by e.last_name, e.first_name
into <return variables>, :emp_id
do begin
i = 0;
for select <list> from employee_project p
where p.emp_id = :emp_id
into <return variables>
do begin
i = i + 1;
if (i = 0) then suspend;

Just as an aside, what do you do with the million or so records
you get back?


We have answers.