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

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

Thomas, my partner and I converted our systems to uniform extents when we migrated platforms about 1.7 years ago. Uniform extents work wonderfully from a space management point of view, and I believe also from a performance point of view not that you notice in normal operations.

To manage space I just count the free extents in a tablespace and divide by the average usage per week (kept in a spread sheet) that gives me a weeks remaining amount of space. When we approach a low level we add a file, migrate an object, or run re-organization on objects we know will release most of their space if reorganized (usually indexes built on columns that are modified after initial insertion, and tables with certain update patterns).

We chose to build every tablespace using one of two extent sizes. Small objects use 512K extent sizes and large objects use 20M extents.  To quality as large the object had to exceed a certain size, which I think was 50M at creation. We generally assign any new table we expect to grow to less than 100M to the small tablespaces (200 extents) and objects over this to the large. On a second db we used 256K and 5M as our sizes, but nothing exceeds 40 extents on that system.

I would suggest looking at your data and pick some sizes and run a couple of queries to see what kind of breakdown that would give you and pick sizes that fit your data. Our goal was to pick sizes such that objects would take an easy to deal with at a glance number of extents. That is I can deal with an average usage per tablespace of 1 - 10 extents easier than 100 - 300 off the top of my head as I am not a numbers type of person.

I hope these ideas give you something to think over.

IMHO -- Mark D Powell -- Received on Fri Apr 11 2003 - 09:23:35 CDT