Re: separate data/inidex

In article <3CC1F255.F517701C_at_exesolutions.com>, "Daniel says...
>
>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
>

I'm giving a presentation next week. It's on developing successful applications. My favorite slide in there has two words on it:

Question Authority

It is followed by a bunch of slides that have quotes from respected authorities like this:

o segments should be in as few extents as possible o the most selective fields must/should be first in an index o when coding plsql, ALWAYS use explicit cursors o rebuilding an index will save space and increase performance o you should commit frequently to save resources and time o index space is not reused
o adding more cpu will make all systems faster for sure o a cold backup is better then a hot one

and more. I'll bet you can find each of those statements in a book by a famous and respected author ;)

If enough people "believe" something or enough people say the same thing -- does that make it true?

The theory behind putting index and data on separate devices goes like this:

"you read the index, then you read the data. We'd like to leave the head positioned in the index structure just so -- so that the next read on it doesn't require head movement". (or something like that).

Well, the problem with that is

o an index is a data structure. The next block you need to read from it *is not anywhere near the block you just read* in general (the reads of an index structure in a range scan are single block reads all over the place).

o a table is generally a heap. When you read the index block, the rows pointed by it may be on 1 data block or 100 data blocks and the 100 data blocks are generally NOT physically colocated by eachother.

The odds of the heads "remaining" in place is sort of silly as well. We do have many more then one user on systems these days. I cannot remember the last time I saw a "real" system that did not employ some form of disk mgmt where the physical disks were visible -- logical volumes are the rule, not the exception (not that it matters in this discussion even!!)

On a single disk to process:

select * from emp where empno = 55;

assuming EMPNO is indexed, we would do something like this:

• read the root index block from the index (SEEK, READ)
• figure out whether to go right, left, whatever in the index (assuming index has more then root block). Assume we go "right"
• read the block pointed to by the "right" hand pointer. (SEEK, READ)
• say that got us to the leaf block with empno 55 on it. That has a rowid for the block we need.
• we now access that block (SEEK, READ)

So, we did three SEEKS, and three READS. We did them serially.

So, if there were two disks -- would we be better off then with one? Nah, we still need to do three random seeks and reads.

Our goal in life, should we choose to accept it, is to get a nice even distribution of all IO across everything evenly in a multi-user system as often as possible. Today 99% of that can be achieved at the hardware/LVM layer, releasing our DBA's to do more important things like getting all of those tables to fit into a single extent ;) (wonder when someone is going to say -- Hey, we could use a cluster to fit ALL of our tables into a single extent -- think of that, instead of a terrible extent/table, we would have one extent for say 100 of our tables -- that would be great wouldn't it!!!) That last comment was a joke, please don't anyone take it seriously....

>
>
>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)
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>

--
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