• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Oracle table partitions


Iam working on oracle table partitions and need to provide a training on the types of partitions , I need the below information in regard to oracle hash partition

1. How does hash linear algorithm work , any example should be fine. I need an detailed explanation on how oracle applies Linear hash algorithm for each table partition using HASH .
  • 2
2 Solutions
Naitik GamitSoftware DeveloperCommented:
sam_2012Author Commented:
hi Naitik,

Can you explain me how the partitions are derived using the steps mentioned in the url above

I have followed the below steps to validate the Linear HASH algorithm

create table hash_tab
id number ,
name varchar2(40))
partition by hash (id)
partitions 4;

Below are the partitions created in oracle after the above statement


Analyze the tables so that the num_rows column in user_tab_partitions is updated.

Insert into hash_tab values (&i,'&n');

Values inserted are

I tried to validate the algorithm logic mentioned in the above url mentioned , iam not able to derive the correct partition into which oracle inserts the records But I noticed  , it starts inserting into the last partition SYS_P28

Below is the sample logic using the algorithm mentioned in the above url
num=4 --> no of partitions
v=power(2*ceil(log(2,4))); --> 4
n=f(column_value) & (v-1) --> 1 & (4-1) = 1 &3 =1

but the value 1 is getting inserted into 4th partition SYS_P28 instead of SYS_P25

Any help with the algorithm mentioned in the url is really appreciated.
This I need for training purpose
According to the link on LINEAR HASH Partitioning, the disadvantage is that data is less likely to be evenly distributed between partitions as compared with the distribution obtained using regular hash partitioning.

Regular partitioning will distribute evenly:
SQL> SELECT Id, MOD ( Id, 4 )+1 Part
  FROM Hash_Tab
 ORDER BY 2, 1

        ID       PART
---------- ----------
         4          1
        24          1
         1          2
        45          2
         2          3
        34          3
         3          4

7 rows selected.

Open in new window

sam_2012Author Commented:
Thanks a lot , gave me an good idea about partitions in oracle.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now