Optimization help

From: Sashi <smalladi_at_gmail.com>
Date: Fri, 18 Sep 2009 11:55:09 -0700 (PDT)
Message-ID: <16124028-8bb4-4d8b-98cc-1543208f14e6_at_g1g2000vbr.googlegroups.com>



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 Received on Fri Sep 18 2009 - 13:55:09 CDT

Original text of this message