Re: Lots of tables in O7

scott_at_chuck.sycraft.com wrote:
...
> fewer than 20 rows (lookup stuff). Option two would be to put all of that
> lookup stuff in one table.
 

> Which is better for Oracle? Any actual experience?
 

> The answer so far is "design a good database and normalize". Yea, ok but
> lets get real also. Any reality out there?
 

> --
 

> scott
> scott_at_chuck.sycraft.com
 

> Knowledge is Good - Emil Faber 1962

Oracle attempts to keep table data in memory (SGA) where possible. This is recycled using a 'Least Recently Used' method. Thus, if you have fewer tables and they fit into memory and they are frequently used, then you will improve performance.

Normalization is a greate process ... for the analysis and understanding. It is also very important for ensuring that future requirements can be injected into the system without redesign.

However, from one point of view (pun intended), all SQL tables in Oracle (and other systems) are hidden from the user/developer. (Try to interpret the SYS-owned table structure). Therefore, all Update/Insert/Delete/Select is done against views - the principal view (that which we call a table) just happens to include table definition spec's.

As a result, I prefer to ensure NORMALIZATION USING VIEWS. My solution to your question, therefore, is to create a single look-up table an overlay it with simple views; each of these views represents an entity in the 3rd normal form.

Note, however, that I specify 'simple views' - joins in views will cause performance issues.

/Hans Received on Wed Jun 01 1994 - 23:06:00 CEST