Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: partitioning

RE: partitioning

From: Basavaraja, Ravindra <Ravindra.Basavaraja_at_T-Mobile.com>
Date: Mon, 24 Mar 2003 12:23:19 -0800
Message-Id: <24765.322921@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_01C2F243.357F48E0
Content-Type: text/plain;

        charset="ISO-8859-1"

Hi Jacques,  

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.  

thanks

-----Original Message-----
From: Jacques Kilchoer [mailto:Jacques.Kilchoer_at_quest.com] Sent: Wednesday, March 19, 2003 11:52 AM To: 'ORACLE-L_at_fatcity.com'
Cc: 'Ravindra.Basavaraja_at_T-Mobile.com'
Subject: RE: partitioning

You could accomplish this with a before insert trigger and a partitioning column that contains the value 0 through 7. e.g.
create trigger
before insert
for each row
begin

   select mod (sequence.nextval, 8) into :new.partition_column     from dual ;
end ;
/

Something similar would be achieve by hash partitioning, which is easier to implement.

> -----Original Message-----
> From: Basavaraja, Ravindra [ mailto:Ravindra.Basavaraja_at_T-Mobile.com <mailto:Ravindra.Basavaraja_at_T-Mobile.com> ]
>
> I am wondering if there is any way to achieve horizontal
> partitioning in Oracle.
>
> Assuming that I have about 8 partitions for a table.When
> there is INSERT onto this table I want one record
> to be inserted into each partition i.e
> 1st record goes into partition 1
> 2nd record goes into partition 2
> 3rd record goes into partition 3
> .....
> .....
> 8th record goes into partition 8
> 9th record goes into partition 1.
>
> I guess this feature is available in Informix handled by The
> informix engine.I am not sure if Oracle has something
> similiar to this OR is it possible to design a logic and
> embede it ,but what would be the performance effect?
>
> Any thoughts or similiar ideas

------_=_NextPart_001_01C2F243.357F48E0
Content-Type: text/html;

        charset="ISO-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1">
<TITLE>RE: partitioning</TITLE>

<META content="MSHTML 5.50.4807.2300" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=749271920-24032003>Hi Jacques,</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=749271920-24032003></SPAN></FONT>&nbsp;</DIV> <DIV><FONT face=Arial color=#0000ff size=2><SPAN class=749271920-24032003>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</SPAN></FONT></DIV> <DIV><FONT face=Arial color=#0000ff size=2><SPAN class=749271920-24032003>records go into that partition.</SPAN></FONT></DIV> <DIV><FONT face=Arial color=#0000ff size=2><SPAN class=749271920-24032003></SPAN></FONT>&nbsp;</DIV> <DIV><FONT face=Arial color=#0000ff size=2><SPAN class=749271920-24032003>Have you tried this.How is the performance for an insert into a table of 100000 records everyday.Executing the trigger for every </SPAN></FONT></DIV> <DIV><FONT face=Arial color=#0000ff size=2><SPAN class=749271920-24032003>insert for high volume of data may be costly on the performance..?</SPAN></FONT></DIV> <DIV><FONT face=Arial color=#0000ff size=2><SPAN class=749271920-24032003></SPAN></FONT>&nbsp;</DIV> <DIV><FONT face=Arial color=#0000ff size=2><SPAN class=749271920-24032003>Can we achieve this or anything closer using HASH partitioning as suggested by others.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=749271920-24032003></SPAN></FONT>&nbsp;</DIV> <DIV><FONT face=Arial color=#0000ff size=2><SPAN class=749271920-24032003>thanks</SPAN></FONT></DIV> <BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">   <DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma   size=2>-----Original Message-----<BR><B>From:</B> Jacques Kilchoer   [mailto:Jacques.Kilchoer_at_quest.com]<BR><B>Sent:</B> Wednesday, March 19, 2003   11:52 AM<BR><B>To:</B> 'ORACLE-L_at_fatcity.com'<BR><B>Cc:</B>   'Ravindra.Basavaraja_at_T-Mobile.com'<BR><B>Subject:</B> RE:   partitioning<BR><BR></FONT></DIV>
  <P><FONT size=2>You could accomplish this with a before insert trigger and a   partitioning column that contains the value 0 through 7.</FONT> <BR><FONT
  size=2>e.g.</FONT> <BR><FONT size=2>create trigger</FONT> <BR><FONT 
  size=2>before insert</FONT> <BR><FONT size=2>for each row</FONT> <BR><FONT 
  size=2>begin</FONT> <BR><FONT size=2>&nbsp;&nbsp; select mod 
  (sequence.nextval, 8) into :new.partition_column</FONT> <BR><FONT   size=2>&nbsp;&nbsp;&nbsp; from dual ;</FONT> <BR><FONT size=2>end ;</FONT>   <BR><FONT size=2>/</FONT> </P>
  <P><FONT size=2>Something similar would be achieve by hash partitioning, which   is easier to implement.</FONT> </P>
  <P><FONT size=2>&gt; -----Original Message-----</FONT> <BR><FONT size=2>&gt;   From: Basavaraja, Ravindra [<A
  href="mailto:Ravindra.Basavaraja_at_T-Mobile.com">mailto:Ravindra.Basavaraja_at_T-Mobile.com</A>]</FONT>   <BR><FONT size=2>&gt; </FONT><BR><FONT size=2>&gt; I am wondering if there is   any way to achieve horizontal </FONT><BR><FONT size=2>&gt; partitioning in   Oracle.</FONT> <BR><FONT size=2>&gt; </FONT><BR><FONT size=2>&gt; Assuming   that I have about 8 partitions for a table.When </FONT><BR><FONT size=2>&gt;   there is INSERT onto this table I want one record </FONT><BR><FONT size=2>&gt;   to be inserted into each partition i.e </FONT><BR><FONT size=2>&gt; 1st record   goes into partition 1</FONT> <BR><FONT size=2>&gt; 2nd record goes into   partition 2</FONT> <BR><FONT size=2>&gt; 3rd record goes into partition   3</FONT> <BR><FONT size=2>&gt; .....</FONT> <BR><FONT size=2>&gt; .....</FONT>   <BR><FONT size=2>&gt; 8th record goes into partition 8 </FONT><BR><FONT   size=2>&gt; 9th record goes into partition 1.</FONT> <BR><FONT size=2>&gt;   </FONT><BR><FONT size=2>&gt; I guess this feature is available in Informix   handled by The </FONT><BR><FONT size=2>&gt; informix engine.I am not sure if   Oracle has something</FONT> <BR><FONT size=2>&gt; similiar to this OR is it   possible to design a logic and </FONT><BR><FONT size=2>&gt; embede it ,but   what would be the performance effect?</FONT> <BR><FONT size=2>&gt;   </FONT><BR><FONT size=2>&gt; Any thoughts or similiar ideas</FONT> Received on Mon Mar 24 2003 - 14:23:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US