Re: Extents and Fragmentation

"Clifton M. Bean" wrote: > > When you load a table from scratch, the first extent size is basically > ignored since the data will be loaded continguously. If you fill one chunk > and hope to another, each will be considered one extent. With such a large > table, you might want to either load it into one multiple drives (one > dbspace, multiple chunks) (round robin) or fragment it by expression > (multiple dbspaces). That is true, but remember that those 212 extents still need to be allocated, albeit appended to the first. Also, extent doubling will kick in at some point, just to add to the overhead. With some home work up front, the overhead of those extent allocations can be eliminated by sizing the first extent correctly. For large tables, that overhead can provide for a saving. Also, as this table will end up in at least two extents (3 Gb) spanning two chunks, the next extent size becomes even more important. You can go as far as setting both extent sizes to 2 Gb for the load. You can always reduce the next extent size after the load with an ALTER TABLE command. This of course depends on your sizing exercise. You don't want to allocate 2 Gb unnecessarily, but you get the idea. > "Carlson@WHSmith" <carlson1@bellsouth.net> wrote in message > news:642A954DD517D411B20C00508BCF23B00124AE6F@mail.sauder.com... > > duffybj1@my-deja.com wrote: > > > > > > Hi guys, > > > > > > > Hi > > > > > I'm a pretty new DBA, so if I sound ignorant, forgive me. > > > > We were all ignorant at one time . . . . some of us still are . . .. 8-) > > > > > > > > In one of my busiest databases, we have a 40 million row table with 212 > > > extents (about 3 MB)and several 4-10 million row tables with close to > > > 200 extents. From what I read read in the past two weeks, this is a > > > very bad thing. > > > > Ouch . . . you _did_ say 212 extents? That's somewhere between "bad" > > and "I can't allocate any more extents" bad. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |This email will self-destruct in |/// / ////| | |10 sec. If you received this email |// / /////| | |in error, sorry about the mess. |/ ////////| +----------------------+-----------------------------------+-----------+