Rado,

> > 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.
>
> I only need to know how can someone force PLAN. Is this for
> future use only? I'm using stored procedures also but this
> was not my question.

Yes, in many situations you can force the engine to use your own plan, but
sometimes the optimizer decides that it knows what you want better than you
and ignores your attempts. The outer join thing is exactly this case.

> > Just as an aside, what do you do with the million or so records
> > you get back?
>
> I don't ever get back million records. I just would like to get
> answer on how to use PLAN in select because IB or FB sometimes
> doesn't use index it should. Will this improve in FB2?

I hope so. But it's quite hard to determine whether an index should be used
or not. For example, with left outer join often it takes less time to read
all rows in the natural order and sort them afterwards, but sometimes the
index navigation is much better way to go. Unfortunately, the optimizer is
not clever enough to choose the best algorithm and always use the former one
in this case.

BTW, how many rows do you have in your right table for every row in the left
one (i.e. what type of relationship exists between those two tables)?

Cheers,
Dmitry