From: ryan_gaffuri@comcast.net
To: oracle-l@freelists.org
Subject: partitioning options for a system that will add 50 million rows/day
Date: Tue, 11 Sep 2007 00:20:30 +0000
Message-Id: <091120070020.9277.46E5DF4E0004D5270000243D2207021553079D9A00000E09A1020E979D@comcast.net>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="NextPart_Webmail_9m3u9jl4l_9277_1189470030_0"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: ryan_gaffuri@comcast.net
--NextPart_Webmail_9m3u9jl4l_9277_1189470030_0
Content-Type: text/plain
Content-Transfer-Encoding: 8bit

I was quoted numbers of 50 Gbs/day so that assumes 1,000 bytes/row. I have not confirmed this. This database will probably grow to around 2 TBs since older data will be purged. All data will be added with bulk loads using external tables. There will be a query element that will use indexes. About 150 rows/query. Not sure what the load will be on this. Right now I am working on a partitioning strategy. We are going to use RAC and we are in 10.2.  

We will definitely use date partitions with local indexes. There has been some talk of 1 partition/hour. Some data loads may take a while. This way newer loads can start on newer partitions. 

Should I explore hash sub-partitions? I need to run tests, but I believe hash partitions will actually hurt performance of inserts since Oracle has to decided where to put the record. 

I think we definitely need ASM because its too hard to manage spreading out partitions across the LUNs on the SAN manually and since we are adding and dropping alot of partitions we would need code to determine which LUN to put partitions on. 

The data model is relatively simple. So there is not alot of complex business logic in the database. The rows are scrubbed before getting to the database. Its just straight array inserts off of external tables. 

anyone have any suggestions or comments?  
--NextPart_Webmail_9m3u9jl4l_9277_1189470030_0
Content-Type: text/html
Content-Transfer-Encoding: 8bit

<html><body>
<DIV>I was quoted numbers of 50 Gbs/day so that assumes 1,000 bytes/row. I have not confirmed this. This database will probably grow to around 2 TBs since older data will be purged. All data will be added with bulk loads using external tables. There will be a query element that will use indexes. About 150 rows/query. Not sure what the load will be on this. Right now I am working on a partitioning strategy. We are going to use RAC and we are in 10.2. &nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>We will definitely use date partitions with local indexes. There has been some talk of 1 partition/hour. Some data loads may take a while. This way newer loads can start on newer partitions. </DIV>
<DIV>&nbsp;</DIV>
<DIV>Should I explore hash sub-partitions? I need to run tests, but I believe hash partitions will actually hurt performance of inserts since Oracle has to decided where to put the record. </DIV>
<DIV>&nbsp;</DIV>
<DIV>I think we definitely need ASM because its too hard to manage spreading out partitions across the LUNs on the SAN manually and since we are adding and dropping alot of partitions we would need code to determine which LUN to put partitions on. </DIV>
<DIV>&nbsp;</DIV>
<DIV>The data model is relatively simple. So there is not alot of complex business logic in the database. The rows are scrubbed before getting to the database. Its just straight array inserts off of external tables. </DIV>
<DIV>&nbsp;</DIV>
<DIV>anyone have any suggestions or comments? &nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV></body></html>

--NextPart_Webmail_9m3u9jl4l_9277_1189470030_0--
--
http://www.freelists.org/webpage/oracle-l