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?

[chitwan_humad]

Here, I would say, By analyzing tables it gets tables statistics, that's mean How the table datas are. So after your analyze command oracle got it's comlete data in it's statistics... and on the basis of this statistics oracle decides to have a Full Table Scan.

[abhaysk]

btw, how did u cacluate your stats..? used analyze or dbms_stats?

[ahmad_anees]

used analyze.

What else I can use intead of !=

[ahmad_anees]

explain plan is attached...

[abhaysk]

use dbms_stats ofcourse do collect histograms, atleast on indexed columns.

[tamilselvan]

Are you sure that the SQL is going to return half a million rows as per the explain plan?

When you executed the query, how many rows did the SQL return? And what's the run time?

[Bore]

Someone to correct me if I am wrong BUT
the first query seems to return one row
Operation Object Name Rows Bytes Cost

SELECT STATEMENT Optimizer Mode=CHOOSE 1 38

The second one however returns
Operation Object Name Rows Bytes Cost

SELECT STATEMENT Optimizer Mode=CHOOSE 501 K

I am not very sure, but before stats seems CBO thought the predicate is highly selective, so it used NL join, hoping the system to return just one row.
After the stats, the CBO considers predicate much less selective (501K rows) so it prefers sort merge join, which generally performs better that NL for huge amount of data and supposes Full Table scans for both tables.
What I would ask is , are you sure that the second query performs worse? Full table scan is not always bad

[abhaysk]

FTS is not bad but Merge join indeed s***s.. instead it could do a hash join..

[Bore]

U r right about the hash joins, but CBO favors hash joins anyway, so since it does not uses hash join, there should be a good reason for that ( say the data should be sorted anyway or...)