It migfht be worth digging out the SAME whitepaper from Oracle that covers
much of the same ground as well.
That recommends striping across multiple disks to get even IO as Tom,Howard
& Jonathan have been saying. It also recommends a stripe size of 1mb. It
does have the merit (from memory) of being based on extensive testing as
well.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Daniel Morgan" <damorgan_at_exesolutions.com> wrote in message
news:3CC4351E.244AADC3_at_exesolutions.com...
> So is Tom Kyte another one of those 'experts' we should ignore? Just
kidding? Or can
> we get corrections to these things pubished in Oracle Magazine so that
they get wider
> distribution. Perhaps you could make your slideshow presentation available
for
> download at AskTom? And, if so, I would like permission to present it to
my students.
>
> Well here I go revising the curriculum again. ;-)
>
> Thanks.
>
> But can I get you to weigh in with a repacement rule or guideline?
>
> Thanks again.
>
> Daniel Morgan
>
>
>
> Thomas Kyte wrote:
>
> > In article <3CC30C98.4977F6F5_at_exesolutions.com>, "Daniel says...
> > >
> > >If the only reason you are building an index is for scanning it why not
an IOT?
> > >
> >
> > the index is a "skinnier" version of the table.
> >
> > an IOT is organized by the primary key. I needed a non-primary key set
of
> > columns...
> >
> > >With the respect to the rest of it ... fascinating.
> > >
> > >So who at Oracle is charged with correcting all of the information that
goes out
> > >under
> > >its name. Not just at Oracle Press, but at technet, tahiti, etc.
> > >
> >
> > it is hard to kill myths, they die slow painful deaths many times.
> >
> > if you point me to explicit doc refs, I'll "bug them"
> >
> > Oracle press -- cannot do a thing about that. anyone can publish for
Oracle
> > press.
> >
> > >Once again thanks for the clarification.
> > >
> > >But is there a general rule that developers can follow. Each
development project
> > >can
> > >not turn into an exploration of what "common knowledge" is wrong. Or
trying out
> > >a
> > >multitude of tablespace, extent combinations to find the one that is
optimal.
> > >Some
> > >rules must be applied or no rules are applied.
> > >
> > >Thanks,
> > >
> > >Daniel Morgan
> > >
> > >
> > >
> > >Thomas Kyte wrote:
> > >
> > >> In article <3CC2CB08.64C747AF_at_exesolutions.com>, "Daniel says...
> > >> >
> > >> >Please help me here with two things.
> > >> >
> > >> >1. "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.
> > >>
> > >>depends on the system you are building. Last benchmark I did ran best
without
> > >>indexes on the tables in most cases. DW vs OLTP.. Full scan away.
Every now
> > >> and again I would create an index for the sole purpose of using it as
a
> > >> "skinnier" table and fast full scanning it.
> > >>
> > >> >
> > >>>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?
> > >>
> > >>no, could be that indexes are more "cacheable" -- you could end up
with phyiscal
> > >>IO on the data and none on the index after a bit. Now you have an hot
disk and
> > >> a cold disk.
> > >>
> > >>Or conversely -- if the cacheability of the index is questionable, you
could end
> > >>up doing 3 or 4 physical IO's on the index to get to a single data
block (that
> > >> was already cached)
> > >>
> > >> Now, if I took a single tablespace with two datafiles -- one on
disk1, one on
> > >> disk2 and created the index and table in the same tablespace and used
lots of
> > >>extents for each -- I would achieve a nice even striping across the
two disks --
> > >>evenly distributing the data across the two devices (as we tend to
allocate an
> > >>extent from datafile1, then datafile2, then datafile1 and so on for
the objects
> > >> -- in the goal of spreading the IO around)
> > >>
> > >> Now -- regardless of the cacheability (or lack thereof) I get a nice
even
> > >> distribution of physical IO in most/many cases...
> > >>
> > >> Hey another reason why the "single extent theory" doesn't hold up ;)
> > >>
> > >> >
> > >> >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..."
> > >> >
> > >>
> > >> --
> > >> Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
> > >> Expert one on one Oracle, programming techniques and solutions for
Oracle.
> > >> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> > >> Opinions are mine and do not necessarily reflect those of Oracle Corp
> > >
> >
> > --
> > Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
> > Expert one on one Oracle, programming techniques and solutions for
Oracle.
> > http://www.amazon.com/exec/obidos/ASIN/1861004826/
> > Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Mon Apr 22 2002 - 11:39:48 CDT
