Hi George,

>I'm well aware of the premise of this thread. The answer is still the same: Fragmented files are not, and cannot be more efficient than those that are not for the reason I stated. If you want to know the exact reasons why, then post back.

The goal is to have the number of head movements minimized, I know. However this has not neccesarely have something to do with fragmented files. If the data you're searching for does not require any head movements it is the fastest way of retrieving the data.

For example if the query is something like:

SELECT ..... ;
   FROM Table1 ;
        INNER JOIN Table2 ON Table1.fld1_fk = Table2.pk ;
        LEFT  JOIN Table3 ON Table1.fld2_fk = Table3.pk ;
        LEFT  JOIN Table4 ON Table1.fld3_fk = Table4.pk ;
   WHERE table1.Date = dSomedate

Lets say that all the tables are unfragmented tables of several 100 MB large. In this case you're sure that the head has to move quite a bit to retrieve all data from the source tables.

On the other hand if the data was stored in such way that all the data of all tables are stacked and more or less grouped on date the head of the disk has to travel a shorter distance because all the neccesary data was more or less physically grouped together.

O.K. I now can hear your ask, In how many cases this would be an advantage ? I think that in many systems where lots of data is stored in different table in one transaction might have a (theoretical) performance advantage here.

OTOH, queries that do perform data retrieval on whole tables rather than on only a few grouped records definitely are going to suffer from this.

I'm not saying that fragmentation is always good, but I neither think that fragmentation is always bad either. In certain situations fragmented files could perform better than unfragmented files. Unfortunately we don't have much control on how tables are fragmented, so talking about advatages and disadvantages is purely an academic issue.

Walter,