SQL tuning - Anti Join
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: SQL tuning - Anti Join

  1. #1
    Join Date
    Aug 2004
    Location
    bangalore
    Posts
    10

    Question SQL tuning - Anti Join

    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?
    Attached Files Attached Files
    Last edited by ahmad_anees; 10-26-2007 at 06:58 AM.

  2. #2
    Join Date
    Oct 2007
    Posts
    11
    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.

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    btw, how did u cacluate your stats..? used analyze or dbms_stats?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Aug 2004
    Location
    bangalore
    Posts
    10
    used analyze.

    What else I can use intead of !=

  5. #5
    Join Date
    Aug 2004
    Location
    bangalore
    Posts
    10
    explain plan is attached...

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    use dbms_stats ofcourse do collect histograms, atleast on indexed columns.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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?

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    FTS is not bad but Merge join indeed s***s.. instead it could do a hash join..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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...)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width