Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement
Christopher Browne wrote:
> 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.
>
>
>
>
>
Likely because I hadn't noticed that this thread was multiposted across multiple groups relating to entirely different environments.
Either way, block or page, Oracle reuses space. And does so rather efficiently.
Daniel Morgan
http://www.extension.washington.edu/extinfo/certprog/oad/oad_crs.asp
Received on Wed Oct 02 2002 - 13:31:04 CDT