Re: Optimization help

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sat, 19 Sep 2009 06:44:25 -0700 (PDT)
Message-ID: <3b316b92-e43c-4255-a7bf-0bfcec0ed657_at_m11g2000vbl.googlegroups.com>

On Sep 18, 2:55 pm, Sashi <small..._at_gmail.com> wrote:
> All, I'm relatively new to query optimization so I could use some help
> or pointers in the right direction.
> My query is pretty simple: just update a table.
>
> I have a table with a data feed. I have three columns of interest:
> switch_name. A typical value will be '123 - New York'.
> Trunk: A typical value is a 3 or 4 digit number.
> Vendor: null. Needs to be updated.
>
> The other table is a vendor <-> switch <-> trunk mapping table.
>
> Vendor. A typical value is "Company A".
> Trunk: matches the trunk above.
> Host: the three digit prefix from the switch above. So a matching
> value for the above would be '123'.
>
> This was my early attempt:
>
> update data_feed a
> set VENDOR = (select distinct vendor from mapping_table
> where substr(a.switch_name,1,3) = HOST and vendor is not null
> and ltrim(a.TRUNK, '0') = trunk)
>
> The data_feed table has about 9 million rows and the mapping table has
> about 2200 rows, some with null vendors.
>
> The above query ran for 14 hrs and I killed it. Upon a colleague's
> advice, I added a WHERE clause as below:
> update data_feed a
> set VENDOR = (select distinct vendor from mapping_table
> where substr(a.switch_name,1,3) = HOST and vendor is not null
> and ltrim(a.TRUNK, '0') = trunk)
> where exists (
> select 1 from mapping_table
> where substr(a.switch_name,1,3) = host );
> This also ended up running indefinitely.
>
> I tried to create indexes on the tables such as:
> data_feed: create index on substr(a.switch_name,1,3)
> create index on  ltrim(a.TRUNK, '0')
>
> This didn't help.
>
> Any suggestions?
>
> TIA,
> Sashi

Try something like this (note the removal of "DISTINCT" and the addition of "AND ROWNUM=1", and the addition of "and ltrim(a.TRUNK, '0') = trunk" to the EXISTS subquery.):
update
  data_feed a
set
  VENDOR =
   (select

      vendor
    from
      mapping_table
    where

      substr(a.switch_name,1,3) = HOST
      and vendor is not null
      and ltrim(a.TRUNK, '0') = trunk
      AND ROWNUM=1 )

where
  exists
   (select

      1
    from
      mapping_table
    where

      substr(a.switch_name,1,3) = host
      and ltrim(a.TRUNK, '0') = trunk
      and vendor is not null
      AND ROWNUM=1);

What is the purpose of "ltrim(a.TRUNK, '0') = trunk"? Is a.TRUNK a NUMBER or VARCHAR2 or CHAR? What about MAPPING_TABLE.TRUNK?

Making a guess here, if you add an index to the MAPPING_TABLE on the columns: TRUNK, HOST, and VENDOR (with VENDOR last in the composite index), does performance improve?

Are you able to use DBMS_XPLAN to display the current plan for this DML statement from the library cache (you need to find the SQL_ID and CHILD_NUMBER by searching V$SQL)?

What Oracle release version are you using (8.1.7.4, 10.2.0.1, 11.2.0.1, etc.)?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Sep 19 2009 - 08:44:25 CDT