RE: IDS 7.30: a table with 150 extents

Rick, Thank you so much for the reply. I will follow the following steps : Unload data Drop table stop logging Create table without indexes and constraintsload data create indexes and constraints start logging take 0 level archive Anyway 0 level archive only takes 2 hours and also I can do it online. As you say create table without indexes and constraints, its ok with non-unique indexes, but if create the indexes (unique/primary) after loading data and if I get constraint violation errors what do I do? Do I need to make use of the Start Violation table... and set constraints disabled ... without error commands? Thanks again Vinod Bhansali >From: "Bernstein, Rick" <rbernste@alarismed.com> >To: 'Vinod Bhansali ' <iiug@hotmail.com> >Subject: RE: IDS 7.30: a table with 150 extents >Date: Thu, 15 Jun 2000 21:17:56 -0700 > >If you do not use HPL to populate the table, it is indeed a good idea to >disable logging before you load a large table. >But, why would perform the fake archive to /dev/null? >Without a real backup you have nothing from which you can recover in case >of hardware error, etc. > >It is also a good idea to build indexes and constraints AFTER you >reload the table. It was not obvious whether this was part of your plan. > >Rick > >P.S. Do not forgot to run UPDATE STATISTICS in the manner recommended > in release notes after you finish creating the table and its indexes. > > >-----Original Message----- >From: Vinod Bhansali >To: informix-list@iiug.org >Sent: 6/15/00 3:11 PM >Subject: Re: IDS 7.30: a table with 150 extents > >Madison, > >If I have a plan to unload, drop and reload a table to reduce the no. of > >extents, is it a good idea to stop logging, do unload, recreate table, >load >data and then start logging back and take an fake archive to /dev/null? > > >Thanks for the reply >Vinod Bhansali > > > >From: Madison Pruet <mpruet@home.com> > >To: Vinod Bhansali <iiug@hotmail.com> > >Subject: Re: IDS 7.30: a table with 150 extents > >Date: Thu, 15 Jun 2000 16:51:15 -0500 > > > >Don't get me wrong. It is still an issue because you can get into a > >situation > >where a new extent can not be allocate, simply because the extent > >information > >will not fit within the partition page. However, the performance impact >is > >not > >nearly as great as it was in 5.x. > > > >The number of extents that can be allocated depend on the number of >indexes > >that > >the table has and the page size. For 2K page systems, I'd become a bit > >concerned if the number of extents reached 150, however, I would not be >too > >concerned with 40. On 5.x systems, anything above 8 extents would be > >grounds > >for a table reorg/recluster. > > > >Vinod Bhansali wrote: > > > > > Madison, > > > > > > >had to be scanned. In 7.x+ the extent table is dynamically sized >so > >that > > > >all of the extent information is kept in memory. > > > > > > So do you mean to say that having more extents in no more a serious > >issue? > > > What is the max no. of extents for a table in 7.x? > > > > > > Thanks > > > Vinod Bhansali > > > > > > >From: Madison Pruet <mpruet@informix.com> > > > >Reply-To: madison.pruet@informix.com > > > >To: informix-list@iiug.org > > > >Subject: Re: IDS 7.30: a table with 150 extents > > > >Date: Fri, 16 Jun 2000 00:43:22 -0500 > > > > > > > >Alex Barilo wrote: > > > > > > > > > Hi folks, > > > > > > > > > > I have this table: 4 million rows (at one point it had 17 mill), > > >700Mb > > > > > and 151 (!) extents! It's not fragmented. I wasn't the one who > >designed > > > > > it that way so please don't nail me for that. > > > > > > > > > > My question is: how come we don't see any performance problems? >Is > >there > > > > > optimal extents number for IDS 7.30 (like 8 for online 5)? Or is >it > >just > > > > > powerful box? > > > > > > > >The main reason for the 8 rule in online 5 was that the extent >table > >was > > > >fixed at 8 entries. For any table larger than 8 extents the >partition > >page > > > >had to be scanned. In 7.x+ the extent table is dynamically sized >so > >that > > > >all of the extent information is kept in memory. > > > > > > > > > > > > > > > > > > > > > > > > > > > Hardware: HP NetServer LH4, 4x PII Xeon 400Mhz, 2Gb RAM, 6 >logical > >HDD's > > > > > (12 physical, RAID 10) for dbspaces > > > > > > > > > > OS: SCO OS 5.0.5 > > > > > > > > > > Any ideas will be greately appreciated. > > > > > > > > > > Thanx! > > > > > > > > > > Alex. > > > > > -- > > > > > Before the accident, I could not even spell UNIX > > > > > > > > > > Sent via Deja.com http://www.deja.com/ > > > > > Before you buy. > > > > > > > >-- > > > >Madison Pruet > > > > > > > >=========================================== > > > >Enterprise Replication Product Developement > > > >Dallas, Texas > > > >Informix Software > > > >=========================================== > > > > > > > > > > > > > > >________________________________________________________________________ > > > Get Your Private, Free E-mail from MSN Hotmail at >http://www.hotmail.com > > > >________________________________________________________________________ >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com