1 Reply Latest reply: Sep 14, 2009 12:56 AM by Karthick_Arp RSS

    Need to re write a query

    user575115
      Hi All,
      Below query is inline view.I need to rewrite as it is taking more time for executing.plz help
      select a.INSTITUTION_ID_DISPLAY,a.institution_name,a.SITE_ID_DISPLAY,
             a.site_name,a.region_name,a.state_name,nvl(b.total_units_used,0) UNITS_USED ,
             nvl(a.available_test_units,0)  units_remaining,a.INSTITUTION_ID_DISPLAY as inisti_display, 
              a.INSTITUTION_ID,a.site_id 
      from vueinstitutionsite a,(select test_unit_transfer_log.institution_id,
                                        sum(nvl(debit,0)) total_units_used 
                                 from test_unit_transfer_log
                                 where trunc(transaction_date) between to_date('01/01/2009','MM/DD/YYYY') 
                                       and to_date('09/07/2009','MM/DD/YYYY')
                                       and ref_type in ('arit','csp','cspb','clm','lgus','loeplis','resk','snmn','alge','rd','ss','localtest','wp_i3','wp_esl')
                                       group by  test_unit_transfer_log.institution_id )  b 
                                       where  a.inst_site_id=b.institution_id   and a.institution_id = 2
        • 1. Re: Need to re write a query
          Karthick_Arp
          You must first read the below thread end to end.

          [HOW TO: Post a SQL statement tuning request - template posting |http://forums.oracle.com/forums/thread.jspa?threadID=863295]

          This thread has another thread linked to it which tells you how to trace your performance issue. So give all the details mentioned in that thread here and we can get going from there.

          But to start with
          where trunc(transaction_date) between to_date('01/01/2009','MM/DD/YYYY') 
                     and to_date('09/07/2009','MM/DD/YYYY')
          use of a function in a column will ignore any index on that column. So do you have any index on transaction_date? If so and if its a useful index for this query then you must consider not to use truncate function.