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

On Sep 18, 2:55*pm, Sashi <small... (AT) gmail (DOT) com> wrote:
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.

They're both varchar.
I have no idea what you're talking about so this will be a good
reading experience.
I've never done an explain plan before to try and analyze what the
server is doing so this should be interesting.

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production

Thanks for your help.
Sashi