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

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/
>
>
Received on Thu Oct 03 2002 - 08:30:26 CDT