Refactoring Very Large DB's

Gentlemen,

I worked on a project which had a single table expected to go to 14 terabytes at a rate of 2 terabytes a year. Are there any design principals which would be useful in avoiding creeping death in this case?
[ July 25, 2006: Message edited by: Ilja Preuss ]

Don, is that table just a container for large objects? Or are there just oodles of records being added?

Oodles of records, Ken. Several hundred thousand a day & all have to be kept by contract. We partitioned the table based on whether the record was active or not.

Yes, you probably want to partition this table, otherwise you'll run into performance challenges. Is this just a log? Can you organize into "active right now", "maybe active at some point", and then a bunch of "we'll likely never need these again" tables?

- Scott

Nope it wasn't a log, Scott, but a working table. I wanted to archive the old records but the requirements were developed at a much higher level (they should have shot the architect who put that on). So we partitioned into active and inactive records and twelve streams to boot (with twelve thread parallel processing) to do the batch job. I noticed that they weren't using bind variables and threw those in which raised performance about 30%. This was an Oracle DB - not sure whether that's a Db pattern or an Oracle-specific pattern.

I asked the question to see whether we missed any tricks?