DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006

    query tuning or give alternate solution- plz

    hi friends,

    if i use the following query it hangs.

    select * from rm_stkfile where piece_no not in(
    select piece_no from rm_purdetl);

    here rm_purdetl table consistis of around 74000 rows.
    rm_stkfile consists of around 50000 rows.
    piece_no is common column in both tables.

    what is alternate solution for this.


  2. #2
    Join Date
    Mar 2007
    Ft. Lauderdale, FL
    1- You would never ever do a "select *", select just what you need.
    2- NOT IN is the worst predicate you can think about
    3- I stand corrected, NOT IN of a inline view is the worst predicate you can think about

    Please post...
    a) Available indexes.
    b) Explain plan.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2002
    Lake Worth, FL


    Check out the explain plan for these:

    Select * From Rm_Stkfile 
     Where Piece_No In (
         Select Piece_No From Rm_Stkfile 
         Select Piece_No From Rm_Purdetl);
    And this:

    Select * From Rm_Stkfile S
     Where NOT EXISTS (
         Select 'True' From Rm_Purdetl P
          Where P.Piece_No = S.Piece_No);

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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

By using this site, you agree to the Privacy Policy