Re: unloading a table more efficiently...

Hi Phillip, It took an additional 2 hours to create the indexes for the table with 1 million+ rows and about 500Mb of data. Of course, I ran this on a SPARC5 with 64MB RAM and one 170MHz Sparc processor. You said: > load table from file > change lock mode if needed and create primary key(s) > ** I create the primary key here because you don't want to load with a > primary key enabled. It's painfully slow because it updates the index > while you're loading.. Are you saying that I can omit the last statement, "primary key...", from the schema file before loading this table? What does it update if I remove the index creation statements? What is the syntax to create the primary key afterwards? schema listed below: create table "opsi".mch ( coentfincble decimal(5,0) not null , femovcble decimal(8,0) not null , cotiuniorgext char(1) not null , couniorgext decimal(5,0) not null , cocomcont char(3) not null , nrcomcble decimal(5,0) not null , nrsecmco decimal(5,0) not null , nrctacble char(15) not null , txdesmov char(50) not null , cotiuniorgext1 char(1) not null , couniorgext1 decimal(5,0) not null , . . coindlibre1 decimal(9,0) not null , coindlibre2 decimal(9,0) not null , coindlibre3 decimal(9,0) not null , coindlibre4 decimal(9,0) not null , coindlibre5 decimal(9,0) not null , tmstamp decimal(16,0) not null , primary key (coentfincble,femovcble,cotiuniorgext,couniorgext,cocomcont,nrco mcble,nrsecmco) end of schema Regards, I appreciate your comments! Denmark W. ----- Original Message ----- From: Phillip <tienp@wholefoods.com> To: <dweatherb@btl.net> Sent: Thursday, February 08, 2001 11:50 AM Subject: unloading a table more efficiently... > Your onload/onunload process might have bombed out because you need to > make sure of two things when using onload/onunload to move databases > between two machine: 1. same OS. That means if you're running Solaris > 2.6 on the original machine, you have to be running Solaris 2.6 on the > other. 2. same version of Informix. Same concept as number 1. > > It's hard to believe that a table of only 1+ million rows takes 7.5 > hours to load. There might be something else going on here. Anyway, > here's what I do for table recreations for relatively small tables such > as this one. > > do a backup > unload the table to a file > run an oncheck -pt to get the table stats, particularly # pages > allocated > drop table > create new table with the appropriate stats based on # pages allocated > and dbschema of old table with new extent size parameters > turn off logging > bump up LRU MAX/MIN to 80/70 for maximum load performance and set > CKPTINTVL to something like 3000> load table from file > change lock mode if needed and create primary key(s) > ** I create the primary key here because you don't want to load with a > primary key enabled. It's painfully slow because it updates the index > while you're loading.. > > create indexes > update statistics> turn logging back on > change LRU and CKPTINTVL back to normal > > > -- > Phillip Tien > Database Administrator > Whole Foods Market, Inc. > > >

Denmark B. Weatherburn wrote in message <961527$ebv$1@news.xmission.com>... > >You said: >> load table from file >> change lock mode if needed and create primary key(s) >> ** I create the primary key here because you don't want to load with a >> primary key enabled. It's painfully slow because it updates the index >> while you're loading.. > >Are you saying that I can omit the last statement, "primary key...", >from the schema file before loading this table? What does it update if I >remove the index creation statements? >What is the syntax to create the primary key afterwards? > You could try primary key ..... disabled Or, look at the set constraints ... <mode> set indexes ... <mode> set triggers ... <mode> commands where you can set a database object to disabled or enabled and a few other modes depending on the server etc. If an object is switched from disabled to enabled, it may be rebuilt by the engine at that time. PS - if you are building your primary key implicitly, I mean, letting the engine pick the name and location of the index used to implement the primary key, then you cannot control it's location or extent sizes. Incidentally, If you build a table like this: create table blah ( key integer not null ); create unique index idx_blah on blah (key) <directives> ; alter table blah add constraint primary key (key); The primary key piggy-backs on the named index instead of building an index of it's own. You then have the ability to direct voting preferences in the creation of the index that does the work. Perhaps the syntax will be rounded out in the near future, to add more control within the table creation statement itself. HTH