Hypothetical questions

I am looking for some input from some people out there who have experience with large Oracle databases on the following questions:

1. BLOBs -- what is the largest number (as in number of rows) and size (as in size of the entire BLOB table(s)) of BLOBs that people out there have stored in an Oracle database (I am talking about actual internal BLOBs, not BFILES)? If you had the chance to redesign your data model from scratch, would you continue to store your BLOBs internally, or would you look at storing them outside the database and why?
2. Table structures -- If you have several lookup tables that basically consist of an id and a description, each describing a different type of entity, would you:
   1. create a separate table for each set of lookup values, e.g. one table for hair color and one table for eye color and one table for title and one table for occupation, etc.

    ---OR----
   b) create one table that had all the lookup values in it, then relate each row to a "parent" or "type" that describes the purpose of that particular item. e.g.:

   table: all_types

     type_id      integer
     description  varchar(40)

   table:  types_to_types
     parent_id    integer
     child_id     integer

  where parent_id and child_id are foreign keys to the all_types.type_id table, and for each set of types (e.g. eye color, etc.) there is a record in the all_types table describing the "category" of the types.

  Which method would you choose and why?

3) Char vs. Varchar2? I have heard the following arguments for char vs. varchar2:

1. argument 1: You should always use varchar because you save space.
2. argument 2: Use char when all possible values stored in a column are about the same length, use varchar when they can have widely different lengths.
3. argument 3: Use char instead of varchar, because varchar can cause a row to span multiple blocks, thus taking longer to retrieve data.

   Which philosophy (a, b, or c, or another different philosophy altogether) would you choose when designing a data model and why?

Any thoughts on these items would be greatly appreciated. Thanks!

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 11 2000 - 21:41:05 CST