Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Myths

Re: Oracle Myths

From: William Rice <ricew_at_operamail.com>
Date: 28 May 2002 08:30:01 -0700
Message-ID: <1f1a539b.0205280730.5c8e188d@posting.google.com>


"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:<acmdu7$cdt$1_at_lust.ihug.co.nz>...
> "William Rice" <ricew_at_operamail.com> wrote in message
> news:1f1a539b.0205231415.51b83543_at_posting.google.com...
> > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:<3ce36ec0$0$8513$ed9e5944_at_reading.news.pipex.net>...
> > <SNIP>
> > > >
> > > > Seperate tables and indexes for performance reasons.
> > >
> > > see the huge thread on this earlier at http://shrinkalink.com/201
> > >
> > > I think my summary of this is that in general seperating data and
> indexes
> > > should be done for
> > > 1) management reasons only.
> >
> > While I didnt read the whole thread, I guess what I got from it was
> > the old statement, benchmark your system and find out...
> >
> > <SNIP>
> >
> > An example of where separating indexes from a table might be
> > beneficial would be if you happen to do lots of full table scans on a
> > particular table. If you make it the only inhabitant of a tablespace
> > (or tablespaces if it is partitioned), you avoid having to skip past
> > all of the extents you are not interested in.
>
>
> Myth alert. The contiguity of extents has absolutely zero bearing on
> performance. None whatsoever. For the simple reason that the blocks of a
> single extent are not physically contiguous on disk anyway. They are
> scattered all over the physical platter... and therefore, you are forever
> having to skip around the place whether there is one extent of one segment,
> or several thousand extents belonging to several hundred segments. It makes
> no difference.
>
> Regards
> HJR
>

I would be interested in seeing any documentation, results from benchmarks, or anything of that nature that would show this to be the case. Even with the volume mangers that do scatter the data around, they figure out if you are doing a sequential scan, and try to prefetch efficiently in order to compensate for the fact that the data is scattered around. If your data is scattered around, you don't get to benefit from this.

I haven't been able to find the benchmarks I did a couple of years ago otherwise I would post the results.

I would also be interested in where you go the information that the data that seems contiguous from a raw device is actually scattered all over the disk. I will admit to not having any documentation to the contrary, because to me this has always been a given documented otherwise by the disk vendor. I am aware that some bad blocks might end up being written to another part of the disk, but this would be the exception, not the general case.

If this is to far off topic for this group, I would be happy to take the discussion offline.

Will Received on Tue May 28 2002 - 10:30:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US