You could write a book on this :) I called my Expert Oracle Database Architecture - and you would be interested in many of the chapters but in particular the ones on datatypes, tables and indexes.


You might need partitioning - to spread inserts out over many segments, to avoid hot spots in right hand side indexes (on dates, on sequences for example)...

You might need index organized tables - a little slower in most cases to insert into but if you query up multiple rows that arrive randomly over time in the table - it could be used to cluster those rows together making retrieval more efficient.

You probably want to use Automatic segment space management (ASSM) for concurrency reasons - to avoid having to figure out what pctused, freelists and freelist groups should be set to (but you should understand what the difference between manual and auto is...)

You'll want to understand how datatypes are physically stored so you can think about things like pctfree - and how to maximize your potential performance with LOBs if you are using them - and so on.


In short - you want to understand at some level how things work. The concepts guide would be a good starting place, if you like the way I write - those chapters in Expert Oracle Database Architecture would be a good starting place.



You'll need to think about concurrency - things like ASSM, partitioning and even clustering techniques (IOT's - index organized tables, hash/btree clusters) can be used.

You'll need to think about archiving over time perhaps.

You might need to consider the need to reorganize every once in a long while - and to design to facilitate that (think partitioning again)