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 ...)
as
declare variable emp_id;
declare variable i integer;
begin
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;
suspend;
end
if (i = 0) then suspend;
end
end

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



Regards,

Ann
www.ibphoenix.com
We have answers.