How can I get this SQL update query to run faster?
Posted on 2004-10-29
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:
UPDATE /*+ INDEX(PMO_PROPERTY I_PP_TX_ACC_FILING_NUMBER) */ TAS_PMO.PMO_PROPERTY
= (SELECT SUBD_NO,
WHERE GIS_GIS_DATA.APN = PMO_PROPERTY.TX_ACC_FILING_NUMBER
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,