Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Optomising a sub-query

544254Nov 6 2006 — edited Nov 7 2006
I'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!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 5 2006
Added on Nov 6 2006
4 comments
504 views