Re: A very slow Select Statement

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:h6ani19gaolq305e7j3a68oulmelbqbmet_at_4ax.com...
> 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

other things that could be learned from the explain plan of the complete statement:

is this in-line view is being executed multiple times? what % or rows are being returned from these tables?

++ mcs Received on Sat Sep 17 2005 - 07:13:13 CDT