[Webinar] Streamline your web hosting managementRegister Today


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

Posted on 2004-10-29
Medium Priority
Last Modified: 2012-06-21

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,
Question by:shaynegw

Assisted Solution

ToddBarry earned 375 total points
ID: 12447158
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);
LVL 35

Accepted Solution

Mark Geerlings earned 375 total points
ID: 12447610
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.

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question