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

"DA Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3E935443.27946E7C_at_exxesolutions.com...
> Thomas T wrote:
>
> > 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

Well I'll be... thanks for the info, Daniel! Good thing my Oracle 8i DBA Handbook dedicates 2 whopping pages to locally managed tablespaces! You'd think a DBA Handbook would've covered a little bit more about LMT's. I take it that the tables inside the tablespaces don't need storage clauses, either?

Do you happen to know if LMT's are supported "well" in Oracle 8i? Our main system requires 8i, and they've finished testing 9i recently, and will be installing an upgrade for us sometime this summer. (Although, when they installed 8i, I might as well have done it. They left EVERY setting at it's default value- I had to go in and tune the system parameters myself. Too bad they just can't give us the 9i CD and let -us- do the upgrade). So I doubt we'll ever see 10i.

Think a uniform size of 256K (the value from this book's example) is too large for a static table tablespace? Seems each bit of the bitmap would represent 32 blocks of space. Or should I just tell it to autoallocate? Shoot; now I'm wondering if I should redefine all the tablespaces in the database.

Thanks,

-Thomas Received on Wed Apr 09 2003 - 12:45:58 CDT