Run Oracle Update Statements in Batch Mode
I need to run some relatively simple SQL update statements to update a single column in an Oracle table with 14.4 million rows. One statement executes a function written in Java and the JVM runs out of memory as Im doing the update on all 14.4 million lines.
Have you written some sort of batch PL / SQL procedure that can break this simple update into many, say 10K records per batch? I know that if I can migrate my updates after a bunch of entries, it will run much faster and I won't run out of memory. I'm sure this is an easy way to do it with FOR loop
and row_num
, but I haven't had much success.
Here are two statements that I need to fulfill for each batch of n records:
the first:
update vr_location l set l.usps_address=(
select mylib.string_utils.remove_duplicate_whitespace(
house_number || ' ' || pre_street_direction || ' ' || street_name || ' ' ||
street_description || ' ' || post_street_direction)
from vr_address a where a.address_pk=l.address_pk);
second:
update vr_location set usps_address = mylib.usaddress_utils.parse_address(usps_address);
a source to share
Make an initial selection to get any grouping attribute so that you end up with groups with the required number of rows. Experiment with a grouping suggestion, such as the last three digits of a postal code or something semi-random.
End the loop over the grouping clause, using a parameter as a parameter to limit the rows intended for each update statement. commit at the end of each iteration.
a source to share
You (or your DBA) must define UNDO correctly and do it as one SQL transaction
Benefits:
- read the sequence in the table while it is happening
- you retain the ability to rollback the transaction in case something doesn't work.
If you are in some kind of boot environment where you care, use CTAS (create table as selection) to create a new table with modified value, build indexes, constraints, etc. and then replace the table names. These days, 14 million lines aren't that big.
a source to share
Well, I had to do my best to make your recommendations and then make a little Python for that. I ended up using cx_Oracle to give me good control over transactions. Obviously PL / SQL would be better, but I don't know that. Python is my new hammer and it's all a nail!
#!/usr/bin/env python
import csv
import time
import cx_Oracle
# Parses USPS addresses from voter addresses
# and inserts them into VR_LOCATION table ready
# for geocoding. Does batches by zipcode
def LoadZips():
zipcodes = []
zips = open('OH_ZIP_CODES.txt','r')
for line in zips:
zip = line[0:5]
if zip not in zipcodes:
zipcodes.append(zip)
zips.close()
return zipcodes
def UpdateAddresses(ziplist):
counter = 1
total = len(ziplist)
for zipcode in ziplist:
orcl = cx_Oracle.connect('voter/voter@oracle')
curs = orcl.cursor()
countsql = "select count(*) from vr_location where zip_co = '%s'" % zipcode
concatsql = """update vr_location l set l.usps_address=(
select mizar.string_utils.remove_duplicate_whitespace(
house_number
||' '||pre_street_direction
||' '||street_name
||' '||street_description
||' '||post_street_direction)
from vr_address a where a.address_pk = l.address_pk)
where zip_co = '%s'""" % zipcode
parsesql = """update vr_location set usps_address = mizar.usaddress_utils.parse_address(usps_address)
where zip_co = '%s'""" % zipcode
curs.execute(countsql)
records_affected = curs.fetchone()[0]
if records_affected == 0:
print "No records for zipcode %s" % zipcode
counter += 1
continue
print "[%s] %s of %s: %s addresses" % (zipcode, counter, total, records_affected)
curs.execute(concatsql)
orcl.commit()
curs.execute(parsesql)
orcl.commit()
curs.close()
counter += 1
# Uncomment this to debug - just steps through X zipcodes
#if counter == 3:
# print "Cleaning up..."
# break
if __name__ == "__main__":
start = time.clock()
zipcodes = LoadZips()
print "Processing addresses in %s zip codes" % len(zipcodes)
UpdateAddresses(zipcodes)
a source to share