Serge Rielau wrote:
Bob Stearns wrote:
left outer
join $schema.animals an_calf
on days(current_date) -
days(an_calf.birth_date) < 280
and an_calf.ACTIVEX = 'Y'
and ((an_calf.dam_bhid = an1.bhid AND
an_calf.recip_bhid = 0)
OR an_calf.RECIP_BHID = an1.bhid)
There are two potential problems. The first is the OR which pretty much
makes teh last AND predicate useless for index exploitation.
The second is the expression on days which disallows index exploitation
on birth_date and, worse, forces an NLJOIN.
What about a small rewrite:
an_calf.birthdate > current date - 280 days
Now DB2 has a clear startkey for an index scan over birth date.
Cheers
Serge
Thank you. Creating the index and changing the query as you indicated
changed the time from over a minute to less than 2 seconds.
How could I convince the optimizer to do what I would do in "procedural
code", which uses the dam_bhid and recip_bhid indices to minimize the
time required (in no actual language that I know of):
found = -1
while (SELECT * FROM animals WHERE dam_bhid=an1.bhid) {
if(recip_bhid=0} continue
if(activex<>'Y') continue
if(birth_date < current_date-280 days) continue
found = bhid
break
}
if(found=-1) {
while (SELECT * FROM animals WHERE recip_bhid=an1.bhid) {
if(activex<>'Y') continue
if(birth_date < current_date-280 days) continue
found = bhid
break
}
}
if(found=-1) {nomatch)
else (matched the current record, with bhid=found}