Re: Design questions for a lareg database

For DB design with performance in mind, in order of effectiveness think about this issues:

SQL statement and Data modelling (this affects the most on performance, model with how the data will be used, make decision if to normalise or not, keep derived value to speed access, e.g. if retrieve of a derived value is frequent, but update to it is rare, than keeping the derived value may not be a bad idea, though this is not normalised. e.g. avoid full table scan in SQL statement, SQL is like filter language, filter out the largest portion, then refine, so to avoid full scan several times in a complex sub-query., etc.)

Design distribution model of app. (2-tier, 3-tier, n-tier, single DB, replicated DB, distributed DB, etc.)

Design if parallel backend, such as OPS for load sharing, parallel query to allow multiprocessing of complex query (the 2nd one should actually be designed with the SQL in mind, as this needs modify the SQL to make use of the feature)

Then design of using index, partitioning, replication, etc. database tech. to speed access.

Design DB param, SGA, block size, processes, MTS or DTS, etc.

Distribute the disk I/O, check the Oracle recommended architecture, e.g. redo log on 1 disk, control log on another, data on 3rd, 4th, etc.

More RAM, as much as you can afford, this help avoid I/O

Then regularly maintain the DB, such as perform reorg regularly, purge no use data (this one can be automated by design the application to access old and new data in different tables, or tablespace), do housekeeping is important as to keep the DB in top shape.

So if you have the chance to design the App. anew, then make the most of it when designing the SQL and data model. I would recommend the following book: Oracle Design from O'Reilly. for good DB app design, and Oracle performance tunning from Oracle Press (the name may be little different, I forgot the exact name)

We can share more on the design experience I had with the Airport system I worked with, just send me email.

--
Best Regards,
Kai Yuen Kiang

MIS Analyst
Cytech Technology Ltd.
Tel:  (852) 2378-2225
Fax: (852) 2375-7700
http://www.cytecht.com
"Jim H" <jims83cj_at_nospam.yahoo.com> ¼¶¼g©ó¶l¥ó
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

>

>