SELECT b.*,
         party_type
  FROM   hz_parties party,
         (SELECT   b.contact_party_id,
                   max(a.lead_date) creation_date,
                   'LEADS' load_type
          FROM     as_sales_leads a,
                   as_sales_lead_contacts b
          WHERE    a.sales_lead_id = b.sales_lead_id
        AND      lead_date > SYSDATE -$DAY
          GROUP BY contact_party_id
          UNION
          SELECT   a.contact_party_id,
                   max(a.hist_date) creation_date,
                   'OPPS' load_type
          FROM     (SELECT b.contact_party_id,
                           to_date(a.attribute4, 'mm/dd/yyyy') hist_date
                    FROM   as_leads_all a,
                           as_lead_contacts_all b
                    WHERE  a.lead_id = b.lead_id
                    AND    a.attribute4 LIKE '__/__/____%'
                    UNION
                    SELECT b.contact_party_id,
                           to_date(a.attribute4, 'yyyy-mm-dd HH24:MI:SS') hist_date
                    FROM   as_leads_all a,
                           as_lead_contacts_all b
                    WHERE  a.lead_id = b.lead_id
                    AND    a.attribute4 LIKE '____-__-__%'
                    UNION
                    SELECT b.contact_party_id,
                           a.creation_date hist_date
                    FROM   as_leads_all a,
                           as_lead_contacts_all b
                    WHERE  a.lead_id = b.lead_id
                    AND    a.attribute4 IS NULL ) a
      WHERE  a.hist_date > SYSDATE -$DAY
          GROUP BY a.contact_party_id
          UNION
          SELECT   a.party_id contact_party_id,
                   max(a.hist_date) creation_date,
                   'QUOTES' load_type
          FROM     (SELECT party_id,
                           to_date(replace(quote_description || ' ', '.', '/'), 'dd/mm/yyyy HH24:MI:SS') hist_date
                    FROM   aso_quote_headers_all
                    WHERE  quote_description LIKE '__.__.____%'
                    UNION
                    SELECT party_id,
                           to_date(quote_description, 'mm/dd/yyyy') hist_date
                    FROM   aso_quote_headers_all
                    WHERE  quote_description LIKE '__/__/____%'
                    UNION
                    SELECT party_id,
                           to_date(quote_description, 'yyyy/mm/dd HH24:MI:SS') hist_date
                    FROM   aso_quote_headers_all
                    WHERE  quote_description LIKE '____/__/__%'
                    UNION
                    SELECT party_id,
                           creation_date hist_date
                    FROM   aso_quote_headers_all
                    WHERE  quote_description IS NULL ) a
        WHERE    a.hist_date > SYSDATE -$DAY
          GROUP BY a.party_id
          UNION
          SELECT   b.party_id contact_party_id,
                   max(a.creation_date) creation_date,
                   'SR' load_type
          FROM     cs_incidents_all_b a,
                   cs_hz_sr_contact_points b
          WHERE    a.incident_id = b.incident_id
        AND a.creation_date > SYSDATE -$DAY
          AND b.primary_flag = 'Y'
          GROUP BY b.party_id
          UNION
          SELECT   c.note_context_type_id contact_party_id,
                   max(n.creation_date) creation_date,
                   'Note' load_type
          FROM     jtf_notes_b n,
                   jtf_note_contexts_v c
          WHERE    n.jtf_note_id = c.jtf_note_id
                   AND n.note_type IN ('KI_CNN', 'SALES')
                   AND c.note_context_type IN ('PARTY', 'PARTY_RELATIONSHIP')
      AND n.creation_date > SYSDATE -$DAY
          GROUP BY c.note_context_type_id) b
  WHERE  party.party_id = b.contact_party_id
         AND party.party_type IN ('PERSON', 'PARTY_RELATIONSHIP');
一眼看上去有很多的union,觉得很不爽,而且执行速度不怎么样,请高手帮助优化!万分感谢!