asked on

Update very slow

I have data in Tbl1 as follows:

ColPk ColBK ColVal Flag End_Date
1 A 10 N '09-FEB-2009'
2 A 20 N '08-JAN-2008'
3 A 30 Y '31-DEC-2019'
4 B 10 N '09-JAN-2005'
5 B 20 N '08-FEB-2006'
6 B 30 N '05-MAR-2009'
7 B 40 Y '31-DEC-2019'

I need to delete the ColVal=40 row for ColBK=B and update the previous row's flag and date.

This is the code I wrote:

update Tbl1
set End_Date='31-DEC-2019', Flag='Y'
where (ColBK, ColVal) in
(
select ColBK, max(ColVal)
from Tbl1
where ColVal < 40
and Flag = 'N'
and ssno in (
select distinct ColBK
from Tbl1
where ColVal=40
)
Group by ColBK
)

So for ColBKs for which there are no ColVal=40 records, nothing gets changed. After this UPDATE sql, I'll execute a SQL to delete ColVal=40 records from the table.

My problem: the SQL works but is very slow due to data volume. I tried the MERGE statement but it doesn't work because of ora-38104. Can anyone help me speed up this SQL?

Another option I have: I can also delete ColVal=40 records first and do the UPDATE if that helps performance.

Thanks.

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Am P

You have used same table three times in the query. Also you are using the IN clause that is affecting the performance.

Yes, Index may decrease the performance and that's why Iwedwell asked about the index.

Am P

is ssno field part of the table?

nQuote

ASKER

SSNO really should be ColBK.

nQuote

ASKER

Here is the Explain Plan:

Lee Wadwell

You say there are is a 'data volume' yet the costs and cardinality are tiny. Do you have statistics gathered on the tables?

nQuote

ASKER

May be statistics need to be analyzed. Let me let everyone know.

ralmada

Also try using exists there instead of the select distinct.

update Tbl1
set End_Date='31-DEC-2019', Flag='Y'
where (ColBK, ColVal) in
(
	select a.ColBK, max(a.ColVal)
    	from Tbl1 a
	where 	a.ColVal < 40
     		and a.Flag = 'N'
		and exists(select 1 from tbl1 where ColVal=40 and ssno = a.ssno)
	Group by ColBK
)

Open in new window

Another option I have: I can also delete ColVal=40 records first and do the UPDATE if that helps performance

That will likely help because you will get rid of the extra condition there... In that case, I guess, it will be

update Tbl1
set End_Date='31-DEC-2019', Flag='Y'
where (ColBK, ColVal) in
(
	select a.ColBK, max(a.ColVal)
    	from Tbl1 a
	where 	a.ColVal < 40
     		and a.Flag = 'N'
	Group by ColBK
)

Open in new window

nQuote

ASKER

The table needed to be analyzed and stats updated.

ralmada

Have you ever read my last comment? Your query can be improved as well... Anyhow