Re: separate data/inidex

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