Re: Design questions for a lareg database

Book
Essential Oracle 8i data warehousing by Gary Dodge and Tim Gorman in Wiley ISBN: 0-471-37678-7 Normalization is good but sometimes can generate less performance, just be careful what you are doing.

In oracle you can get a better performance spreading your table on different drives on multiple files, etc. Look for partitioning.

It is crucial that you declare an Oracle Block size big enough to keep full records, to avoid migrating or chaining.

There is much more to be considered.

Good Luck

George
ghita_at_att.net

"Jim H" <jims83cj_at_nospam.yahoo.com> wrote in message news:0FE47.10368$rK3.836781_at_news1.onlynews.com...
> I am a programmer who has written apps to connect to and manipulate data
> from Oracle databases. My company has given me the opportunity to help in
> the design of a database for a large core product I worked on and
 supported
> in the past because of my knowledge of the product and my experience using
> databases. I will be doing this design with someone who has been
> maintaining and designing Oracle databases for some of our other products.
> We are designing this database from scratch. It is not an upgrade or a
> modification of an existing database. My company is porting a product to
> use Oracle for its next release. This will be a fairly large database and
 I
> have some performance related questions. The database it currently uses
 is
> proprietary, text based and split into roughly 300 databases per machine.
> It is not a SQL database. Applications can not connect to this database
> from other machines, nor can they connect to more than one database at a
> time from a single process. I tell you this because the current database
> design had to take all of these limitations into account. The old design
> does nto really apply to what the new database will be.
>
> We are planning to use Oracle 8i on some HP-UX 10.20 and 11.0 servers.
> The main table will hold 32 million+ records.
> The database will be hit heavily during the day for small amounts of data
> and heavily at night for larger amounts of data for reports needed by the
> morning.
>
> Questions:
> Is it the size of the record or the number of records that has the biggest
> impact on database performance? There are many fields that will not be
 used
> by most of the programs connecting to the database so i could move them
 out
> to other tables to decrease the size of the record. If byte size doesn't
> matter I may as well leave them in with the record to prevent multiple
> lookups.
>
> When you create a view from multiple tables the database is soing a join.
> Does the database actually create an entire table that contains the data
> from both tables in the join? This seems like it could be very
 inefficient
> and slow on large tables and maybe, in certain cases, the design should
> duplicate data rather than spread it around to different tables.
>
> Also are there any books you would recommend on database design. I know
> about normalization and not duplicating data, and that makes a database
> efficient on size, but does it make for a slower database to follow these
> rules strictly?
>
> Any help you could give me would be greatly appreciated,
> Jim
> --
> To reply by email remove NOSPAM from my address
>
>
Received on Mon Jul 16 2001 - 22:40:21 CEST