Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

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

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Wed, 02 Oct 2002 18:31:04 GMT
Message-ID: <3D9B3B50.E3641DC3@exesolutions.com>


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.

>

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

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US