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

From: WhiteDog <cchenoweth56_at_msn.com>
Date: 19 Apr 2006 11:36:47 -0700
Message-ID: <1145471807.017854.322800@i40g2000cwc.googlegroups.com>

Here are the indexes:

ebiz_application_document-

APPLICATION_DOCUMENT_PK (Unique)

       -Application_Documnent_ID
APPLICATION_DOCUMENT_UNIQUE (Unique)

• Application_id
• Document_Category_id
• Report_Type_NCID REPORT_TYPE_IDX (NON UNIQUE)
• Report_Type_NCID

Event

EVENT_PK (Unique)

• EVENT_ID Event_Subtype_NCID_X1 (Non Unique)
• EVENT_SUBTYPE_NCID EVENT_X1 (Non Unique)
• UNIT_NUMBER
• EVENT_TYPE_NCID
• EVENT_SUBTYPE_NCID EVENT_X2 (Non Unique)
• MMM_ENCOUNTER_NUMBER

Document

DICTATING_CLINICIAN_NCID_X1 (Non Unique)

• DICTATING_CLINICIAN_NCID DOCUMENT_PK (Unique)
• EVENT_ID
• Document_ID REPORT_TYPE_NCID_X1 (Non Unique)
• REPORT_TYPE_NCID

Also, here is the Explain Plan:

Operation,Object,COST,CARDINALITY
SELECT STATEMENT (),,60,162
 SORT (ORDER BY),,60,162
  NESTED LOOPS (),,59,162
   NESTED LOOPS (),,58,386
    TABLE ACCESS (BY INDEX ROW,EVENT,19,386      INDEX (RANGE SCAN),EVENT_X1,4,386
    TABLE ACCESS (BY INDEX ROW,DOCUMENT,1,1      INDEX (RANGE SCAN),DOCUMENT_PK,1,1
   INDEX (UNIQUE SCAN),APPLICATION_DOCUMENT_UNIQUE,0,1 Received on Wed Apr 19 2006 - 13:36:47 CDT