Re: separate data/inidex

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)
>> > >
>> > >
>> >
>> >