Re: A very slow Select Statement

On 16 Sep 2005 15:14:12 -0700, "McCormick" <mccormm_at_indy.net> 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

Pulling out my crystall ball here.
Could it be the only real predicate you have on the open table is
> WHERE SMGR.calculate_credit_rating(o.open_cust_code) IN (0,1)
which looks like calling a function in a package. In that case:
you likely have a full table scan on open. Because of the full table scan on the driving table (as it is the leftmost table), you also have a full table scan on hist. You'll need to put a function based index on open. The only other option is to make sure you don't need the calculation.

--
Sybrand Bakker, Senior Oracle DBA