DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Sort-merge join???

  1. #1
    Join Date
    Feb 2007
    Posts
    20

    Sort-merge join???

    Which of the following queries will mostly likely require a sort-merge join?
    Why???

    A. Two-table equijoin with no indexes
    B. Two tables equijoin with one index
    C. Two-table equijoin in which both tables are indexed on the join column

    In which case nested loop join is used?
    Last edited by janki_mehta; 03-31-2007 at 12:47 PM.

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    May i know why you are asking this question ?
    May i also know what prevents you from setting up a test case and exploring ?

    regards
    Hrishy

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    do you want us to do your test questions? LOL

    I'm 100% in agreement with Hrishy
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have a look through the documentation, and understand how the join mechanisms work. The answers are pretty obvious.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Feb 2007
    Posts
    20

    [QUOTE=hrishy]

    Quote Originally Posted by hrishy
    Hi

    May i know why you are asking this question ?
    May i also know what prevents you from setting up a test case and exploring ?

    regards
    Hrishy
    Actually i am a student and find many difficulties when i am trying to learn oracle without any tutor..

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    As a student you are most welcome to setup a test case and see how things work.

    regards
    Hrishy

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by hrishy
    Hi

    As a student you are most welcome to setup a test case and see how things work.

    regards
    Hrishy
    Unfortunately you'll often find in the process that the answer depends on many other factors ... hence the importance of understanding the mechanism by which the joins work.

    None of those scenarios actually require a sort-merge join at all, by the way.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by janki_mehta
    Which of the following queries will mostly likely require a sort-merge join?
    Why???

    A. Two-table equijoin with no indexes
    B. Two tables equijoin with one index
    C. Two-table equijoin in which both tables are indexed on the join column

    In which case nested loop join is used?
    When oracle sees in-equality join between two tables, then it may choose sort-merge operation.
    Last edited by tamilselvan; 04-08-2007 at 07:18 PM.

  9. #9
    Join Date
    Dec 2005
    Posts
    195
    You can read this link. This will answer all your questions about optimizers

    http://www.lc.leidenuniv.nl/awcourse...3/optimops.htm

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