Optomising a sub-query
544254Nov 6 2006 — edited Nov 7 2006I'm having trouble optomising a SQL statement with a sub-query, here 'tis:
SELECT
CB_SAMPLE_POT.ID AS "ID",
CB_SAMPLE_POT.HAUL_SEQUENCE_NUMBER AS "Pot",
CM_LOCATION.DESCRIPTION AS "Location",
CB_SAMPLE_POT.DEPTH AS "Depth",
DEC_TO_DMS(CB_SAMPLE_POT.LONGITUDE) AS "Longitude",
DEC_TO_DMS(CB_SAMPLE_POT.LATITUDE) AS "Latitude",
(SELECT COUNT(*) FROM CB_SAMPLE_LOBSTER WHERE CB_SAMPLE_POT_ID = CB_SAMPLE_POT.ID) AS "Lobsters"
FROM CB_SAMPLE_POT
LEFT OUTER JOIN CM_LOCATION
ON CB_SAMPLE_POT.CM_LOCATION_ID = CM_LOCATION.ID
WHERE CB_SAMPLE_POT.CB_SAMPLE_SHOT_ID = 12505
ORDER BY CB_SAMPLE_POT.HAUL_SEQUENCE_NUMBER;
CB_SAMPLE_POT.ID and CB_SAMPLE_POT_ID are primary and foreign keys respectively. CB_SAMPLE_POT.CB_SAMPLE_SHOT_ID is also a foreign key. All the keys are integers. There's around 650,000 records in CB_SAMPLE_LOBSTER and 100,000 in CB_SAMPLE_POT. The query returns 60 rows.
As it is, it takes around 25-30 seconds to run. If I remove the subquery (SELECT COUNT(*)....AS "Lobsters"), it only takes 0.2 secs. Any ideas?
BTW: I'm reasonably new to Oracle and come from a SQL Server background. I hate to rub it in, but SQL Server can optimise this kind of query much better than Oracle!!
How about somebody proving me wrong!