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

From: Thomas T <T_at_T>
Date: Tue, 8 Apr 2003 17:37:05 -0400
Message-ID: <3e934101$1@rutgers.edu>

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:2687bb95.0304060913.19f57085_at_posting.google.com...
> 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 --

Mark, thanks for the tip about contexts. I can't find much in my Oracle documentation about contexts; but I found some on the 'net. Seems like a good idea, along the lines of a login trigger. Looks like I'll have to pick up an advanced PL/SQL book! (I wonder why my 8i "DBA Handbook" only has one paragraph about contexts.)

-Thomas Received on Tue Apr 08 2003 - 16:37:05 CDT