"collincm" <collincm@thathotmail.comservice> wrote in message
news:vssp4m9o09tk91@corp.supernews.com...
> 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,
>
Just out of curiosity, try explaining this statement:

INSERT into foo
(foo_id, status ,data , lasttime )
values( 1, 'A', 'This is Data', current timestamp)