Re: separate data/inidex

This is a very sophomoric dissection, if I may call it that, of this issue.

You know full well that spindle diversity, particularly between (in my triage) tables and indices, and then secondly, further separation of concomitantly used tables and their indices, is a key to performance. Further, of course, making sure tedious items like REDO and TEMP don't caught in the crossfire.

It is a trite and juvenile statement in asserting "well, then, every table should have its own tablespace", etc. A careful analysis of just what is being used, how much, by whom, in what volume, and when, is how I plan out my storage on a physical and logical basis.

For example, flat tables loaded by SQL*Loader should reside separately from their derivative tables. It goes without saying that the residence of the flat ASCII feeder files should not be on the same spindles, either. The derivative tables and the tablespaces that hold them should be diversified based on their volume of concurrent demand, and needless to say, the tablespaces holding their indices ought not to be on the same spindles. Of course, if one has a 95% DSS application, the worry of simultaneous update and read activity and disk heads flying all over the place is not as much of a concern. And with humungous DB_BLOCK_BUFFERS and DB_BLOCK_SIZE and the rest, the performance hit would not be too bad.

SYSTEM, TEMP, and the REDO log files should be judiciously isolated from this madness on other devices.

Of course, if you have only one disk or two, you are quite right in saying it wouldn't make much difference, apart from the fact that defining the tablespaces now, despite their residence, would make rebuilding the database on a larger platform, with more diverse storage available, would be eased greatly.

I guess I missed the part where the original poster of this question said that only one session / connection to the database would be in use. Lord knows, I probably did, in which case I apologize profusely.

RSH. "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:a9r92f$vjm$1_at_lust.ihug.co.nz...
> Guy D. Kuhn wrote:
>
> > There is a very "physical" reason for separating indexes and tables,
> > naming
> > disk heads. They can only be over one disk cylinder at a time. If the
> > disk is reading the index, it can not be reading the data block.
>
> There is ZERO physical reason for separating indexes and tables, period.
>
> >
> > If you only have one session accessing the table at a time, no problem.
> > Likewise, if you have enough memory for enough db_block_buffers to
remove
> > contention, no problem.
> >
> > But since an index access requires reading the index at least twice and
> > the data block at once, placing them on different disks (not
tablespaces)
> > can improve performance by removing physical I/O contention.
>
>
> Not true. The only reason for separating indexes and tables into separate
> tablespaces is management issues. Anything else is just buying in to old
> myths.
>
> On this basis, every damn table needs to be in its own tablespace on its
> own disk. After all, you've got lots of tables, and each of them are being
> accessed simultaneously (never mind that index accesses and table accesses
> are not simultaneous). So if you permit two tables to reside in the same
> tablespace, why not a table and its index???
>
> Performance-wise, there's nothing in it.
>
> Regards
> HJR
>
>
> >
> > "Howard J. Rogers" wrote:
> >
> >> There is precisely zero physical reason for separating indexes and
> >> tables, at least in routine database configurations. No performance
gain
> >> arises if
> >> you do. There are real *management* issues (tables *need* backing up,
> >> indexes don't). And the rebuild command is fine to move the indexes
> >> somewhere else if you decide to do it; but then the 'alter table X
move'
> >> command will do much the same by shunting the tables off somewhere new,
> >> too.
> >>
> >> Regards
> >> HJR
> >>
> >> "Troy Meyerink" <meyerink_at_usgs.gov> wrote in message
> >> news:Gus70n.CA3_at_igsrsparc2.er.usgs.gov...
> >> > You need to create an index tablespace and then move the existing
> >> > indexes
> >> by
> >> > issuing an
> >> > 'alter index rebuild' statement.
> >> >
> >> > Troy Meyerink
> >> > Oracle DBA
> >> > Raytheon
> >> >
> >> >
> >> > "Titi" <thierry.constant2_at_wanadoo.fr> wrote in message
> >> > news:3cbf2607$0$15182$626a54ce_at_news.free.fr...
> >> > > Hi,
> >> > >
> >> > > For a user, I have data and index in one tablespace.
> >> > > I want to separate data and index ( good idea ??)
> >> > >
> >> > > How can do that ???
> >> > >
> >> > > Thanks in advance ...
> >> > > ( oracle 8.1.7)
> >> > >
> >> > >
> >> >
> >> >
>
Received on Sun Apr 21 2002 - 02:01:46 CDT