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

"Hans Forbrich" <forbrich_at_telusplanet.net> wrote in message news:3E8FCE58.DA81CCE1_at_telusplanet.net...
> Thomas T wrote:
>
> > How acceptable are one row, one column tables? I've heard of people
storing
> > multiple system variables in two-column, multi-row tables, with column
names
> > such as "parameter_name" and "parameter_value". But is using a one
row/one
> > column table common? Or is it a way that works, but of bad design (like
> > creating multiple public rollback segments in the System tablespace)?
>
> A 1-row, 1-column table is not a bad thing. It tends to look silly, and
many
> purists will argue against it. But, remember the most famous table of all
> "DUAL"!
>
> However, with a bit of thought, you could expand that single column to add
a
> description, and effective & expiry date, and a whole lot of other things.
> Surprisingly, the extra columns will come in handy about 12 to 18 months
later,
> when management decides they want a specific report that happens to be
enabled
> through that column.
>
> A further bit of thought, and you end up building a 'domain' table, as
recently
> discussed in cdo.server and the single row, single column table becomes
part of
> a much more interesting environment.
>
> >
> > Are there any particular ways I should define this table? Do I -need-
an
> > index? (It's only one value.) Should I store this table in the system
>
> If you stick to the 1x1, avoid creating an index. You do not want to
increase
> disk access by accidently accessing the index. That said, you might want
to
> consider an IOT, and you might wnt to consider constraints such as unique
and
> not null.
>
> Consider pinning this at startup.
>
> > tablespace? Restrict the storage to a few kilobytes? Keep the
tablespace
>
> I generally dislike putting anything not owned by the system into the
system
> tablespace. Partly because of maintenance (if I'm sick, the DBA on duty
needs
> to start hunting and wasting time). Put it into a LMT, keep the extents
as
> small as possible, see if there is a reason for creating a small read-only
> tablespace with this and a few other relatively static tables.
>
> > from extending itself?
>

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.)

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.

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)?

Thanks again,

-Thomas Received on Tue Apr 08 2003 - 16:35:24 CDT