How can I make this query more efficient??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How can I make this query more efficient??

  1. #1
    Join Date
    Feb 2002
    Posts
    4
    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width