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

Oracle table partitions

Hi,

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 .
0
sam_2012
Asked:
sam_2012
  • 2
2 Solutions
 
Naitik GamitSoftware DeveloperCommented:
0
 
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

SYS_P25
SYS_P26
SYS_P27
SYS_P28

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
1,sdsd
45,sdfsdf
34,dfgdfg
24,dssdf

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
0
 
MikeOM_DBACommented:
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

;)
0
 
sam_2012Author Commented:
Thanks a lot , gave me an good idea about partitions in oracle.
0
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