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

Comments embedded.

"Phil Singer" <psinger1_at_chartermi.invalid> wrote in message news:3DA24A4E.F5B362A_at_chartermi.invalid...
> 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.

From my distant memories of DB2 this reorg was always an issue. When inserting data DB2 would attempt to keep the clustered key order but as free space ran out, the order was maintained by pointers and the clustering factor become worse and worse. Eventually, reading via the clustering key becomes too inefficient and a reorg is required.

The key benefits here are of course being able to read the data in a predefined order, hence eliminating a sort. Also range scans via the clustered index are efficient as fewer pages need to be visited. The bummer though were those damn reorgs (although it was always good for overtime).

I going back many years here so things could well be quite different.

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

But if much data is inserted/updated, these reorgs could be pretty regular (a database on high fibre diet so to speak ;)

Oracle doesn't have this as an issue per se and hence Oracle's needs for reorgs are somewhat less (as mentioned earlier in this thread).

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

I would suggest it's not that common a practice to regularly export/import an Oracle database. And for some sites that do, they do so for somewhat unnecessary reasons (such as keeping extents down to one, in the belief it's an effective backup, etc.).

I would also suggest that rebuilding indexes because they become unbalanced is also not a common practice on account that Oracle's indexes are always balanced. Indexes with oddish characteristics (that may have wasted space that's unlikely to be reused) may benefit from an occasional rebuild but the requirement to do so is somewhat rare (although admittedly it doesn't prevent some from doing so more regularly).

Even row migration could be "cured" by a simple reinsertion of the effected rows rather than a full table reorg as such.

Cheers

Richard
>
>
>
>
> --
> Phil Singer | psinger1ATchartermiDOTnet
> Oracle DBA
> Replace the obvious to reply
Received on Tue Oct 08 2002 - 07:57:46 CDT