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: Thu, 03 Oct 2002 15:48:33 GMT
Message-ID: <3D9C66B9.601501B4@exesolutions.com>


Richard Foote wrote:

> Hi Serge,
>
> This thread began to lose it's relevance from an Oracle point of view when
> the discussion of reorg started (at the point when my name sake started
> questioning the reasons for a re-org).
>
> As Daniel, Sybrand, Norman and Howard have all since stated, if tables have
> been set-up correctly (with appropriate values for PCTUSED/PCTFREE), a reorg
> is generally not required as tables do not become fragmented. Emptyish
> blocks get placed back on the freelists and can be subsequently reused very
> efficiently.
>
> Fragmentation occurs when PCTUSED/PCTFREE have not been set appropriately or
> if a table permanently reduces in size.
>
> The issue of a table no longer meeting a clustering factor is not relevant
> per se in Oracle as Oracle doesn't have the concept of a 'clustering index'
> on a table. The closest Oracle gets to this is it's implementation of
> Clusters (which is somewhat different) and Index Organised Tables (which is
> even more somewhat different). Rows in Heap tables are 'effectively'
> randomly distributed, especially so if deletes occur. This is good in that
> it's one less reason for a reorg but bad in that maintaining a desirable
> clustering factor is difficult.
>
> Having such a discussion across so many groups is difficult as each DB does
> things and implements things differently.
>
> BTW (sorry Norman), this is by no means the "Ultimate Question". My vote for
> the real Ultimate Question is "Will you marry me" ;)
>
> Cheers
>
> Richard
> "Serge Rielau" <srielau_at_ca.ibm.com> wrote in message
> news:3D9C3BFE.4070008_at_ca.ibm.com...
> > Naturally my knowlegde of Oracle is mostly book-smarts....
> >
> http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/em.920/a86
> 647/reorg.htm#1006543
> > I'm not sure why Oracle would spend money on features that aren't useful
> > in one way or another.
> >
> > Howard, please note that this thread has in no means been an attack on
> > any product, nor on the relative need for reorg between produts. However
> > minuscule the need for reorg of some sorts is in any given product
> > (inluding Oracle) it exists and that makes reorg one of the many reasons
> > to stick to separation of an abstract access to the data withot exposing
> > any physical properties.
> > .. which coincidently was is the topic of this thread.
> >
> > Now if you insist we can cut out all the other cross-posted groups and
> > turn to a reorg war in the Oracle newsgroup.
> >
> > Cheers
> > Serge
> >
> > --
> > Serge Rielau
> > DB2 UDB SQL Compiler Development
> > IBM Software Lab, Toronto
> >
> > Try the DB2 UDB V8.1 beta at
> > http://www-3.ibm.com/software/data/db2/
> >
> >

I can see numerous reasons in Oracle to reorg. Most of them irrelevant if a competent DBA is involved in database creation. Among the ones you mention ... appropriate settings for extent size, pctfree, pctused, pctincrease, etc. If one uses locally managed tablespaces with uniform extents ... the pctincrease and extent size issues go away.

But the one remaining issue that does not go away is balancing I/O. There is no way anyone can know how I/O will happen in the real world before an application goes into production. We can all make educated guesses but end-users have a way of surprising us on an ongoing basis.

So I can see a reason to move things around from time-to-time to balance I/O. But other than that ... you are correct. It is not an issue in Oracle.

Daniel Morgan Received on Thu Oct 03 2002 - 10:48:33 CDT

Original text of this message

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