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

But how would this all effect my desire to retrieve an ordered subset of records?

Surely the only things that I would need to worry about is that the order is consistent ie. record 1 is before record 2 is before record 3 etc; I have to be aware that an addition of a record or deletion of a record may result in records moving between subsets (what I called "pages", but that may be confusing people, as I am not refering to physical disk pages). That doesn't really concern me, because it is unusual to be looking for a record that hasn't been entered - it's all a matter of process, I suppose.

Whatever happens behind the scenes is not really my concern, surely? If the system decides to reorganise the records internally, then provided my (sorted) records are returned the same way as they would have been returned before the reorg, then there should be no difference, other than perhaps performance.

Serge Rielau <srielau_at_ca.ibm.com> wrote in message news:<3D9A4615.4050300_at_ca.ibm.com>...
> ... further...
> When an update causes a row to grow (e.g. my storing a longer variable
> length value). Then the DBMS will try to shuffle the other rows in the
> page to squeeze teh row in. When that fails the row has to move to
> another page and it leaves an indirection pointer (like the forwarding
> address you leave when you move :-). This indirection hurts performance
> (one more IO).
> Reorg will remove those inderections and eliminate free space between
> rows that occurs if a row gets inserted in the place where a bigger row
> got deleted, or where a rows' variable fields shrank.
> Reorg may also try to enhance any chosen clustering of the data.
>
> Cheers
> Serge
Received on Wed Oct 02 2002 - 00:48:23 CDT