Help with 20 million records

We are planning to build an archive and retrieval system to store one year of multimedia data. The database needs to hold 20 millon records total, with 1.5 million items added each month and 1.5 million items deleted each month after the initial year. The indexing requirement is rather minimum, the data will mostly be PDF with average size of 50KB, but it could be XML, HTML or JPEG/TIFF. Retrieval volume has not been determined yet but it should be fairly low, ~20%.

We are touting a few ideas:

1. Store everything in one table with data stored as BLOBs. This is the simplest approach but I am concern with performance, both with insert and delete. I would love to do this but may have difficulties convincing management that preformance is not an issue.
2. Store index data only in one table and use the filesystem for the multimedia data. We have used this approach with a 1 million item table and it performs very well. However I worry about disk fragmentation for 20M files.
3. Create one table of index and BLOBs data for each month and a lookup scheme to locate the appropriate table for retrieval. Deletion (rolloff) is simple under this approach and performance should be quite deterministic.

Hopefully someone out there can shred some lights on these approaches.  I am mostly interested in real life volume data, is 20M record too much for one table? How many insert/delete can we expect? I are fairly new to Oracle, particularly with storing BLOBs data, does it significantly degrade performance?

I would appreciate any help. Received on Sat Sep 21 2002 - 04:38:17 CDT