
www.Usenet.com
| <-- __Chronological__ --> | <-- __Thread__ --> |
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__ --> |