RE: partitioning

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 24 Mar 2003 19:11:17 -0800
Message-Id: <24765.322954@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C2F27C.3399A910
Content-Type: text/plain;

charset="iso-8859-1"

>-----Original Message-----
>From: Basavaraja, Ravindra [mailto:Ravindra.Basavaraja_at_T-Mobile.com]
>
>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) ;

'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).

------_=_NextPart_001_01C2F27C.3399A910
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2654.45">

<TITLE>RE: partitioning</TITLE>
</HEAD>
<BODY>

>-----Original Message----- >From: Basavaraja, Ravindra [<A = HREF=3D"mailto:Ravindra.Basavaraja_at_T-Mobile.com">mailto:Ravindra.Basavar= aja_at_T-Mobile.com</A>]
 >
 >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

<BR><FONT SIZE=3D2>&gt;records go into that partition.</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;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.

<P><FONT SIZE=3D2>--&nbsp; using sequence and range partition</FONT>
<BR><FONT SIZE=3D2>drop table sales ;</FONT>
<BR><FONT SIZE=3D2>drop sequence sales_seq ;</FONT>
<BR><FONT SIZE=3D2>drop sequence sales_partition_seq ;</FONT>
<BR><FONT SIZE=3D2>create table sales</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; (sales_id number,</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; sales_partition_key number =
(1),</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; sales_date date,</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; sales_amt number (6,2),</FONT>

item_count number (5),     constraint sales_pk primary key = (sales_id)
    )  partition by range = (sales_partition_key)

<BR><FONT SIZE=3D2>&nbsp;(partition sales_p0 values less than =
(1),</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p1 values less than =
(2),</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p2 values less than =
(3),</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p3 values less than =
(4),</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p4 values less than =
(5),</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p5 values less than =
(6),</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p6 values less than =
(7),</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p7 values less than =

(8)

<BR><FONT SIZE=3D2>&nbsp;) ;</FONT>
<BR><FONT SIZE=3D2>create sequence sales_seq ;</FONT>
<BR><FONT SIZE=3D2>create sequence sales_partition_seq ;</FONT>
<BR><FONT SIZE=3D2>create trigger sales_b4i</FONT>
<BR><FONT SIZE=3D2>before insert on sales</FONT>
<BR><FONT SIZE=3D2>for each row </FONT>
<BR><FONT SIZE=3D2>begin</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; select sales_seq.nextval,</FONT>
<BR><FONT =

SIZE=3D2>          mod = (sales_partition_seq.nextval, 8)
     into :new.sales_id,          = :new.sales_partition_key

<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; from dual ;</FONT>
<BR><FONT SIZE=3D2>end ;</FONT>
<BR><FONT SIZE=3D2>/</FONT>
<BR><FONT SIZE=3D2>insert</FONT>
<BR><FONT SIZE=3D2>&nbsp;into sales (sales_date, sales_amt, =

item_count)
  select a.last_ddl_time,         mod = (a.object_id, 1000000) / 100,
         mod = (b.object_id, 100000)

<BR><FONT SIZE=3D2>&nbsp;from dba_objects a, dba_objects b</FONT>
<BR><FONT SIZE=3D2>&nbsp;where rownum &lt; 100000 ;</FONT>
<BR><FONT SIZE=3D2>commit ;</FONT>
<BR><FONT SIZE=3D2>&nbsp;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)

<BR><FONT SIZE=3D2>union</FONT>
<BR><FONT SIZE=3D2>&nbsp;select 'p7', count (*) from sales partition =
(sales_p7) ;</FONT>

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><FONT SIZE=3D2>'P&nbsp; COUNT(*)</FONT>
<BR><FONT SIZE=3D2>-- ---------</FONT>
<BR><FONT SIZE=3D2>p0&nbsp;&nbsp;&nbsp;&nbsp; 12499</FONT>
<BR><FONT SIZE=3D2>p1&nbsp;&nbsp;&nbsp;&nbsp; 12500</FONT>
<BR><FONT SIZE=3D2>p2&nbsp;&nbsp;&nbsp;&nbsp; 12500</FONT>
<BR><FONT SIZE=3D2>p3&nbsp;&nbsp;&nbsp;&nbsp; 12500</FONT>
<BR><FONT SIZE=3D2>p4&nbsp;&nbsp;&nbsp;&nbsp; 12500</FONT>
<BR><FONT SIZE=3D2>p5&nbsp;&nbsp;&nbsp;&nbsp; 12500</FONT>
<BR><FONT SIZE=3D2>p6&nbsp;&nbsp;&nbsp;&nbsp; 12500</FONT>
<BR><FONT SIZE=3D2>p7&nbsp;&nbsp;&nbsp;&nbsp; 12500</FONT>
</P>

<P><FONT SIZE=3D2>8 ligne(s) s=E9lectionn=E9e(s).</FONT>

<P><FONT SIZE=3D2>--&nbsp; using hash partition</FONT>
<BR><FONT SIZE=3D2>drop table sales ;</FONT>
<BR><FONT SIZE=3D2>drop sequence sales_seq ;</FONT>
<BR><FONT SIZE=3D2>drop sequence sales_partition_seq ;</FONT>
<BR><FONT SIZE=3D2>create table sales</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; (sales_id number,</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; sales_date date,</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; sales_amt number (6,2),</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; item_count number (5),</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; constraint sales_pk primary key =

(sales_id)

<BR><FONT SIZE=3D2>&nbsp;&nbsp; )</FONT>
<BR><FONT SIZE=3D2>&nbsp;partition by hash (sales_id)</FONT>
<BR><FONT SIZE=3D2>&nbsp;(partition sales_p0,</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p1,</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p2,</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p3,</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p4,</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p5,</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p6,</FONT>
<BR><FONT SIZE=3D2>&nbsp; partition sales_p7</FONT>
<BR><FONT SIZE=3D2>&nbsp;) ;</FONT>
<BR><FONT SIZE=3D2>create sequence sales_seq ;</FONT>
<BR><FONT SIZE=3D2>create trigger sales_b4i</FONT>
<BR><FONT SIZE=3D2>before insert on sales</FONT>
<BR><FONT SIZE=3D2>for each row </FONT>
<BR><FONT SIZE=3D2>begin</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; select sales_seq.nextval</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; into :new.sales_id</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; from dual ;</FONT>
<BR><FONT SIZE=3D2>end ;</FONT>
<BR><FONT SIZE=3D2>/</FONT>
<BR><FONT SIZE=3D2>insert</FONT>
<BR><FONT SIZE=3D2>&nbsp;into sales (sales_date, sales_amt, =

<BR><FONT SIZE=3D2>&nbsp;from dba_objects a, dba_objects b</FONT>
<BR><FONT SIZE=3D2>&nbsp;where rownum &lt; 100000 ;</FONT>
<BR><FONT SIZE=3D2>commit ;</FONT>
<BR><FONT SIZE=3D2>&nbsp;select 'p0', count (*) from sales partition =

<BR><FONT SIZE=3D2>union</FONT>
<BR><FONT SIZE=3D2>&nbsp;select 'p7', count (*) from sales partition =
(sales_p7) ;</FONT>

<P><FONT SIZE=3D2>'P&nbsp; COUNT(*)</FONT>
<BR><FONT SIZE=3D2>-- ---------</FONT>
<BR><FONT SIZE=3D2>p0&nbsp;&nbsp;&nbsp;&nbsp; 12603</FONT>
<BR><FONT SIZE=3D2>p1&nbsp;&nbsp;&nbsp;&nbsp; 12574</FONT>

Received on Mon Mar 24 2003 - 21:11:17 CST