Optimization help
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