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

Thomas T wrote:

> "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

Dictionary managed tablespaces are dead. The arrow was fired in 8i ... hit the target in 9i ... and with 10i, later this year, will make a clean kill so forget the storage clause of the table. But ... if you are going to use a storage clause ... always make initial and next the same size to minimize fragmentation.

Yes ... I'd waste one 8K or 16K block for this table even if it only stored one or a dozen rows.

Daniel Morgan Received on Tue Apr 08 2003 - 17:59:15 CDT