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

"Ed Prochak" <edprochak_at_adelphia.net> wrote in message news:3E93AB5E.80701_at_adelphia.net...
> Hans Forbrich wrote:
> >>Would it be wise, for backup/recovery reasons, to create a tablespace
that
> >>is read-only? AFAIK, Oracle doesn't back up such a tablespace. Or, is
that
> >>the idea behind a static table? Any idea as to how small of a
tablespace I
> >>should create? Can I create a tablespace of 1 block size (8k)?
> >
> >
> > No - IIRC the smallest tablespace is about 50K - 6 blocks. Is disk space
that
> > expensive where you are that you need to be worried about it to this
extent?
> > Also, I'm concerned - the way you ask the question leads me to believe
you
> > design 1 table per tablespace. Is this true?
> >
> > In any designs I have done, there is always at least one tablespace for
> > miscellaneous tables. Many applications tend to have a few large
tables, and
> > then dozens or hundreds of small 'supporting' tables. In my experience,
these
> > tend to be relatively static, updated once a month to once a few years.
I
> > simply put most of these types of tables into one tablespace & then
heavily
> > optimize the access. Yes, this is a throwback from Oracle 6 & 7 and
before high
> > efficiency disk arrays but it certainly seems to have simplified admin
for me.
> >
> >
>
> I can second this idea. In a conversion or test environment, there tend to
be
> many tables that are constant from one run to the next. Keeping them
separate
> can save a lot of time in these cases.
>
> --
> Ed Prochak
> running http://www.faqs.org/faqs/running-faq/
> netiquette http://www.psg.com/emily.html
> --
> "Two roads diverged in a wood and I
> I took the one less travelled by
> and that has made all the difference."
> robert frost
>

Hans, sorry if my phrasing was poor, but I don't create one table per tablespace. I have one main tablespace, APPDATA, which holds all the main tables for my application. I also have APPINDEX for all the indexes, and APPUSERS and APPTEMP.

We actually have -tons- of diskspace available; about 300 GB free (raid 5). Since the disks aren't separate, I figured it was better to have smaller tablespaces, to avoid fragmentation, and to reduce the physical access time. Is my reasoning valid? I toyed with the idea of creating a 10 GB tablespace for my main areas, and never having to worry about tablespace ever againbut  wound up using a 50 MB tablespace instead. (laughs) Although, our old system, Oracle 7, does have very small tablespaces; and our support was always anxious about extending our tablespace. The same fear might have "autoextended itself" onto me!

I was planning on adding APPSTATIC for this little table, and the possibility for future tables. It's the same thing that you and Ed P agreed on. (Thanks for the input, Ed! I appreciate it!) But that familiar fear of our support team seemed to have resurfaced in my message; that's probably why I asked about an 8k tablespace. Looking back, I can see how that made no sense at all. In fact, I'm not sure why we ever listened to support. I found a 100 MB rollback segment in the system area that they created. Going through old logs, I found out that it was part of a big table archive operation- but they forgot to turn off the 100MB rollback segment, and we were running off it for 2 years!

Daniel mentioned that 10i phases out the dictionary managed tablespaces, which goes back to your mention of an LMT early in this discussion (message ID <3E8FCE58.DA81CCE1_at_telusplanet.net> ). I read a bit about the LMT on the net after you mentioned it, but I didn't realize it was being phased out. It makes me wonder about going back to my initial setup, and recreating those tablespaces to be locally managed; but that'd be a hell of an export that I don't think would be wise on a production system. Maybe later this year, right after we get the 9i upgrade, I'll have a chance... anyway, should I just tell this APPSTATIC tablespace to be locally managed, with a uniform size of 256K? (That's the only mention of LMTs in my 8i DBA Handbook, by Oracle/Osborne Press.)

Thanks!

-Thomas

(Wow, this thread is getting rather hard to follow!) Received on Wed Apr 09 2003 - 13:18:10 CDT