Solved

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

Posted on 2004-10-29
668 Views
Last Modified: 2012-06-21
Hi

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
   SET
       (PMO_PROPERTY.GIS_SUBD_NUMBER,
        PMO_PROPERTY.GIS_SUBD_DESCRIPTION
       )
     = (SELECT SUBD_NO,
                     SUBDIVISION
          FROM GIS_GIS_DATA
         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,
Shayne
0
Question by:shaynegw
    2 Comments
     
    LVL 3

    Assisted Solution

    by:ToddBarry
    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);
    0
     
    LVL 34

    Accepted Solution

    by:
    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.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

    875 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now