Re: separate data/inidex

Daniel A. Morgan wrote:
>
> I can't believe it either. Please explain Howard.
>
> And please don't just throw out jargon like RAID and striping. This statement
> appears to fly in the face of more than a few books by well respected authors
> (not that you aren't one yourself now) as well as a lot of HTM and PDF files put
> out on technet.
>
> Daniel Morgan
>
> RSH wrote:
>
> > Mister Rogers,
> >
> > I can't believe you said this.
> >
> > Tablespaces containing tables, and tablespaces containing indices
> > corresponding to them, would be like locating matter and antimatter on the
> > same spindle.
> >
> > In RAID environments, I do not even like them sharing the same controller;
> > failing that, certainly not sharing the same RAID ranks.
> >
> > How do you arrive at this astonishing (to me at least) notion?
> >
> > RSH.
> > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > news:a9ov52$kjn$1_at_lust.ihug.co.nz...
> > > 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)
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

Separating data and indexes is a very (very) special case of the argument for balancing IO. I would suspect that it came about originally when everything when running rule based optimizer so your app was doing full scans or nested loops and that was it...Even then the advice was dubious in a multiuser database anyway...

Nowadays to satisfy queries, you're full scanning tables, full scanning indexes, not using indexes at all, just using indexes, dumping sort segments out, dumping temporary hash workareas out and 'n' permutations of all them..

Quality IO balancing is important...separating data and indexes is not the same thing

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."