Re: Index question

iNav's index advisor should show you this. However let's keep some things
in mind. Are you omitting any facts? The first one that comes to mind
is: are there any 'active records' coming into play here? For example on
your where clause do you have something like
where actrec='A'? If so, they need to be the FIRST column in your index.

Tested

CREATE TABLE rob.jhhlParent
(ParentKey INTEGER NOT NULL,
ActCode char(1) not null check (ActCode in('A', 'Z')),
ClosedDate date,
primary key (ParentKey));

CREATE TABLE rob.jhhlChild
(ParentKey INTEGER NOT NULL ,
ActCode char(1) not null check (ActCode in('A', 'Z')),
vendor integer,
foreign key (ParentKey) references rob.jhhlParent (ParentKey));

insert into rob.jhhlparent values(1, 'A', '2012-01-01');
insert into rob.jhhlparent values(2, 'Z', '2012-01-03');
insert into rob.jhhlparent values(3, 'A', '2012-12-01');
insert into rob.jhhlchild values(1,'A',1);
insert into rob.jhhlchild values(2,'A',2);
insert into rob.jhhlchild values(3,'A',42);
insert into rob.jhhlchild values(3,'A',10);

select parentKey,ClosedDate,Vendor
from rob.jhhlparent left outer join rob.jhhlchild using (parentkey)
where jhhlparent.actcode='A' and jhhlchild.actcode='A'
and closeddate between '2012-11-01' and '2013-02-01'
and vendor=42;

Resulted in the following index recommendations:
JHHLCHILD: ACTCODE, VENDOR, PARENTKEY
JHHLPARENT: ACTCODE, CLOSEDDATE
and
JHHLPARENT: ACTCODE, PARENTKEY, CLOSEDDATE

I could click on these recommendations and it would build these indexes.




Rob Berendt