How do add primary key contraint on existing table?
John Graffio
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)? 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
John Graffio <jgra...@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.My...@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
Jorge Torralba
> :but I get an ISAM error message that says it has run out of disk space.
> :
Keep in mind, if you alter a table Informix makes a copy of that table
before making changes to it. If your table is very big, you just might
not have enough room for the copy.
A quicker way of doing your trim would be to:
1- generate a schema for the table in question.
2- change the name of the table to something else
3- save the constraints from the schema in a separate file
4- create the new table
5- disable the constraints
6- insert into newtable select * from oldtable
( this is much faster than load from bla bla bla, since it
is using page reads and writes)
7- Once you VERIFY that the new table has the records you want
to save, drop the old table.
8- rename newtable to oldtable name
9- enable constraints for the newtable
10- run the file with the constraints.
11- update stats
If you put this into a script you will save some time on how you do
these
steps. But, you will notice a cut in the time it takes to do your trim.
--
Jorge Torralba Intel
Information Technology HF2-71
(503)696-4587 5200 NE Elam Young Parkwa
jorge_t...@ccm2.hf.intel.com Hillsboro, Or 97124
=============================================================
Any views or opinions expressed by me do not reflect those of
Intel Corp.
David Williams
In article <3385DA...@ccm2.hf.intel.com>, Jorge Torralba <jorgex_tor
ra...@ccm2.hf.intel.com> writes
>> :but I get an ISAM error message that says it has run out of disk space.
>> :
>
>
>Keep in mind, if you alter a table Informix makes a copy of that table
>before making changes to it. If your table is very big, you just might
>not have enough room for the copy.
>
Look in your $INFORMIXDIR/etc/$ONCONFIG file and change DBSPACETEMP
to be a comma separate list of dbpaces which should be used to
store temporary tables which is what the ALTER TABLE effectively
creates. E.g.
DBSPACETEMP dbspace1,dbspace2,dbspace3
Then shutdown and startup online for the change to take effect.
--
David Williams
Nils Myklebust
David Williams <d...@smooth1.demon.co.uk> wrote:
:In article <3385DA...@ccm2.hf.intel.com>, Jorge Torralba <jorgex_tor
:ra...@ccm2.hf.intel.com> writes
:>> :but I get an ISAM error message that says it has run out of disk space.
:>> :
:>
:>
:>Keep in mind, if you alter a table Informix makes a copy of that table
:>before making changes to it. If your table is very big, you just might
:>not have enough room for the copy.
:>
: Correct - the copy gets created in the root dbspace by default.
Are you realy sure of this David. Remember all he is doing is:
ALTER TABLE tablename ADD CONSTRAINT PRIMARY KEY (colname)
CONSTRAINT tablename_primary;
In my book this doesn't create a copy of the table. It simply adds the
constraint to the existing table. I may be very wrong though as I
haven't ever realy checked to see what is happening. It sure shouldn't
have to create a copy of the table, so I would call it a defect if it
does.
Even if it doesn't create a copy of the table it does create a new
unique index and this may be the reason why he runs out of space.
That's why I suggested that he created the index first (see earlier
post in this thread).
: Look in your $INFORMIXDIR/etc/$ONCONFIG file and change DBSPACETEMP
: to be a comma separate list of dbpaces which should be used to
: store temporary tables which is what the ALTER TABLE effectively
: creates. E.g.
:
: DBSPACETEMP dbspace1,dbspace2,dbspace3
:
:
: Then shutdown and startup online for the change to take effect.
:
:
:--
:David Williams
mirosla...@ssa.co.uk
In article <33887f78...@gate.idg.no>,
Nils.My...@idg.no wrote:
>
> David Williams <d...@smooth1.demon.co.uk> wrote:
>
> :In article <3385DA...@ccm2.hf.intel.com>, Jorge Torralba <jorgex_tor
> :ra...@ccm2.hf.intel.com> writes
> :>> :but I get an ISAM error message that says it has run out of disk space.
> :>> :
> :>
> :>
> :>Keep in mind, if you alter a table Informix makes a copy of that table
> :>before making changes to it. If your table is very big, you just might
> :>not have enough room for the copy.
> :>
> : Correct - the copy gets created in the root dbspace by default.
>
No, copy of table which is under alter procedure is created in the same
dbspace as original table. But this is not this case. Adding primary key
constraint does not copy new table. Nils is right - he says it on
following rows:
> Are you realy sure of this David. Remember all he is doing is:
> ALTER TABLE tablename ADD CONSTRAINT PRIMARY KEY (colname)
> CONSTRAINT tablename_primary;
>
> In my book this doesn't create a copy of the table. It simply adds the
> constraint to the existing table. I may be very wrong though as I
> haven't ever realy checked to see what is happening. It sure shouldn't
> have to create a copy of the table, so I would call it a defect if it
> does.
>
> Even if it doesn't create a copy of the table it does create a new
> unique index and this may be the reason why he runs out of space.
> That's why I suggested that he created the index first (see earlier
> post in this thread).
>
Create index statement always needs space for sorting. It should be
reason for running out of space. Check DBSPACETEMP environment variables
following David`s instructions. Check number of free pages in temporary
dbspaces and dbspaces you are using. But: I would recommend to use High
Performance Loader for unloading and loading large amount of data (I am
not sure, but I think you are on 7.2x). You can load from several devices
(tapes, files, UNIX pipes) in parallel. You can also disable indexes,
constraint checking and!! disable logging of loaded data depending on
mode (express or deluxe) you are using. HPL has many other features. Read
documentation about HPL. This tool seems to be very good. If you don`t
want to use HPL, you can disable indexes, constraints and triggers or set
them to filtering mode with SQL command (beginning with 7.10 UD1).
I hope this helps.
Miroslav Lorenc
SSA Czech Republic
-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet
Peter Lancashire
> 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.
DISABLED before the load and then enable afterwards. This is is version
7.1. I haven't tried it for this specific problem but it's great for
loads of non-unique data with the set violations commands.
Is everything in the world an object now?
--
Peter Lancashire
Information Systems Specialist, Bayer plc
Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK
Tel: +44-1635-562258, Fax: +44-1635-562281