Re: Help with 20 million records

"Steve Ashmore" <sashmore_at_neonramp.com> wrote in message news:<uooi1l5g3r7rb3_at_corp.supernews.com>...
> You may also consider using table partitioning.
> This way you can simply drop the oldest partition each month.

In addition to partitioning, make sure you understand that with LOB data, you have the option to store the data in the database blocks as a normal column or externally to the rest of the row. Given the size of the data you're talking about, I would imagine that external storage would be a performance win for you. This should let you go down path #1 rather than #2.

Also, you may want to look into interMedia if you haven't already. interMedia's goal is efficient storage of multimedia data in the database. There are a lot of features you get by storing data as interMedia types (i.e. ORDText IIRC) rather than CLOB.

Justin Cave

>
> 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 - 10:30:02 CDT