Re: How do add primary key contraint on existing table?

John Graffio <jgraffio@home.com> wrote: :Hello all, : :I have to periodically purge data from an approximately 7 million row :table. Btw, I am running Informix OnLine 7.20 on a DEC Alpha Unix 3.20G :with 128 MB. The table is fragmented over two dbspaces. I am doing the :purge by unloading all data except that which I want to exclude, then :using dbload utility to reload the data after dropping the table. Prior :to dropping the table, I run the dbschema utility with server specific :syntax so I can recreate the table. The table has two not null :constraints on it along with a primary key constraint. I have found that :I can load 40,000 records per minute without the primary key, and 5000 :records/min with the primary key constraint. That works out to 3 hours :vs 29 hours. I tried to use the ALTER TABLE command (without specifying :the dbspace) to add the primary key constraint after loading the data, :but I get an ISAM error message that says it has run out of disk space. : :My question is this: does the ALTER TABLE command try to create a new :table in the same dbspace (and then copy records from old to new and :then drop old table)? Not not if it's an: ALTER TABLE tablename ADD CONSTRAINT PRIMARY KEY (colname) CONSTRAINT tablename_primary; or something like this. If there is no index on this column(s) it will however create a unique index in the same dbspace as the table. This is probably the reason why you run out of space. What you should do is: CREATE UNIQUE INDEX tablename_primidx on tablename (colname);with any added functionality you may want. Only after this you should run the above alter statement to add the primary key. A new index will then not be generated and you should be ok. However I do also think it's in 7.2 or possibly even earlier where you can temporarily turn off creation of index data while loading your table. That should also work. : If so, it would definitely run out of space; what :syntax would I use to specify that I want to add the contraint in-place? :If not, where is the system trying to add the constraint, and how can I :get it on there without running out of space? : :Any help would be greatly appreciated... : :John Graffio Systems Manager, CPTC :(714) 637-9191 x202 Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org