Home » RDBMS Server » Performance Tuning » update query tuning
update query tuning [message #384891] Thu, 05 February 2009 11:59 Go to next message
dr46014
Messages: 48
Registered: February 2007
Member
Hi all
I have a table where i have 3 major columns cust_id,acct_nbr and effective_end_date.Now i have global index on cust_id and acct_nbr.effective_end_date contains NULL values.The latest records are those where effective_end_date is NULL.A closed record has a valid date value for effective_end_date.
Suppose in my table i have the below values:

cust_id acct_nbr effective_end_date
B 200 04-feb-05
A 100 10-dec-07
B 200 15-dec-08
C 200 NULL

In a file i have new account numbers which will be updated and its last cust_id.
Here i have account_nbr=200
cust_id=C
The the row that will be updated will be
cust_id:C
account_nbr:200
effective_end_date=NULL

Here the record will be closed just by updating the effective_end_date value to a date value.

The update query will be
update table .....
set effective_end_date=to_date(....)
where effective_end_date is NULL
and cust_id=<cust_id in the file>
and account_nbr=<account_nbr in the file>

Here the query will take much time..
Is there any other optimized way we can do this.We need to update 10 MM records like this.The cust_id and account_nbr which will be updated are in a file.There may be more than 1 combination of cust_id and account_nbr but we need to choose the latest one with effective_end_date is NULL.
Re: update query tuning [message #384892 is a reply to message #384891] Thu, 05 February 2009 12:02 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results

>where effective_end_date is NULL
poor implementation choice
NULLs are not indexed & forces a Full Table Scan for each row updated.


Re: update query tuning [message #384898 is a reply to message #384891] Thu, 05 February 2009 12:16 Go to previous messageGo to next message
dr46014
Messages: 48
Registered: February 2007
Member
i agree...but the table can't be changed now Sad
is there any other work around







Re: update query tuning [message #384901 is a reply to message #384891] Thu, 05 February 2009 12:21 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
A car was built with square tires; which you claim can not be changed.

Such a car can not be made to go fast.
Either accept what you have or change to make it go faster.

You are asking to teach a pig to fly.
Good Luck!
Re: update query tuning [message #385074 is a reply to message #384898] Fri, 06 February 2009 06:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Fortunately, some of us round here have a more helpful attitude.

Having a null non-leading column in your index does not force a Full table scan.

Your index on Cust_id and Account_Nbr should mean that you would get an Index Range scan acess path for each update to the table.

If you can create indexes and can alter the query that you're using, then you could create an index on Cust_id, Account_Nbr, nvl(effective_end_date,to_date('01-01-1700','dd-mm-yyyy')) and change your query to:
update table .....
 set effective_end_date=to_date(....)
 where nvl(effective_end_date,to_date('01-01-1700','dd-mm-yyyy')) = to_date('01-01-1700','dd-mm-yyyy')
 and cust_id=<cust_id in the file>
 and account_nbr=<account_nbr in the file>


If cust_id, account_nbr and effective_end_date make a unique combination, then you could make this a unique index.

Re: update query tuning [message #385160 is a reply to message #384891] Fri, 06 February 2009 13:11 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
First ask youself

"How much of the table am I updating?"

THis is the first way to choose the right tuning method.

If its over 20%, consider the tuning options specified in the tuning guide in the first post in the performance tuning section.

10 million rows through an index range scan on a 20 million row table will be much slower than using a full table scan (no index). You can use the FULL hint to force that in your query.

You can also create a copy of the table using CTAS query, with the data amended, and then insert it back in.

If its not an option, you can split the table into "streams" and update using 10-20 update statements all running at the same time (using the block separation method described in the tuning guide to avoid contention).
Previous Topic: Issues with V$SQLTEXT
Next Topic: Slow Import on x64
Goto Forum:
  


Current Time: Wed Dec 07 06:28:18 CST 2016

Total time taken to generate the page: 0.17849 seconds