| Improve Performance of SQL [message #37547] |
Wed, 13 February 2002 20:39  |
Suparna Saha
Messages: 10
Registered: October 2001
|
Junior Member |
|
|
Hi,
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.
Thanks.
Suparna Saha
|
|
|
|
| Re: Improve Performance of SQL [message #37567 is a reply to message #37547] |
Thu, 14 February 2002 08:21  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
