Re: Oracle Myths

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:<ad0p0k$7pg$1_at_lust.ihug.co.nz>...
> "William Rice" <ricew_at_operamail.com> wrote in message
> news:1f1a539b.0205280730.5c8e188d_at_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.
<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. Received on Thu Jun 06 2002 - 00:19:23 CDT