Re: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

Jim Kennedy wrote:
>
> I think we might be assuming different definitions for cluster. What do you
> mean by cluster? (That isn't a flame item, just a fact. I suspect that DB2
> might have a different meaning for cluster than Oracle - not better or
> worse, just different.)
> Jim
> "Serge Rielau" <srielau_at_ca.ibm.com> wrote in message
> news:3D9C98A7.70206_at_ca.ibm.com...
> > I prefer not to be the one answering that question since I can see the
> > scale tip towards flame the moment I do so.. Maybe someone less likely
> > to sound biased can pick up the reigns here...
>

Having read thru all of this (and having once worked with DB2 in an earlier life), I thought I might have something to contribute.

DB2 is designed around IBM hardware. On an IBM mainframe, there is special hardware support to do a sequential read 'real fast'. Because the database is so tuned to the hardware, one has to jump thru hoops to avoid making sequential reads be anything other than fast. Consequently, DB2 goes out of its way to make sequential reads (in Oracle read: Full Table Scan) do as much as possible.

Consequently, it has the construction of a 'cluster index'. Forget everything you know from Oracle, it is unrelated. If an index is the cluster index for a table (which can have only one) it means that the data is physically ordered on the disk in the order implied by the index. This means, that if you do a full table scan, the rows come out in the same order as they would if you retrieved each row by the cluster index.

This means that queries which are ordered by this index can be done quite easily (the index may not even have to be read). Index range scans also go fast.

Now couple this with the fact that DB2 programs typically use what Oracle now calls optimizer plan stability (you cannot change the execution plan of a compiled program) and you see that it is essential to keep the table physically ordered. And, if you have a lot of inserts and updates, the only way to do this is to unload and reload the table. When I was a DB2 DBA most of the nightly production cycle was based on doing exactly that.

I believe that SQL Server has something similar, although I know I am likely to be in error.

This is really the only need to reorganize a DB2 table. It was designed to be as re-org free as possible, in contrast to IMS and other early DBMS which sometimes had to be re-org'd twice a day. Of course, some of these IMS guys were booted up to manager, where they were put in charge of Oracle systems....

In defense of those who claim that Oracle databases need to be re-org'd: they may well have found that in practice it works. When it does, it probably is because the export/import process also cleaned up row chaining and rebuilt unbalanced indexes, items which could have been prevented in the first place with a little thought in the table definition (or at least could have been fixed with a lot less work).

-- 
Phil Singer                |    psinger1ATchartermiDOTnet
Oracle DBA
Replace the obvious to reply