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

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.

-- 
(concatenate 'string "aa454" "@freenet.carleton.ca")
http://cbbrowne.com/info/spiritual.html
"...It is also  possible to post imbecilic  articles with any browser,
especially when you  toppost    and omit snippage."    --   CBFalconer
<cbfalconer_at_yahoo.com> - seen on comp.lang.c