Re: Databse File layout on only 4 drives Ideas?

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<l9pZ9.34802$jM5.89167_at_newsfeeds.bigpond.com>...
> "David Platt" <david-platt_at_cogeco.ca> wrote in message
> news:AepY9.54719$L47.8070994_at_read2.cgocable.net...
> > I would argue that one drive should be dedicated to archive desitination -
> > don't want to be sharing that if we lose a disk.
> >
> > I am quite curious as to why a couple of you have written off the idea of
> > splitting data and index across drives. This is a practise that I have
> > followed for a while and I am wondering why it is being written off so
> > quickly
>
> Because (here goes!) there is no intrinsic performance benefit to be gained
> by housing indexes and tables separately. That would only be a possibility
> if indexes and tables were read and written simultaneously, thus introducing
> a contention issue, but they aren't. Table and index reads are serialised,
> and writes are at the mercy of the LRU list and DBWR's own flushing
> schedule.
>
> They *might* contend, of course. But they might not. In the absence of a
> suitable number of hard disks, and in pursuit of the simplest rule for the
> most common situations, then index and table separation is a waste of time.
>
> But it needs monitoring on a segment-by-segment basis to find the
> exceptions.
>
> Regards
> HJR
Howard,

Tables are the type of segment that I would like to keep in a tablespace whose datafiles I would never want to offline drop. Indexes are the type of segment that I would consider offline dropping in a bad circumstance. I also might have all of the objects (indexes) in that tablespace created with the attribute "NOLOGGING" and might keep the indexes with that attribute set. The indexes can be rebuilt. This is no revelation - I'm sure you have been thru far more recovery scenarios that me. So - even though you're on board with "indexes and tables need not be separated onto separate disks", I still believe that they do not belong in the same tablespace, as their backup/restore/recovery requirements differ significantly (nologging).

If the datafiles for each tablespace (and maybe we're using the Juan Loiza strategy of small, medium and large extents for each) are striped across the same disks, so be it. But they - imho - do not belong in the same tablespace.

Paul Received on Wed Feb 05 2003 - 22:58:21 CST