SQL & PL/SQL

Oracle 10.2.0.4.0

Running TOAD 9.1

I am running some SQL on our eBusiness Suite:

SELECT pha.segment1
     , pha.type_lookup_code
     , (SELECT COUNT(DISTINCT pha2.po_header_id)
          FROM po.po_headers_all pha2
             , po.po_lines_all pla
         WHERE pha2.po_header_id = pla.po_header_id
           AND pla.contract_id = pha.po_header_id) po_count
     , (SELECT MAX(pha2.creation_date)
          FROM po.po_headers_all pha2
             , po.po_lines_all pla
         WHERE pha2.po_header_id = pla.po_header_id
           AND pla.contract_id = pha.po_header_id) latest_cpa_po
  FROM po.po_headers_all pha
     , po.po_vendors pv
     , po.po_vendor_sites_all pvsa
 WHERE pha.vendor_id = pv.vendor_id
   AND pha.vendor_site_id = pvsa.vendor_site_id
--   AND pv.VENDOR_NAME LIKE 'H%'
   AND pha.vendor_id = 98
   AND pha.type_lookup_code = 'CONTRACT'
   AND pha.org_id IN(7041, 7042);

The above query runs quicky (approx. 1 second). If I take out the AND pha.vendor_id = 98 then the query takes a few minutes to run.

When I try to export it, or scroll down to view > 500 rows, TOAD crashes.

I know this isn't a TOAD forum, but I think that this is probably an issue with my no doubt rubbish SQL.

If I take out this sub-select, then the problem doesn't happen:

     , (SELECT COUNT(DISTINCT pha2.po_header_id)
          FROM po.po_headers_all pha2
             , po.po_lines_all pla
         WHERE pha2.po_header_id = pla.po_header_id
           AND pla.contract_id = pha.po_header_id) po_count

However, I can't work out a better way of getting the data I need.

The sub-select counts POs which have been raised where the contractID on the PO line is the same as the PO Header ID from the main query.

Any advice please, on what I could do to sort this out would be much appreciated.

Thanks!