Re: separate data/inidex

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.

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.

"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 Fri Apr 19 2002 - 15:11:50 CDT