8 Replies Latest reply: Dec 3, 2012 5:07 AM by 831674 RSS

    Hash index

    831674
      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
          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..
            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
              Check jonathan lewis Blog :
              http://jonathanlewis.wordpress.com/2012/01/30/index-hash/
              • 4. Re: Hash index
                santosh..
                http://www.oracle-base.com/articles/8i/partitioned-tables-and-indexes.php#Hash
                • 5. Re: Hash index
                  moreajays
                  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
                    >
                    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
                      Thank you sir .