Re: Ques. on adding constraints as opposed to indexes

Mickey Mestel <mickm@netcom.com> wrote: > so far so good for my first index, it ran pretty fast. but my other > three indexes are in the form of constraints, and these run for hours and > hours. when i looked at the index building, i saw the usual for a 7.X engine > which is lots of scan threads. but later when i looked at the constraint > building, i saw only an sqlexec thread for the add constraint. > what is going on here? it appears that the engine is not running > multiple threads for adding the constraint, and hence it is taking much >longer. > it seems to me that even though checking has to be done in the case of > unique constraints, this is done at the last level, after the xchng threads > have passed all their data up. > any clue as to why this is only using one thread to do the work here? There is an effective work-around to this situation. It also allows you to fragment constraints if you want to. This is explained in greater detail in the book "OnLine-Dynamic Server Handbook" from Informix Press, but what you do is create an index in the form you would want your constraint to be in. Since it is an index, you can fragment it if you like. Then run your "alter table" command to add the constraint. The functionality of the index will change to that of a constraint. You do have to be careful about the order in which you drop the index and con- straint combination, explained as well in the book. Do the constraint first. Carlton ______________________________________________________________________ Carlton Doe "It's not *over* until I win!" DBA Resources, Inc. -- Les Brown Salt Lake City, UT carlton@iiug.org http://www.iiug.org dbaresrc@xmission.com http://www.xmission.com/~dbaresrc carlton@errin.dbaresrc.com PGP key: finger -l dbaresrc@xmission.com