A very slow Select Statement

From: McCormick <mccormm_at_indy.net>
Date: 16 Sep 2005 15:14:12 -0700
Message-ID: <1126908852.047158.135700@g14g2000cwa.googlegroups.com>

  I'm having trouble with the following Select statement. It's a statement for an Inline View of a much larger Select, and it seems to be the part that takes the most time to run.

SELECT o.open_cust_code,
       o.open_prem_code,
       a.hist_printed_date,
       SUM(o.open_balance) as balance

  FROM open o, hist a
 WHERE SMGR.calculate_credit_rating(o.open_cust_code) IN (0,1)

   AND a.hist_cust_code = o.open_cust_code
   AND a.hist_prem_code = o.open_prem_code
   AND a.hist_printed_date =
      (SELECT MAX(hist_printed_date)
         FROM hist b
        WHERE a.hist_cust_code = b.hist_cust_code
          AND a.hist_prem_code = b.hist_prem_code
          AND b.hist_printed_date < sysdate-30
          AND b.hist_account_status_ind = 'F'
       )
 GROUP BY o.open_cust_code,
          o.open_prem_code,
          a.hist_printed_date

HAVING SUM(o.open_balance) > 5

  The OPEN table is much larger than the HIST table, though both tables are huge. Nothing I've tried so far with hints and (available) indexes and made any significant improvement. What I'd like to know is if there is a way to restructure the thing to be more efficient.

Thanks!

Mike McCormick Received on Fri Sep 16 2005 - 17:14:12 CDT