Usenet.com

www.Usenet.com

Group Index

Comp Thread Archive from Usenet.com

<-- __Chronological__ --> <-- __Thread__ -->

FOREIGN KEY CHECK ON INSERT



Hi,

I am trying to optimize a table for inserts.  Half of the timeron cost is in
the FK lookup!

These tables for example
CREATE TABLE FOO2(
FOO2_ID INTEGER NOT NULL CONSTRAINT  FOO2_PK PRIMARY KEY,
DATA VARCHAR(200) NOT NULL,
LASTTIME TIMESTAMP NOT NULL);

CREATE TABLE FOO(
FOO_ID INTEGER NOT NULL CONSTRAINT  FOO_PK PRIMARY KEY,
FOO2_ID INTEGER CONSTRAINT FOO_FK1 REFERENCES FOO2(FOO2_ID) ON DELETE
RESTRICT ON UPDATE RESTRICT,
STATUS CHAR(1) NOT NULL,
DATA VARCHAR(200) NOT NULL,
LASTTIME TIMESTAMP NOT NULL);

when I create an access plan for the following insert into FOO it shows a
lookup for the FOO2_ID value even though it is NULL!
INSERT INTO FOO VALUES(
1,
NULL,
'A',
'This is Data',
CURRENT TIMESTAMP);

I thought maybee the explain plan showed the typical results for any data
values so I tested inserts with the FK applied and with the FK removed.

I inserted 70% faster with the FK removed.

Can someone explain why it is doing the key scan for a value that cannot be
thier FOO2.FOO2_ID is NOT NULL?

Even if FOO2.FOO2_ID was nullable would it be appropiate to 'link' them?

Can I keep the standard RI checks but not do the FK check when NULL?

I believe I could accomplish this with custom triggers but I would think the
DBM would optimize this shortcut.

thank you for your time,










<-- __Chronological__ --> <-- __Thread__ -->


Usenet.com



Please check out one of the premium Usenet Newsgroup Service Providers below for access to Usenet.