Re: Help with 20 million records

You may also consider using table partitioning. This way you can simply drop the oldest partition each month.

Stephen C. Ashmore
Brainbench MVP for Oracle Administration http://www.brainbench.com
Author of: 'So You Want to be an Oracle DBA?'

"Emery Lam" <lam94618_at_yahoo.com> wrote in message news:8ac2135d.0209210138.5259839e_at_posting.google.com...
> 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 - 05:21:45 CDT