At 12:52 AM 10/08/2006, yoav112003 wrote:

>I am trying to run the following query (FB 2 RC3):
>
>SELECT distinct ROWID FROM events
>WHERE events.ROWID IN (
>
>SELECT DISTINCT DESTINATION.EVENTID FROM DESTINATION
>WHERE ( (DESTINATION.IP ='158.125.73.220'))
>)
>
>The default plan that is used is:
>PLAN SORT ((DESTINATION INDEX (IDX_DESTINATION2, IDX_DESTINATION1)))
>PLAN SORT ((SEAM_EVENT NATURAL))
>
>where IDX_DESTINATION2, IDX_DESTINATION1 are indexes on
>DESTINATION.EVENTID and DESTINATION.IP respectively.
>
>This plan is very ineffecient since the plan does not rely on the
>index for events.ROWID. When I try to use a customized plan with this
>index, FB replies that the index cannot be used in this query.
>
>Is there anything else that can be done (except for using INNER JOIN)?
>Why can't FB use the indexes on the events table?

Because of the DISTINCTs. It's pointless having the DISTINCT in the
subquery and it is costing you a lot of unnecessary time and
resource, besides preventing the use of the index.

Replace with this:

SELECT distinct ROWID FROM events
WHERE EXISTS (
SELECT 1 FROM DESTINATION
WHERE DESTINATION.ROWID = events.ROWID
AND DESTINATION.IP ='158.125.73.220'
)

And, if events.ROWID is unique, take out the DISTINCT there as well.

./heLen