1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

hash join,Index performance reg.

Discussion in 'SQL PL/SQL' started by laxman, Oct 26, 2009.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear sir,
    Can you give some information with example of below mentioned query

    1) diff between IN and EXIST operator
    2) What is HASH JOIN
    3) What is tempory table
    4) On what condition an INDEX performance is effective or not
    effective
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi laxman, to my knowledge i will explain.
    1. "in"operator returns hardcoded value and values are displayed where as exists returns boolean value and used by outer select stmnts these values cannot be displayed.
    4.
    not effectiv:
    i)index is not effective when table contains less rows.
    ii)when there is frequent use of select stmnts on the table.

    effectiv:
    i)when there are records more than some 1000 records

    hope what i explained is right
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Using IN causes Oracle to process the entire list; using EXISTS causes Oracle to process only 'matching' entries, those which produce output in the subquery, which can make EXISTS more efficient.

    A hash join causes Oracle to create a hash table of the joined column values and process only those entries where the hash value matches that of the specified criteria. Since it makes one pass through each table and one pass through the hash table it can be a more efficient join mechanism than a nested loop join, which makes a complete pass through the joined table for each join key from the driving table.

    Temporary tables have a persistent structure but not persistent data, used usually to contain transient data for the duration of a transaction or a session. A user can have a local temporary table (only he or she can see it) or a user can have access to a global temporary table which allows all users to see and use it; in a global temporary table the only data a user can see is his/her own and this data can remain for the duration of a transaction or for the duration of the session.

    The index question is complex; four possible answers are found here:

    http://oratips-ddf.blogspot.com/2008/04/to-b-or-not-to-b.html
    http://oratips-ddf.blogspot.com/2008/04/tale-of-two-indexes.html
    http://oratips-ddf.blogspot.com/2008/11/magical-indexes.html
    http://oratips-ddf.blogspot.com/2007/06/table-scans-histograms-and-scarlett.html
     
  4. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hello sir,
    A heartfully thanks to you and Rajavu Sir for providing me valuable information related to unique index issues in such a critical situation.yes, i have successfully implemented it and now project is in next level implementation phase.

    Thanks n Regards
    Laxman