select s.submittal_Log_id, s.File_Name, Data_Source_Code,
from submittal_log s,
(Select Submittal_Log_ID, count(*) as RecCount
group by Submittal_Log_ID) AS Obs
where Obs.Submittal_Log_ID = s.Submittal_Log_ID and accepted_yn = 'y'
Order by s.File_Name
There is a lot of information missing from your question for any definite answer. Like your Oracle release, the number of rows in each table, the existing indexes, number of rows with submittal_log.accepted_yn = 'y', probably the current execution plan etc etc .....
Nevertheles, without knowing anything of the above I can suggest you to try the following query:
(SELECT COUNT(*) FROM air_obs
WHERE Submittal_Log_ID = s.Submittal_Log_ID
) AS recCount,
from submittal_log s
where accepted_yn = 'y'
Order by s.File_Name;
Of course, I'm assuming the existance of the index on AIR_OBS(SUBMITTAL_LOG_ID).
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?