Subject Re: Subqueries and indexes
Author yoav112003
Is there someway to control how the optimizer rewrites the queries
(i.e custom plans) ? or at least get more information about the chosen

--- In, Daniel Rail <daniel@...> wrote:
> Hi,
> At August 10, 2006, 6:48 AM, yoav112003 wrote:
> > Shouldn't the 'WHERE events.ROWID IN ...' clause be a hint to use the
> > index on this field?
> Because the optimizer would've most likely rewritten your query to
> what Milan posted. Especially, since you said that both queries had
> the same performance.
> > As you can read in my reply to Milan, I tried to use a custom plan
> > with this query which used this index, but I got an error message.
> And, the error is valid, since the optimizer is using a query
> structured like the one Milan posted, and an index on events.ROWID
> can't be used.
> > The query suggested by Milan and Helen have the same performance as
> > the my query, my guess is because they too require a full table scan
> > on the events table.
> > It seems that the best solution is to use an INNER JOIN query if
> > possible, but I am still curious to know the reason the ROWID index
> > cannot be used.
> If using INNER JOIN yields better performance, use it.
> --
> Best regards,
> Daniel Rail
> Senior Software Developer
> ACCRA Consultants Inc. (
> ACCRA Med Software Inc. (