How can I get this SQL update query to run faster?


I have two oracle tables (PMO_PROPERTY and GIS_GIS_DATA) and I want to update two fields in PMO_PROPERTY using data from GIS_GIS_DATA

The tables are related using PMO_PROPERTY.TX_ACC_FILING_NUMBER = GIS_GIS_DATA.APN and both fields are indexed. TX_ACC_FILING_NUMBER is NUMBER(9) and APN is VARCHAR2(9). There are 66730 records in PMO_PROPERTY and 59125 distinct APNs, there are duplicate APNs but no NULLS. There are 65571 records in GIS_GIS_DATA and 59380 distinct APNs, there are no duplicates but there are NULLS. All 66730 (59125) records in PMO_PROPERTY will have a matching record in GIS_GIS_DATA.

This is the SQL I tried:


However the running time is very, very long...close to two hours. I have very little experience with SQL optimization, so I would appreciate any advice on how to run this update as quick as possible.

Thanks and take care,
Who is Participating?
Mark GeerlingsDatabase AdministratorCommented:
Todd is correct, the hint to use an index on the table being updated adds no value.  The statement he gave you should work, but there may still be a problem because of the datatype differences.  Oracle will have to do an implicit data conversion, and it may do the wrong one, preventing use of the index on gis_gis_data.apn.  To be safe use an explicit conversion with "to_char" like this:
update tas_pmo.pmo_property p
   set (gis_subd_number, gis_subd_description) =
         (select g.subd_no, g.subdivision
            from gis_gis_data g
           where g.apn = to_char(p.tx_acc_filing_number));

If you have any tx_acc_filing_number values that have leading spaces or leading zeroes, you will have to use a format mask with the to_char so they match.
A hint referencing the table being updated here is not going to help.  You have no WHERE clause on the PMO table so this will always be a FTS.  You said there is an index on the APN column in the GIS table - that is the index that should be used here.  Without a hint, what does EXPLAIN PLAN show?  Are the tables analyzed?

update tas_pmo.pmo_property p
   set (gis_subd_number, gis_subd_description) =
         (select g.subd_no, g.subdivision
            from gis_gis_data g
           where g.apn = p.tx_acc_filing_number);
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.