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

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

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.

Daniel Morgan Received on Wed Oct 02 2002 - 10:27:32 CDT