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

Home -> Community -> Mailing Lists -> Oracle-L -> partitioning options for a system that will add 50 million rows/day

partitioning options for a system that will add 50 million rows/day

From: <ryan_gaffuri_at_comcast.net>
Date: Tue, 11 Sep 2007 00:20:30 +0000
Message-Id: <091120070020.9277.46E5DF4E0004D5270000243D2207021553079D9A00000E09A1020E979D@comcast.net>


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?

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Sep 10 2007 - 19:20:30 CDT

Original text of this message

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