Re: separate data/inidex

If the only reason you are building an index is for scanning it why not an IOT?

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.

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
Received on Sun Apr 21 2002 - 14:01:44 CDT