From: "Daniel A. Morgan" <dmorgan@exesolutions.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Optimizing a query using indexes
Date: Tue, 05 Jun 2001 22:59:46 -0700
Organization: EXE
Message-ID: <3B1DC6D2.7A108CAB@exesolutions.com>
X-Mailer: Mozilla 4.7 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
References: <0mXS6.2191$QW4.90151@ozemail.com.au>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Cache-Post-Path: yabetcha.drizzle.com!unknown@ava25.drizzle.com
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
X-Complaints-To: newsabuse@supernews.com
Lines: 95


Christopher Latta wrote:

> 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

The first step is to drop the view and just incorporate the view's SQL into your
query.

Daniel A. Morgan