Re: separate data/inidex

From: Daniel A. Morgan <damorgan_at_exesolutions.com>
Date: Sun, 21 Apr 2002 15:22:00 +0100
Message-ID: <3CC2CB08.64C747AF@exesolutions.com>

Please help me here with two things.

"Nowadays to satisfy queries, you're full scanning tables, full scanning indexes, not using indexes at all,...."

Since when? I work very hard to not do these things.

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

I'll agree that I can't put an equals sign between them. But isn't one way of guaranteeing that the goal will be met (I said one way, not the only way) to force a physical separation?

Thanks,

Daniel Morgan

Connor McDonald wrote:

> 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..."
Received on Sun Apr 21 2002 - 09:22:00 CDT