Constraint indices

I have a table: CREATE TABLE ime_question_rel_dc ( appt_id INTEGER NOT NULL, question_id INTEGER NOT NULL, answer CHAR(1), timestamp DATETIME YEAR TO SECOND, create_timestamp DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND ); I want to add some constraints to it: alter table ime_question_rel_dc add constraint foreign key (appt_id) references ime_appt (appt_id) on delete cascade constraintfk_ime_question_rel_dc1; alter table ime_question_rel_dc add constraint primary key (appt_id,question_id) constraint ct_ime_question_rel_dc1; alter table ime_question_rel_dc add constraint foreign key (question_id) references cd_ime_questions (id) constraint fk_ime_question_rel_dc2; This works fine, but I wind up with three indices on the table: CREATE INDEX R2202_2982 ON ime_question_rel_dc ( appt_id ASC ) USING btree; -- Check index location. Constraint indexes are created in ROOTDBS! CREATE UNIQUE INDEX P2173_2909 ON ime_question_rel_dc ( appt_id ASC, question_id ASC ) USING btree; CREATE INDEX R2202_2984 ON ime_question_rel_dc ( question_id ASC ) USING btree; It would seem like I only need two of them. Is there a way to get the foreign key on the appt_id field to use the primary key index (appt_id, question_id)? I've tried creating the indices manually before creating the constraints, but it still creates another index for the foreign key. Thanks. DC DOUG CONREY OCI(r) Database Administrator doug_conrey@oci.com 307.772.4193 www.oci.com

Doug Conrey wrote: > I have a table: > > CREATE TABLE ime_question_rel_dc ( > appt_id INTEGER NOT NULL, > question_id INTEGER NOT NULL, > answer CHAR(1), > timestamp DATETIME YEAR TO SECOND, > create_timestamp DATETIME YEAR TO SECOND > DEFAULT CURRENT YEAR TO SECOND > );> > I want to add some constraints to it: > > alter table ime_question_rel_dc add constraint foreign key (appt_id) > references ime_appt (appt_id) on delete cascade constraint> fk_ime_question_rel_dc1; > alter table ime_question_rel_dc add constraint primary key (appt_id,> question_id) constraint ct_ime_question_rel_dc1; > alter table ime_question_rel_dc add constraint foreign key (question_id) > references cd_ime_questions (id) constraint fk_ime_question_rel_dc2;> > This works fine, but I wind up with three indices on the table: > > CREATE INDEX R2202_2982 ON ime_question_rel_dc ( > appt_id ASC > ) USING btree;> -- Check index location. Constraint indexes are created in ROOTDBS! > > CREATE UNIQUE INDEX P2173_2909 ON ime_question_rel_dc ( > appt_id ASC, > question_id ASC > ) USING btree;> > CREATE INDEX R2202_2984 ON ime_question_rel_dc ( > question_id ASC > ) USING btree;> > It would seem like I only need two of them. Is there a way to get the > foreign key on the appt_id field to use the primary key index (appt_id, > question_id)? I've tried creating the indices manually before creating > the constraints, but it still creates another index for the foreign key. > Thanks. Doug, first thank you for using utils2_ak and myschema ;-). Next, IDS will only use an exact index for a constraint index. That makes sense. Yes, IDS can look up any appt_id in the compound index created for the primary key, but that's not unique on appt_id in theory (or I suspect in practice) so it's neither ideal nor efficient for the kind of lookups that IDS will need to perform to maintain the FOREIGN KEY constraint. IFF the primary key and foreign key were an identical column list then, yes, IDS would share the one index for both constraints whether auto-generated or user created. In this case, since the column lists are different, an additional index is needed by the foreign key constraint. Art S. Kagel

Doug Conrey wrote: > I have a table: > > CREATE TABLE ime_question_rel_dc ( > appt_id INTEGER NOT NULL, > question_id INTEGER NOT NULL, > answer CHAR(1), > timestamp DATETIME YEAR TO SECOND, > create_timestamp DATETIME YEAR TO SECOND > DEFAULT CURRENT YEAR TO SECOND > );> > I want to add some constraints to it: > > alter table ime_question_rel_dc add constraint foreign key (appt_id) > references ime_appt (appt_id) on delete cascade constraint> fk_ime_question_rel_dc1; > alter table ime_question_rel_dc add constraint primary key (appt_id,> question_id) constraint ct_ime_question_rel_dc1; > alter table ime_question_rel_dc add constraint foreign key (question_id) > references cd_ime_questions (id) constraint fk_ime_question_rel_dc2;> > This works fine, but I wind up with three indices on the table: > > CREATE INDEX R2202_2982 ON ime_question_rel_dc ( > appt_id ASC > ) USING btree;> -- Check index location. Constraint indexes are created in ROOTDBS! > > CREATE UNIQUE INDEX P2173_2909 ON ime_question_rel_dc ( > appt_id ASC, > question_id ASC > ) USING btree;> > CREATE INDEX R2202_2984 ON ime_question_rel_dc ( > question_id ASC > ) USING btree;> > It would seem like I only need two of them. Is there a way to get the > foreign key on the appt_id field to use the primary key index (appt_id, > question_id)? I've tried creating the indices manually before creating > the constraints, but it still creates another index for the foreign key. > Thanks. Doug, first thank you for using utils2_ak and myschema ;-). Next, IDS will only use an exact index for a constraint index. That makes sense. Yes, IDS can look up any appt_id in the compound index created for the primary key, but that's not unique on appt_id in theory (or I suspect in practice) so it's neither ideal nor efficient for the kind of lookups that IDS will need to perform to maintain the FOREIGN KEY constraint. IFF the primary key and foreign key were an identical column list then, yes, IDS would share the one index for both constraints whether auto-generated or user created. In this case, since the column lists are different, an additional index is needed by the foreign key constraint. Art S. Kagel

in addition to Art's explanation: > -- Check index location. Constraint indexes are created in ROOTDBS! where is your database created?? select dbinfo("dbspace",partnum) from systables where tabname ='systables' --> i guess rootdbs??? i would create the indexes first (specify where you want them, may be fragment them??? ) and then add the constraints. the constraints will use the created indexes. Superboer.