Re: separate data/inidex

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