| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: separate data/inidex
This is repeated in many places but here are the first two links to it I found.
Tablespace location is determined by the physical location of the datafiles that constitute that tablespace. Use the hardware resources of your computer appropriately.
For example, if several disk drives are available to store the database, it might be helpful to store table data in a tablespace on one disk drive, and index data in a tablespace on another disk drive. This way, when users query table information, both disk drives can work simultaneously, retrieving table and index data at the same time.
http://technet.oracle.com/doc/server.815/a67772/dfiles.htm#423
Tablespace location is determined by the physical location of the datafiles that constitute that tablespace. Use the hardware resources of your computer appropriately.
For example, if several disk drives are available to store the database, consider placing potentially contending datafiles on separate disks.This way, when users query information, both disk drives can work simultaneously, retrieving data at the same time.
http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a90117/dfiles.htm#7384
You see what I mean about Oracle's own literature. You, and others here, have clearly stated that the concept of simultaneously retrieving data is not valid and yet every reference I can find at technet and tahiti states that this is the case. If Couchman and others, myself included, repeat this the source is Oracle. And the first reference, as you can see, clearly suggests separating tables and indexes to improve IO.
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:07:08 CDT
 |
 |