8 Replies Latest reply on Dec 3, 2012 11:07 AM by 831674

    Hash index

      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,
        • 1. Re: Hash index

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

          Ajay More

          Edited by: moreajays on Nov 29, 2012 12:48 PM
          • 2. Re: Hash index

            You may refer foll link:
            • 3. Re: Hash index
              Check jonathan lewis Blog :
              • 4. Re: Hash index
                • 5. Re: Hash index

                  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


                  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


                  Ajay More
                  • 7. Re: Hash index
                    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
                      Thank you sir .