[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2004-10-29
2
Medium Priority
?
676 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
Comment
Question by:shaynegw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 3

Assisted Solution

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

Accepted Solution

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

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

656 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