>-----Original Message-----
>From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]]
>
>How do I exactly implement this.In the before insert trigger
> what after I generate the value for the new partition column.How does the
>records go into that partition.
>
>Have you tried this.How is the performance for an insert into a table
> of 100000 records everyday.Executing the trigger for every
>insert for high volume of data may be costly on the performance..?
>
>Can we achieve this or anything closer using HASH partitioning as suggested by others.
To answer your questions:
How do you implement?
Create a column that is populated by mod (sequence_number, num_desired_partitions) and then do a range partition on that column. (see first example below)
But if you have access to the hash partition feature, then you can use hash partitions (see second example below) that will have pretty much the same distribution of row count against partitions.
How is the performance?
Don't know, never really tried the range partition method I suggested.
Can you do it with hash partitions?
Yes, see below. My opinion is: if you can do it with an existing Oracle feature, why try and write more complicated code to do it yourself?
If you want to separate the table into multiple partitions for load balancing, then the hash partition should be the right solution.
Some of our performance experts might have some educated reasons for choosing the first method over the other, but I doubt it.
-- using sequence and range partition
drop table sales ;
drop sequence sales_seq ;
drop sequence sales_partition_seq ;
create table sales
(sales_id number,
sales_partition_key number (1),
sales_date date,
sales_amt number (6,2),
item_count number (5),
constraint sales_pk primary key (sales_id)
)
partition by range (sales_partition_key)
(partition sales_p0 values less than (1),
partition sales_p1 values less than (2),
partition sales_p2 values less than (3),
partition sales_p3 values less than (4),
partition sales_p4 values less than (5),
partition sales_p5 values less than (6),
partition sales_p6 values less than (7),
partition sales_p7 values less than (8)
) ;
create sequence sales_seq ;
create sequence sales_partition_seq ;
create trigger sales_b4i
before insert on sales
for each row
begin
select sales_seq.nextval,
mod (sales_partition_seq.nextval, 8)
into :new.sales_id,
:new.sales_partition_key
from dual ;
end ;
/
insert
into sales (sales_date, sales_amt, item_count)
select a.last_ddl_time,
mod (a.object_id, 1000000) / 100,
mod (b.object_id, 100000)
from dba_objects a, dba_objects b
where rownum < 100000 ;
commit ;
select 'p0', count (*) from sales partition (sales_p0)
union
select 'p1', count (*) from sales partition (sales_p1)
union
select 'p2', count (*) from sales partition (sales_p2)
union
select 'p3', count (*) from sales partition (sales_p3)
union
select 'p4', count (*) from sales partition (sales_p4)
union
select 'p5', count (*) from sales partition (sales_p5)
union
select 'p6', count (*) from sales partition (sales_p7)
union
select 'p7', count (*) from sales partition (sales_p7) ;
SQL> select 'p0', count (*) from sales partition (sales_p0)
2 union
3 select 'p1', count (*) from sales partition (sales_p1)
4 union
5 select 'p2', count (*) from sales partition (sales_p2)
6 union
7 select 'p3', count (*) from sales partition (sales_p3)
8 union
9 select 'p4', count (*) from sales partition (sales_p4)
10 union
11 select 'p5', count (*) from sales partition (sales_p5)
12 union
13 select 'p6', count (*) from sales partition (sales_p7)
14 union
15 select 'p7', count (*) from sales partition (sales_p7) ;
'P COUNT(*)
-- ---------
p0 12499
p1 12500
p2 12500
p3 12500
p4 12500
p5 12500
p6 12500
p7 12500
8 ligne(s) sélectionnée(s).
-- using hash partition
drop table sales ;
drop sequence sales_seq ;
drop sequence sales_partition_seq ;
create table sales
(sales_id number,
sales_date date,
sales_amt number (6,2),
item_count number (5),
constraint sales_pk primary key (sales_id)
)
partition by hash (sales_id)
(partition sales_p0,
partition sales_p1,
partition sales_p2,
partition sales_p3,
partition sales_p4,
partition sales_p5,
partition sales_p6,
partition sales_p7
) ;
create sequence sales_seq ;
create trigger sales_b4i
before insert on sales
for each row
begin
select sales_seq.nextval
into :new.sales_id
from dual ;
end ;
/
insert
into sales (sales_date, sales_amt, item_count)
select a.last_ddl_time,
mod (a.object_id, 1000000) / 100,
mod (b.object_id, 100000)
from dba_objects a, dba_objects b
where rownum < 100000 ;
commit ;
select 'p0', count (*) from sales partition (sales_p0)
union
select 'p1', count (*) from sales partition (sales_p1)
union
select 'p2', count (*) from sales partition (sales_p2)
union
select 'p3', count (*) from sales partition (sales_p3)
union
select 'p4', count (*) from sales partition (sales_p4)
union
select 'p5', count (*) from sales partition (sales_p5)
union
select 'p6', count (*) from sales partition (sales_p7)
union
select 'p7', count (*) from sales partition (sales_p7) ;
SQL> select 'p0', count (*) from sales partition (sales_p0)
2 union
3 select 'p1', count (*) from sales partition (sales_p1)
4 union
5 select 'p2', count (*) from sales partition (sales_p2)
6 union
7 select 'p3', count (*) from sales partition (sales_p3)
8 union
9 select 'p4', count (*) from sales partition (sales_p4)
10 union
11 select 'p5', count (*) from sales partition (sales_p5)
12 union
13 select 'p6', count (*) from sales partition (sales_p7)
14 union
15 select 'p7', count (*) from sales partition (sales_p7) ;
'P COUNT(*)
-- ---------
p0 12603
p1 12574
p2 12581
p3 12508
p4 12342
p5 12381
p6 12382
p7 12382
8 ligne(s) sélectionnée(s).
