Re: Design questions for a lareg database

1. Make sure you use bind variables.
2. Use bind variables.
3. If a field is empty in a row then it takes up a byte (unless it is the last field in a table then it does not). I am assuming you are going to use varchar2 not char (fixed length).
4. A view is a logical entity. Thus if you issue a query against a view Oracle mathematically puts the two together and then fetches the data. Oracle does NOT copy the view data into a table and then use your sql statement against the table. 5.Get Jonathan Lewis's Book I believe it is called Building practical Oracle 8i databases. Excellent book. Also get Thomas Kyte's new book Expert On-on-one Oracle. Also read the application developer's part of the manual.

Jim

"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 Tue Jul 17 2001 - 03:09:51 CEST