Re: Large Oracle databases?
Date: 6 May 1994 21:04:18 GMT
Message-ID: <2qebci$sde_at_tisdec.tis.tandy.com>
In article <dischner-040594234302_at_u7x5201.ppp.lrz-muenchen.de>, dischner_at_med.uni-muenchen.de (Anton Dischner) writes:
|> In article <2pp8pr$255_at_access1.speedway.net>, lknutsen_at_speedway.net (Leif
|> Knutsen) wrote:
|>
|> >
|> > We are investigating the possibility of implementing a large database on
|> > Oracle 6 (to be migrated to 7 at some point - we hope :)
|> >
|> > Does anyone have experience implementing databases with row sizes of 5
|> > million or more? I'd like to know hardware and software specifics, as
|> > well as implementation and skill requirements.
|> >
|> > This would be incredibly helpful. Please post here or send me e-mail, as
|> > you feel is appropriate.
|> >
|> > Thanks in advance.
|> >
|> > Leif Knutsen
|> Hi Leif,
|>
|> We are running a Oracle 7.0.12 database with one table with 20 mio records.
|> The table occupies ca 3 gig and the index about 2 gig.
|> I would strongly recommend to use the v7 because migrating is difficult and
|> time intensive.
|> Be aware of following problems if you do an export.
|>
|> An export should be no problem; remember to first to compress the extents
|> or not.
|> If you do an import, your rollback segments have to be large enough or use
|> autocommit
|> (every 100000 records or so).
|> You might have to create the table and index first because creating an
|> index on an existing
|> large table might need very much free tablespace.
|> Remeber to plan your segmenting carefully. With, for example blocksize 4096
|> (standard)
|> maxextents is about 250.
|> An import into an unindexed table is much faster on the other hand.
|> The insert performance is constatnt, regardless of the size of the table.
|> Be sure to access data using an indexed key.
|> If you do somthing like 'select * from mytable where name = 'MILLER'
|> 'without an index
|> might take easily longer than one hour.
|>
|> I hope this helps,
|>
|> Toni
I would just add one note to the above. If you do decide to import the table without creating the index(es) before the import then you can up the sort_area_size parameter in the init*.ora file and restart the database to greatly reduce the amount of freespace that is needed to create the index. When doing this we usually get a quiet system and up the sort_area_size to about half of the free memory - like 60 M.
Geoffrey Bray | The thoughts and views herein do not Tandy Information Services | necessarily reflect that of Tandy geoff_at_tisdec.tis.tandy.com | Corporation. ------------------------------------------------------------------------------I wish I were the government so my budget wouldn't have to balance.
Received on Fri May 06 1994 - 23:04:18 CEST