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

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?
Received on Sun Apr 06 2003 - 00:55:14 CST