Re: Oracle Myths

In article <f369a0eb.0206052119.4865711_at_posting.google.com>, you said (and I quote):
>
> 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.

Any file system will cause this. Any. Not just a volume manager. It's how file systems work, both in Unix and NT. There are some very interesting papers on file systems and how they work in the Unix arena. And the NT doco from M$ has some accurate descriptions of what NTFS does to a disk...

> 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

Any wonder why people like Steve Adams recommend raw for the highest performance? It gives you absolute control over placement on disk, assuming of course you're not going through a LVM and are actually allocating raw partitions in the disk geometry files in Unix. If not, then even with raw you can still fail to achieve this performance separation. Many LVM's take assumptions without telling you what they are doing.

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

Nope. What you think is a sequential I/O is only so in logical terms. Never is in physical terms when using a file system. Never.

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

That's the whole point. There is NO difference that can be absolutely and objectively measured when one of the givens of the test is that BOTH options will be using random I/O even though one "thinks" it is sequential! What happens most of the times these things have been "measured" is precisely the effect of caches, be they in the disk (which you can NOT disable), controller (which in most cases you can NOT disable), OS (which in some cases you can bypass) and the DB (which you may have control over IF you know where to go and how).

Still, this is only relevant when we're talking extracting absolute max speed out of a system. In 99.9999999999% of real life cases, it can be debated to death but no one will be able to confirm conclusively that separating an index from a table by tablespace will achieve any speed gain. Because the two results will be so close that it won't make any sense to go to all the effort.

However, of course: if one feels that it makes sense in terms of management and maintenance, then go for your life. It will not be better in speed, but most probably it won't be much worse either, so: 6 of one, half a dozen of the other.
But purely for performance it doesn't make the slightest.

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam