Double check a query

[tburmeister]

I am a bit new to my poisition but for some reason it sometimes take a long time to pull a query from the db. Can I get some if to see if I can optimize my query any more?

SELECT c.udf1_c as "Merial ID", c.udf2_c as "Location ID", c.bus_name as "Practice Name", e.synopsis as " Program Name", d.field4 as "Software", d.field26 as "HSI", d.field27 as "Shared DB",
d.field28 as "Converted Pims", d.field29 as "Open 12 mo. or Less", d2.field1 as "Submission Type", d2.field4 as "Media Issue", c.state as "Clinic's State",
to_char(n.completed_date,'MM/DD/YYYY') as "Assigned Date"
FROM nxscuste c, nxstar_e e, nxsnotes n, nxsdatas d, nxsdatas d2
WHERE e.symptom_code = 'CC'
AND e.completed is null
AND d.type_code = 'ENROLL'
AND n.completed_date in (SELECT MAX(n2.completed_date) from nxsnotes n2 WHERE n2.id = n.id and n2.type = 'DS-BadMedia')
AND d2.type_code = 'RECMEDIA'
AND e.tar_num = d2.id
and e.tar_num = d.id
and e.tar_num = n.id
and c.cust_code = e.cust_code;

Thank you!

[gandolf989]

You should be able to generate an explain plan on your own, otherwise,
you are unqualified for your position. Here is an index, that might help.

create index nxsnotes_compdt_id ON nxsnotes ( completed_date, tar_num );

It helps to use SQL 99 joins, since doing so helps to document how the tables
are joined together, and sometimes results in better performance.

SET timing ON
SET linesize 150
set pagesize 50

SET autotrace ON
SELECT c.udf1_c   AS "Merial ID",           c.udf2_c   AS "Location ID", 
       c.bus_name AS "Practice Name",       e.synopsis AS " Program Name", 
       d.field4   AS "Software",            d.field26  AS "HSI", 
       d.field27  AS "Shared DB",           d.field28  AS "Converted Pims", 
       d.field29  AS "Open 12 mo. or Less", d2.field1  AS "Submission Type", 
       d2.field4  AS "Media Issue",         c.state    AS "Clinic's State", 
       to_char(n.completed_date,'MM/DD/YYYY') AS "Assigned Date"
  FROM nxscuste c
 INNER JOIN nxstar_e e
    ON c.cust_code = e.cust_code
 INNER JOIN nxsnotes n
    ON e.tar_num = n.id
 INNER JOIN nxsdatas d
    ON e.tar_num = d.id
 INNER JOIN nxsdatas d2
    ON e.tar_num = d2.id
 WHERE e.symptom_code = 'CC'
   AND e.completed IS NULL
   AND d.type_code = 'ENROLL'
   AND n.completed_date IN (SELECT MAX(n2.completed_date) 
                              FROM nxsnotes n2 
                             WHERE n2.ID = n.ID 
                               AND n2.TYPE = 'DS-BadMedia')
   AND d2.type_code = 'RECMEDIA';