SQL tuning - Anti Join

[ahmad_anees]

Hi All,

I have a cursor in a package which is taking a lot of time, and using full table scan.

strainge thing is that, when I saw explain plan, before analyzing table there was one full table scan, after analyzing there are three full table scans and cost increased.

Can you experts hepl me tuning this sql?

explain plan is attached.
SELECT
IDE_INSTRUMENT
,IDE_LISTING
,COD_LANG
,TYP_NAME
,IDE_NAME
,COD_REGROUPING
,IDE_INSTITUTION
,COD_MARKET
,COD_CLASS
,COD_SECT_LIST
,DAT_LAST_TRADING
,NAM_DENORM
FROM SMF_NAMES_DENORM_IBS sndi, SMF_INSTR_NAMES inna, SMF_ISTN_NAMES isna
WHERE isna.ide_institution_name = sndi.ide_name
AND inna.ide_instr_name = sndi.ide_name
AND DECODE(typ_name, 'SHOR', UPPER(isna.nam_short),
'LONG', UPPER(isna.nam_long),
'TECH', inna.nam_tech_short, sndi.nam_denorm) != sndi.nam_denorm;


Please help me?