Ineffecient Query 1

[cduncan]

This query brings back 31000 rows in 5 minutes... Does ANYONE know how I can get a faster response back by tweaking the SQL??? We've indexed the tables also.


SELECT BORG.CUSTOMER.CUSTOMER_LAST_NAME,
BORG.CUSTOMER.CUSTOMER_FIRST_NAME,
BORG.CUSTOMER.LICENSE,
BORG.CUSTOMER_RECORD.ID,
BORG.CUSTOMER_RECORD.TECHNICIAN_ID,
BORG.CUSTOMER_RECORD.PRIM_PROVIDER_ID,
BORG.CUSTOMER_RECORD.SECOND_PROVIDER_ID,
BORG.CUSTOMER.ID,
BORG.PAT_UNIT.INCIDENT_NUMBER,
BORG.CUSTOMER_RECORD.INCIDENT_DATE,
BORG.CUSTOMER_RECORD.NUMBER,
BORG.CUSTOMER_RECORD.STATUS,
BORG.CUSTOMER_RECORD.UNIT_NOTIFIED_DATE,
BORG.CUSTOMER_RECORD.EN_ROUTE_TIME,
BORG.CUSTOMER_RECORD.LAST_UPDATE,
BORG.CUSTOMER_RECORD.LAST_UPDATER_ID,
BORG.CUSTOMER.CUSTOMER_SSN,
BORG.RECORD_IDENTIFIER.TYPE,
BORG.CUSTOMER.CUSTOMER_BIRTHDATE,
BORG.CUSTOMER.ZIP,
BORG.CUSTOMER.MIDDLE_INITIAL,
BORG.CUSTOMER_RECORD.IS_FINALIZED
FROM BORG.CUSTOMER_RECORD,
BORG.record_identifier,
BORG.units,
BORG.pat_unit,
BORG.CUSTOMER
WHERE BORG.CUSTOMER_record.PRIM_PROVIDER_ID =
x'20020307224217364715000000'
and
BORG.CUSTOMER_RECORD.ID = BORG.RECORD_IDENTIFIER.RECORD_ID and
BORG.RECORD_IDENTIFIER.ID = BORG.UNITS.IDENTIFIER_ID AND
BORG.UNITS.ID = BORG.PAT_UNIT.OWNER_ID AND
BORG.RECORD_IDENTIFIER.ID = BORG.CUSTOMER.IDENTIFIER_ID and
BORG.record_identifier.type = 0

 

[notadba]

At a pinch you could try getting rid of BORG.RECORD_IDENTIFIER.TYPE from your select statement since you are filtering on one type anyway. Since this is the only column you are taking from that table, you could change that to an an 'exists' subquery.

Hope that helps.