How can I make this query more efficient??

select s.submittal_Log_id, s.File_Name, Data_Source_Code,
obs.RecCount,s.file_notes
from submittal_log s,
(Select Submittal_Log_ID, count(*) as RecCount
from air_obs
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:

Code:

---

select
  s.submittal_Log_id,
  s.File_Name,
  Data_Source_Code,
  (SELECT COUNT(*) FROM air_obs
    WHERE Submittal_Log_ID = s.Submittal_Log_ID
  ) AS recCount,
  s.file_notes
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).