Re: oracle update query is taking lot of time..
Date: Thu, 02 Apr 2009 14:38:45 -0500
Mark D Powell wrote:
> On Apr 2, 8:37 am, Rajesh <mrajesh1..._at_gmail.com> wrote:
>> Dear Experts, >> >> I have a very huge table having 89 columns and 100,00,000(1CRORE) >> records. >> >> update customers_master set status='R' >> where REC_ID in >> ( select REC_ID from customers_master where >> ( >> (firstname is null and >> lastname is null) or >> address1 is null or >> mobilenumber is null >> ) >> and batchnumber='1'); >> >> basically we are marking customer record as "R" if any of the above >> listed fields(in the query) are blank. >> >> can we use PARTITION BY option in query to optimize it. >> >> thanks in advance
> The explain as mentioned would be helpful but why are you using an in
> clause subquery when you could just be testing the columns in the
> where clause directly using ( condition_1 or condition_2 etc ...)?
> Just test each row as you process the table sequentially from top to
> HTH -- Mark D Powell --
- and make sure you have the appropriate indexing to support the WHERE clause.
a good start would be batchnumber depending on how "selective" this is you may not need additional, YMMV.
so, if the full table has 1M rows and batchnumber=1 is only 500 then you should use it to your advantage....
Let me break your query down for you and what it is going to do (no explain plan necessary, but, I think it will be pretty close...
- select REC_ID from customers_master where (firstname is null and lastname is null) or address1 is null or mobilenumber is null this portion is going to do a FTS unless you have indexes that START with: firstname lastname address1 mobilenumber
THEN that result set will be reduce by: batchnumber='1');
too make the whole thing more efficient:
update customers_master set status='R'
AND ((firstname is null and lastname is null) OR address1 is null
OR mobilenumber is null
if you have an index with batchnumber as the leading edge, it will reduce the"lookup" from 100,00,000 to the size of that batch (say... 500 records before evaluating the rest of the conditions) Received on Thu Apr 02 2009 - 14:38:45 CDT