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 .
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naitik GamitSoftware DeveloperCommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.