Re: Are one row, one column tables "acceptable"?

Thomas T wrote:

> Hans, thanks for your ideas & opinions! I can't seem to find anything in my
> Oracle documentation, books -or- 8i EE CD, that mentions a domain table. I
> also can't find the messages in cdo.server that you referenced. Do you
> happen to know the message ID of the discussion from the cdo.server group?
> Is the domain table something that came out with 9i? (I'm running 8i.)

Domain encompasses the concept of 'range of valid values'. About once every 3-6 months, someone asks 'how do I handle a dynamic list of "lookup" value' or 'how do I optimize these n-hundred tables that only have 2-30 rows' or some such. Then the discussion starts again about a 'central universal look up table' or a 'doamin table', etc. You will not see it in the docco, but virtually every experienced programmer/DBA has created at least 1 variant.

The concept, in it's simplest form is a table whose columns are of the form (domain_or_way__the_code_should_be_used varchar(n), actual_code_value varchar(n2), meaningful_description_of_code varchar(n3)). effective_date and expiry_date are also occasionally added. Then all these little lookup tables can be put into one master 'domain' table, and the original little table replicated using a view that extracts the codes based on the domain_or_way__the_code_should_be_used column.

Last time we discussed was about 2 weeks ago - sorry I can't remember the thread name.

> An IOT is a good idea; I think I'll definately use it. This will be a very
> static table, after all; and a very small table at that. And the table
> becomes even smaller with IOT, since I the Oracle-generated rownum will be
> excluded from each row! I'm leaning towards Daniel's implementation for
> now, since it allows for future design improvements. I checked with my
> team, and it's rather inconclusive as of now as to whether an effective
> date/expiry date, or other such columns, would be helpful. Since this
> information will only be used by programs run "internally" at my office, I
> could re-design the layout later without affecting the bulk of the users.

Certainly a suitable solution.

>
> Would it be wise, for backup/recovery reasons, to create a tablespace that
> is read-only? AFAIK, Oracle doesn't back up such a tablespace. Or, is that
> the idea behind a static table? Any idea as to how small of a tablespace I
> should create? Can I create a tablespace of 1 block size (8k)?
>

You create the tablespace, update the data, take a backup, then set it read only. The backup is valid because you have set the tablespace read-only, and you do not need to back it up again until you 'set it read/write, update the data, take a ...' Received on Tue Apr 08 2003 - 17:27:02 CDT