Optimizing a query using indexes

From: Christopher Latta <nobody_at_nowhere.not>
Date: Tue, 5 Jun 2001 12:17:29 +1000
Message-ID: <0mXS6.2191$QW4.90151@ozemail.com.au>

I am currently trying to optimize a slow query that does an outer join of a table with a view. Currently, the query insists on not using the indexes I have set up for it.

I have a Diary table which has, among other fields, two date fields Start_dt and Notify, a text field ReminderFor being the code of the person for this Diary entry and another text field Fileid that may reference one of two external tables: Matter or ArchMatt; or the field is null. ArchMatt is used for archiving rows out of the Matters table when they are finished. Matter and ArchMatt are both uniquely indexed by Fileid. There is a view, AllMatters, joining Matter with ArchMatt which contains the Fileid and other fields required by reports which span both tables.

On the Diary table I have two indexes:
DiaryNotify indexing by ReminderFor and Notify DiaryStart indexing by ReminderFor and Start_dt

To get my Diary entries for a day, I do a query like this one: select D.*, M.ShortDescr
from Diary D, AllMatters M

where D.ReminderFor = 'CML'
  and D.Start_dt >= '01-MAY-2001'
  and D.Start_dt < '01-JUN-2001'
  and D.Fileid = M.Fileid(+)

order by D.Start_dt

The explain plan for this query comes back telling me that it is going to use the DiaryNotify index, rather than the DiaryStart index, even though I am selecting by both ReminderFor and Start_dt and ordering by Start_dt. Even if I include a hint to tell the query to use the DiaryStart index, it still uses DiaryNotify. If I change the Order By clause to include ReminderFor it will use the right index, which is understandable. Strangely, it also chooses the right index if I remove the outer join on the AllMatters view.

Why would the optimizer choose the wrong index?

At the moment I am thinking of including a new index by Start_dt then ReminderFor, because this gets chosen by the optimizer all the time if it exists.

Also, with the join on the view, the explain plan reports that it is going to do a full table access for both Matter and ArchMatt (which is expensive), even though they are both indexed by Fileid to reduce the overhead of this search.

Shouldn't I join on a view? Is there a better way to do this join? I have though about doing it like this:

select
D.ReminderFor, D.Start_dt, D.Descr, null as ShortDescr from Diary D

where D.ReminderFor = 'CML'
  and D.Start_dt >= '01-MAY-2001'
  and D.Start_dt < '01-JUN-2001'
  and D.Fileid is null

union
select
D.ReminderFor, D.Start_dt, D.Descr, M.ShortDescr from Diary D, Matter M

where D.ReminderFor = 'CML'
  and D.Start_dt >= '01-MAY-2001'
  and D.Start_dt < '01-JUN-2001'
  and D.Fileid is not null and D.Fileid = M.Fileid

union
select
D.ReminderFor, D.Start_dt, D.Descr, A.ShortDescr from Diary D, ArchMatt A

where D.ReminderFor = 'CML'
  and D.Start_dt >= '01-MAY-2001'
  and D.Start_dt < '01-JUN-2001'
  and D.Fileid is not null and D.Fileid = A.Fileid

order by 1, 2

This uses the Fileid indexes, but still uses the DiaryNotify index, even though it is ordered by ReminderFor and Start_dt. Still, it should be cheaper due to the use of the Fileid indexes.

I have also thought about building the fields from the Matter or ArchMatt dynamically within my program (I use Delphi, which makes this painless). However, for each Diary row with a non-null Fileid I will have to do a trip back to the database to retrieve the Matter fields, and another trip to retrieve the ArchMatt fields if the Matter retrieval is unsuccessful. These extra trips to the database will be small, but might outweigh the gain from not doing to do the join or union within the select.

Any comments appreciated.
Christopher Latta Received on Mon Jun 04 2001 - 21:17:29 CDT