Re: A very slow Select Statement

McCormick wrote:
> 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

To start with at least one badly chosen table name.

SQL> select count(*)

   2 from v$reserved_words
   3 where keyword = 'OPEN';

   COUNT(*)

          1

But lets see if I can help you.

1. Oracle version? Not provided.
2. Explain plan? Not provided.
3. Are statistics current and created with DBMS_STATS? Not provided.
4. Do indexes exist? Not provided.
5. Definition of the word slow? Not provided.
6. Number of rows in the tables? Not provided.

Haven't got a clue. Sorry. I'd start by renaming the table.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)