If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I'd say that CASE (which makes this query to seem huge and complicated) doesn't slow things down, but your WHERE condition ... Are there many rows in "componente" table? If so, it HAS to be slow as no index can be used here.

How about omiting the complete WHERE clause? You could easily do it if all "componente_materiale_costo.id_componente"s are contained in the "componente" table.

Sorry to jump way off subject, but Tony you should consider adding a sticky to the top of the forum page telling everyone the tags they need to use to format code correctly - It definitely gets questions answered quicker as I've seen you say!

you should also have an index on COMPONENTE.ID_COMPONENTE - this will allow oracle to rewrite the subquery to a join (not saying it will, but it might). Depending on how many rows are in each table, the IN could be inefficient. And, depending on the version of Oracle, the optimizer setting, the stats, and a plethora of other things, Oracle may or may not perform a straight NL join, as opposed to a MERGE or HASH. You could try an EXISTS subquery (again, Oracle may do it for you anyway, but give it a try):

Sorry to jump way off subject, but Tony you should consider adding a sticky to the top of the forum page telling everyone the tags they need to use to format code correctly - It definitely gets questions answered quicker as I've seen you say!

Done - quickly knocked together a replacement for the previous sticky thread. Any suggestions gratefully received!

UPDATE componente_materiale_costo
SET costo_materiale = costo_materiale *
CASE id_componente_materiale
WHEN 'FE' THEN 1.0005
WHEN 'GH' THEN 1.005
WHEN 'AL' THEN 1.5
WHEN 'M_ELE' THEN 5
ELSE 1
END
, data_ora_aggiornamento =
CASE id_componente_materiale
WHEN 'FE' THEN DATE '2000-09-24'
WHEN 'GH' THEN DATE '2000-09-24'
WHEN 'AL' THEN DATE '2000-09-24'
WHEN 'M_ELE' THEN DATE '2000-09-24'
ELSE NULL
END
WHERE id_componente IN
( SELECT id_componente
FROM componente );

UPDATE componente_materiale_costo
SET costo_materiale = costo_materiale *
CASE id_componente_materiale
WHEN 'FE' THEN 1.0005
WHEN 'GH' THEN 1.005
WHEN 'AL' THEN 1.5
WHEN 'M_ELE' THEN 5
ELSE 1
END
, data_ora_aggiornamento =
CASE WHEN id_componente_materiale IN ('FE','GH','AL','M_ELE')
THEN DATE '2000-09-24'
ELSE NULL
END
WHERE id_componente IN
( SELECT id_componente
FROM componente );