This discussion is archived
8 Replies Latest reply: Dec 3, 2012 3:07 AM by 831674 RSS

Hash index

831674 Newbie
Currently Being Moderated
Hi ,

what is hash index,in which case we have to use hash index in oracle . And please give me one simple example .

Thanks in advance,
Sanjeev.
  • 1. Re: Hash index
    moreajays Pro
    Currently Being Moderated
    Sanjeev,

    Hash indexes are generally used for where clause involving "=" operator. Generally used in MySQL explicitly
    Will get back to you for Oracle soon

    Thanks,
    Ajay More
    http://moreajays.blogspot.com

    Edited by: moreajays on Nov 29, 2012 12:48 PM
  • 2. Re: Hash index
    Vedant.. Journeyer
    Currently Being Moderated
    Hi,

    You may refer foll link:
    http://docs.oracle.com/cd/E17952_01/refman-5.5-en/index-btree-hash.html
  • 3. Re: Hash index
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check jonathan lewis Blog :
    http://jonathanlewis.wordpress.com/2012/01/30/index-hash/
  • 4. Re: Hash index
    santosh.. Newbie
    Currently Being Moderated
    http://www.oracle-base.com/articles/8i/partitioned-tables-and-indexes.php#Hash
  • 5. Re: Hash index
    moreajays Pro
    Currently Being Moderated
    Sanjeev,

    Hash index stores key value pairs based on a pseudo randomizing function called hash function , hashing compacts the input based on hasing function such as more set of values could have similar hash value

    Hashin index generally found on hash partitioned table as global partition hash indexes reduces high contention of the leaf blocks


    e.g.

    create index hg_idx on tab (c1,c2,c3)
    global partition by hash (c1,c2)
    (partition p1 tablespace tbs_1,
    partition p2 tablespace tbs_2,
    partition p3 tablespace tbs_3,
    partition p4 tablespace tbs_4);

    One good article on hashing pls refer

    http://www.databasejournal.com/features/oracle/article.php/3706506/Hashing-in-Oracle.htm


    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 7. Re: Hash index
    rp0428 Guru
    Currently Being Moderated
    >
    what is hash index,in which case we have to use hash index in oracle . And please give me one simple example .
    >
    You never HAVE to use any a hash, or any other, index. You only use an index if it provides some benefit.

    A hash index is often used when there is a lot of data that you want to break into separate partitions or subpartitions or when you have a set of distinct values that you want to partition on and don't want to have to specify all of the partitions individually.

    An example would be if you have data for the 50 United States and you want to partition by state. You could create a hash-partitioned table on STATE_NAME or STATE_CODE (50 distinct values) and you would likely get 50 partitions. That would be easier that creating a list-partitioned table and having to specify 50 different 'lists' (one for each state).

    Oracle would still do partition pruning if your queries used filter predicates such as: WHERE STATE_NAME = 'CALIFORNIA'. Only one partition would be used in the query even though you used hash-partitioning. That is because the word 'CALIFORNIA' always hashes to the same value and Oracle can do that hash when the query is compiled and determine that only one partition is needed.
  • 8. Re: Hash index
    831674 Newbie
    Currently Being Moderated
    Thank you sir .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points