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

"DA Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3E9066A2.1473061A_at_exxesolutions.com...
> Thomas T wrote:
>
> > I recently asked how to save a system variable into an Oracle database,
and
> > Sybrand Bakker, DA Morgan, and Hans Forbrich gave me some ideas I hadn't
> > thought of (using a PL/SQL package constant, using a login trigger,
etc).
> > The general idea revolved around using the PL/SQL package to pull the
value
> > once from a table, instead of referencing the table over and over and
over
> > again.
> >
> > 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)?
> >
> > 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
> > tablespace? Restrict the storage to a few kilobytes? Keep the
tablespace
> > from extending itself?
> >
> > Should I create a two-column, multi-row table anyway, for "future
> > implementation"?
> >
> > Thanks!
> >
> > -Thomas
>
> I like the answers Hans and Mark have provided but I routinely build a
> parameters table into applications I build. The table has two columns and
serves
> the same purpose as one might find were v_$parameters a table.
>
> It allows me to load a variety of initialization parameters that are
required
> universally by an application. Some examples of possible parameters are:
>
> 1. company licensed to operate the software
> 2. number of licensed users
> 3. minimum acceptable date entry (to be used by triggers that do date
> validation)
> 4. address of email server
> 5. email address responsible DBA / SysAdmin resouce for UTL_SMTP
>
> I am against ever hard-coding parameters that may later require
modification.
> And sometimes it makes sense to encrypt those entries such as the number
of
> licensed users.
>
> Daniel Morgan

Daniel, how much space do you allow for this table? If I design a table with pname varchar2(15) and pvalue varchar2(60), assuming an average parameter name of 12 characters and average value of 10 characters, and using IOT design, I get a row requirement of 26 bytes/row. The database uses an 8k block size; if I drop the pctfree down to 1% for a free space of 81 bytes/row, I can store 309 rows in one block! I can't see myself ever storing 309 parameters; I think -max- parameter storage would I think be around 10 or 20!

Would I really set the table's storage size as follows?

create table ALLMINE.PARAMS (

    pname varchar2(15) primary key,
    pvalue varchar2(60) not null))
organization index pctfree 1 pctused 80

    storage (initial 8K next 4K pctincrease 1 autoextend off) tablespace MYSTATICDATA;

Seems funny to see a table sized at 8K when there's gigs of available HD space...

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