Creating Indexes
In the Informix documentation, it states that the optimizer with use the
primary key index that is referenced by a foreign key. There have been
times when I've created indexes on columns that are used as filters in
queries, and then add the foreign key constraints later.
Given the following:
create table person (
id int,
name char(20),
primary key (id) );
create table dept (
deptid int,
dept_name char(20),
id int references person(id),
primary key (deptid));
-- sample query
SELECT name
FROM dept, person
WHERE dept.id = 1
AND dept.id = person.id;
Any thoughts on the order of creating indexes? In the above example, would
this approach be more efficient:
create table dept (
deptid int,
dept_name char(20),
id int,
primary key deptid);
create index dept_idx_01 on dept (id);
alter table dept add constraint foreign key (id) references person;
TIA...
Steve Romankiw
Not more efficient by easier to manage. Then you can drop and recreate the
index as needed or even CLUSTER it for efficiency of sequential accesses.
I would even go so far as to do the same with the PRIMARY KEY indexes by
creating the UNIQUE index first then altering the table to add the PRIMARY
KEY. Indeed I like htis method so much that my dbschema replacement utility
myschema automatically makes the conversion for you for use in moving a
development schema to production.
Art S. Kagel
Steve Romankiw1 wrote:
>
> In the Informix documentation, it states that the optimizer with use the
> primary key index that is referenced by a foreign key. There have been
> times when I've created indexes on columns that are used as filters in
> queries, and then add the foreign key constraints later.
>
> Given the following:
>
> create table person (
> id int,
> name char(20),
> primary key (id) );
> create table dept (
> deptid int,
> dept_name char(20),
> id int references person(id),
> primary key (deptid));>
> -- sample query
> SELECT name
> FROM dept, person
> WHERE dept.id = 1
> AND dept.id = person.id;>
> Any thoughts on the order of creating indexes? In the above example, would
> this approach be more efficient:
>
> create table dept (
> deptid int,
> dept_name char(20),
> id int,
> primary key deptid);
> create index dept_idx_01 on dept (id);
> alter table dept add constraint foreign key (id) references person;>
> TIA...
>
> Steve Romankiw