Add Constraint

Add Constraint

Post by Colin Daws » Thu, 01 Sep 2005 18:23:43



I've just added a constraint on a table both the master and detail tables
have over 100M rows, and it took almost 2 hours, does anyone know what
exactly happens during an ADD CONSTRAINT.




Regards

Colin

There are 10 types of people in the world, those that understand binary and
those that don't
sending to informix-list
 
 
 

Add Constraint

Post by Obnoxio Th » Thu, 01 Sep 2005 19:03:08


Colin Dawson said:

What kind of constraint?

--

Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule"
- Coluche

A smile is a gift that is free to the giver and precious to the recipient.
But giving someone the finger is free too, and I find it more personal and
sincere.
sending to informix-list

 
 
 

Add Constraint

Post by Simmons, K » Thu, 01 Sep 2005 19:28:18


Colin

You do not say which type of constraint you have created, however in
general an index must be created to support the constraint (unique if
needed). Even if an index exists if there is more than one field in
the constraint (for a primary key perhaps) then every field must be
checked to ensure it is not null. Obviously if there is a referential
constraint then each row in the detail must be referred back to the
master to ensure it exists.
Any help??
If not what precisely are you doing.

Keith

-> -----Original Message-----
-> From: Colin Dawson [mailto: XXXX@XXXXX.COM ]
-> Sent: Wednesday, August 31, 2005 10:24 AM
-> To: XXXX@XXXXX.COM
-> Subject: Add Constraint
->
->
-> I've just added a constraint on a table both the master and
-> detail tables
-> have over 100M rows, and it took almost 2 hours, does anyone
-> know what
-> exactly happens during an ADD CONSTRAINT.
->
->
->
->
-> Regards
->
-> Colin
->
-> There are 10 types of people in the world, those that
-> understand binary and
-> those that don't
-> sending to informix-list
->

**********************************************************************************
This message is sent in strict confidence for the addressee only. It may
contain legally privileged information. The contents are not to be disclosed
to anyone other than the addressee. Unauthorised recipients are requested
to preserve this confidentiality and to advise the sender immediately of any
error in transmission.
This footnote also confirms that this email message has been swept for the
presence of computer viruses, however we cannot guarantee that this message
is free from such problems.
**********************************************************************************
sending to informix-list
 
 
 

Add Constraint

Post by Colin Daws » Thu, 01 Sep 2005 20:07:03


Sorry.....

It's a Foreign Key constraint




Regards

Colin

There are 10 types of people in the world, those that understand binary and
those that don't





sending to informix-list
 
 
 

Add Constraint

Post by scottishpo » Thu, 01 Sep 2005 21:31:47

maybe the database is building some indexes and checing there are no
violations?
 
 
 

Add Constraint

Post by Obnoxio Th » Thu, 01 Sep 2005 21:35:07


Colin Dawson said:

It may well have had to build an index, then.



--

Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule"
- Coluche

A smile is a gift that is free to the giver and precious to the recipient.
But giving someone the finger is free too, and I find it more personal and
sincere.
sending to informix-list
 
 
 

Add Constraint

Post by Colin Daws » Thu, 01 Sep 2005 21:50:57


Nope, that isn't the problem. There is an index on the appropriate column in
both tables.

sending to informix-list
 
 
 

Add Constraint

Post by Obnoxio Th » Thu, 01 Sep 2005 22:18:40


Colin Dawson said:

Validating all the keys, then?

--

Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule"
- Coluche

A smile is a gift that is free to the giver and precious to the recipient.
But giving someone the finger is free too, and I find it more personal and
sincere.
sending to informix-list
 
 
 

Add Constraint

Post by Bill Dar » Thu, 01 Sep 2005 22:52:47


The server is going to build that index in your rootdbs so you might
want to create the index explicitly first so you can control where it
resides and then add the constraint.

Bill


sending to informix-list
 
 
 

Add Constraint

Post by Bill Dar » Thu, 01 Sep 2005 23:37:07


The index will not be used by the add constraint unless it is on ONLY
the column(s) in the constraint. So, if you have a composite index with
your constraint column as the lead column in the index, that index will
not be used for the constraint. The server will create a new index.

Bill


sending to informix-list
 
 
 

Add Constraint

Post by rkusene » Fri, 02 Sep 2005 00:34:53

Then it is checking for every detail table whether the key exists in the master table.

Just a discussion out of curiosity: Only informix chose to implement PKY->FKY relationship
via an index. In Oracle, Db2, and SQL Server, FKY does not need an index.

There are both plus and minus of Informix approach. The plus is that the join of master
and child will always use index. In other dbs, if the DBA forgets to create it, it will
suffer in performance.

The minus is that, sometimes for small code tables, where there is no need of index,
Informix will still create an index. On a large table, this is a waste of space.
 
 
 

Add Constraint

Post by Obnoxio Th » Fri, 02 Sep 2005 01:36:28


rkusenet said:

Errrr.... a small table isn't a large table, or am I missing something? :o)

--

Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule"
- Coluche

A smile is a gift that is free to the giver and precious to the recipient.
But giving someone the finger is free too, and I find it more personal and
sincere.
sending to informix-list
 
 
 

Add Constraint

Post by rkusene » Fri, 02 Sep 2005 05:11:25


OK lemme give you a example.

Suppose there is a lookup table (parent table in this case) containing few values.
(say at most 5).
And then there is a transaction table (child table) with millions of rows which
has a FKY column on the above mentioned parent table.

In this case an index on the transaction table will be unnecessary bcos no
query joins it to the lookup (master) table. So an index just to maintain
referential integrity is a complete waste of space, not to mention an
additional bottleneck on inserts.

However this can be easily solved by using check constraint instead of FKY->PKY
relationship table, provided the lookup values are guaranteed to be static and small range.

For eg, Credit Card Type. MC - Mastercard, VI - Visa, AM - Amex, DC - Discover.
 
 
 

Add Constraint

Post by davi » Fri, 02 Sep 2005 10:53:19


delete from the parent table. how does the engine check that no rows in
the
big foreign table use that key? scan the large table?
 
 
 

Add Constraint

Post by Bill Dar » Fri, 02 Sep 2005 21:41:28

Yes, I assume you are correct. I always create my databases in rootdbs and then explicitly place my tables where I want them, so I always see implicitly created indexes in rootdbs.

Bill


sending to informix-list