Path: news.easynews.com!easynews!cyclone.swbell.net!cyclone-sf.pbi.net!128.32.206.55!newsfeed.berkeley.edu!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: dyou98@aol.com (D.Y.)
Newsgroups: comp.databases.oracle.server
Subject: Re: Oracle Myths
Date: 5 Jun 2002 22:19:23 -0700
Organization: http://groups.google.com/
Lines: 43
Message-ID: <f369a0eb.0206052119.4865711@posting.google.com>
References: <3ce21b71$0$8510$ed9e5944@reading.news.pipex.net> <ue5fnl8qiei092@corp.supernews.com> <3ce36ec0$0$8513$ed9e5944@reading.news.pipex.net> <1f1a539b.0205231415.51b83543@posting.google.com> <acmdu7$cdt$1@lust.ihug.co.nz> <1f1a539b.0205280730.5c8e188d@posting.google.com> <ad0p0k$7pg$1@lust.ihug.co.nz>
NNTP-Posting-Host: 12.237.95.20
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1023340763 19521 127.0.0.1 (6 Jun 2002 05:19:23 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 6 Jun 2002 05:19:23 GMT
Xref: easynews comp.databases.oracle.server:149696
X-Received-Date: Wed, 05 Jun 2002 22:23:52 MST (news.easynews.com)

"Howard J. Rogers" <dba@hjrdba.com> wrote in message news:<ad0p0k$7pg$1@lust.ihug.co.nz>...
> "William Rice" <ricew@operamail.com> wrote in message
> news:1f1a539b.0205280730.5c8e188d@posting.google.com...
> > "Howard J. Rogers" <dba@hjrdba.com> wrote in message
>  news:<acmdu7$cdt$1@lust.ihug.co.nz>...
> > > "William Rice" <ricew@operamail.com> wrote in message
> > > news:1f1a539b.0205231415.51b83543@posting.google.com...
> > > > "Niall Litchfield" <n-litchfield@audit-commission.gov.uk> wrote in
>  message
>  news:<3ce36ec0$0$8513$ed9e5944@reading.news.pipex.net>...
> > > > <SNIP>
> > > > > >
> > > > > > Seperate tables and indexes for performance reasons.
<snip>
> Just remember that a single Oracle block is not contiguous on disk.
> Comprised of many O/S blocks, it's all over the place. Extrapolate from a
> single block to an extent, and the truth of the matter will be evident.
> 

Almost missed this part of the thread. I think I am beginning to understand
why this seems to a debate which can never be settled.

The argument that separating tables and indexes, or separating tables that
are concurrently accessed etc. makes no performance difference seems to be
based on the assumption that space is allocated on file systems by a 
volumn manager. In that case I wouldn't care to argue one way or the other, 
because you have no control over the physical location of your segment when
(logically contiguous)<>(physically contiguous). You may even consider your
data as being randomly distributed on the disks. It's not hard to prove that 
random + anything is still not much different from random. Whatever test you 
do, your result will likely stay within the standard deviation of a random 
distribution.

The interesting argument, I think, is whether you can improve performance by
controlling the physical location of your segments. I believe you can, and raw
devices may be the only way to do this. You want to make sure large table 
scans are physically sequential on a given disk. The only way to gauranttee
that is to make you segment physically contiguous (you may use many disks, but
on any given disk you still want to do this). Look at the number of sequential
I/Os vs random I/Os per second. It depends on the RPM and I don't have the 
acurate figure. But the difference is significant. If it weren't we would have
to rewrite the basic laws of physics. This is assuming you don't have 100GB of
cache or any kind of smart prefetch to speed things up.