Re: Horrible Query Performance, Simple Query -- Performance Tuning Help Needed

If it were me, I would try changing the where clause to:

where  x.application_id=1
  and  x.document_category_id=5
  and  D.REPORT_TYPE_NCID = x.report_type_ncid
  and  E.EVENT_ID=D.EVENT_ID
  and  E.UNIT_NUMBER= 232411

make a non-unique index on documents with event_id,report_type_ncid and

add event_x3 non-unique index unit_number,event_id. It is currently only using
the first segment of the event_x1 index and is looks like it is throwing most of
those out because of the selectivity. This allows for an index_only scan with out
having to go to the tables to "throw out" non-selected rows.

Even though it is using the application_document_unique index, it is last in the
order and really should be first.

Some times there are ways to "influence" this optimizer by doubling a portion of the
where clause:

example:

where  x.application_id=1
  and  x.document_category_id=5
  and  D.REPORT_TYPE_NCID = x.report_type_ncid
  and  D.REPORT_TYPE_NCID = x.report_type_ncid
  and  E.EVENT_ID=D.EVENT_ID
  and  E.UNIT_NUMBER= 232411

This causes the optimizer to say "hmmmmmm, maybe he really did want this value evaluated" Received on Thu Apr 20 2006 - 09:08:12 CDT