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

After takin a swig o' grog, Daniel Morgan <dmorgan_at_exesolutions.com> belched out...:
> Christopher Browne wrote:
>
>> Richard <stupiddickhead_at_postmaster.co.uk> wrote:
>> > Christopher Browne wrote:
>> >> Richard <stupiddickhead_at_postmaster.co.uk> wrote:
>> >>
>> >>>Serge Rielau wrote:
>> >>>
>> >>>>Online Reorg on the fly is very important to improve
>> >>>>availability. The trend is to go towards reorg demons. I.e. your
>> >>>>tables would be contantly reorg-ed in the background. It is
>> >>>>therefore not acceptable to have any app hold a bookmark of some
>> >>>>sort or other on a physical location.
>> >>>
>> >>>What is all this reorg stuff? What does it do? Why do you have to do
>> >>>it? Do you have to do a lot of it?
>> >> It is about reorganizing the physical structure of the database.
>> >> You have to do it because over time the 'random' additions and
>> >> deletions of data that take place lead to it being not optimally
>> >> structured, which hurts performance and may even lead to the DBMS
>> >> running out of "space" even though there is considerable space not
>> >> being used.
>> >
>> > So it doesn't re-use space from deletes?
>>
>> /What/ doesn't re-use space from deletes?
>>
>> It is quite likely that space relinquished by a deletion will not be
>> generally reusable for all purposes.
>>
>> Consider a DBMS using 8K pages, where each page contains records for a
>> particular table. If you delete a record, that leaves space free on
>> that page. But that space can only be used for records in that same
>> table and will likely only be used if the records are in a certain
>> range within the table.
>>
>> If 500 records get deleted, spread out across 50 pages, there might
>> not be a single page that gets /totally/ emptied and thereby returned
>> to the DBMS for general use.
>>
>> A reorganization would go through those pages, compressing the live
>> records into a smaller number of pages and returning a set of empty
>> pages back for general use.
>>
>> That is, at a rudimentary level, what a reorg looks like.
>
> Are you talking Oracle or some other database?
>
> Pages are not exatly a term with any relevance to Oracle ... perhaps
> you meant to respond with respect to SQL Server. And your conclusion
> that empty blocks in Oracle are not reused is quite remote from what
> actually happens.

I'm not sure that you actually read what I wrote, because you certainly didn't didn't comment on anything that I wrote.

A careful reading would show that I most certainly did /not/ write the absurd statement that "empty blocks in Oracle are not reused."

What I /actually wrote/ was that:
  "... there might not be a single page that gets /totally/ emptied   and thereby returned to the DBMS for general use."

I can't fathom how you drew your absurd statement from what I wrote.

As for the terminology matter, Oracle calls those "blocks;" other DBMSes often call them "pages," as do operating systems, the initial question was not specific to any database, so I see no reason to toady to Oracle's terminology.

-- 
(reverse (concatenate 'string "gro.gultn@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/emacs.html
MICROS~1 has  brought the  microcomputer OS to  the point where  it is
more bloated than even OSes from what was previously larger classes of
machines   altogether.   This  is   perhaps  Bill's   single  greatest
accomplishment.