Home » SQL & PL/SQL » SQL & PL/SQL » Improve Performance of SQL
Improve Performance of SQL [message #37547] Wed, 13 February 2002 20:39 Go to next message
Suparna Saha
Messages: 10
Registered: October 2001
Junior Member
Thanks for your reply. You send me following SQL.
update t_sale_summary_monthly s
> set qty_sold_ltrs =
> (select nvl(sum(d.qty_sold_ltrs), 0)
> from t_sale_prodwise d
> where d.comp_code = s.comp_code
> and d.cust_code = s.cust_code
> and d.year_no = s.year_no
> and d.month_no = s.month_no)
> where year_no = 2001
> and month_no = 10

I have already tried with this SQL. But after 12hours it was
not showing any complete message. It was also not
giving any rollback segment error message. That's why
I tried with cursor. I used current of cursor because
it will take rowid of that row. In another way I am
trying. If you have any other idea please mail me.

Suparna Saha
Re: Improve Performance of SQL [message #37567 is a reply to message #37547] Thu, 14 February 2002 08:21 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Are you sure you tried this exact SQL? Does your detail table really have an index on (comp_code, cust_code, year_no, month_no)?

Did you maybe try the SQL by including the UPPER function - which, as I mentioned, would completely bypass your index?

How many of the rows in your summary table (out of the 170,000) are for year_no = 2001 and month_no = 10?

Can you show us the EXPLAIN PLAN for the exact SQL I provided you? I just don't believe that it could run for 12 hours even if it was doing full-table scans.
Previous Topic: Explain plan
Next Topic: Re: LONG RAW
Goto Forum:

Current Time: Thu Sep 23 12:52:39 CDT 2021