| update query tuning [message #384891] |
Thu, 05 February 2009 11:59  |
dr46014
Messages: 49
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 #385074 is a reply to message #384898] |
Fri, 06 February 2009 06:11  |
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 |
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).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
