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?

Thomas, I believe the answer on if a one column one row table is a good or bad thing depends on its purpose and on if your application design ends up with several of them.

In a case where you just want to make a value available you have several options including Oracle Contexts [create context; select sys_context()] as well as those techniques you already mentioned. In the case where you have several one column one row tables you might want to consider a multiple row two column table where the first column is the parameter name and the second column is the matching parameter value.

Just another option. -- Mark D Powell -- Received on Sun Apr 06 2003 - 12:13:05 CDT