Hi

If I query

select count(*) from tableA left outer join tableB on
tableA.field1 = tableBfield1
where tableB.field2 = 'ZZZZZ'

the index on tableB field2 should really be used - it will not since i
imagine the right side of this query can allow for null.

if I do the same query

select count(*) from tableA, tableB
where tableA.field1 = tableBfield1
and tableB.field2 = 'ZZZZZ'

this chooses the index tableB field2

in a production case the first query had 5 000 000 reads
and the second had 1300 reads.

Problem is on an update I can't really do the second query. Hence the
slow results.

update tableA set field3 = 'YYYYY'
where tableA.field1 in ( select field1 from tableB
where table1.field1 = tableB.field1
and tableB.field2 = 'ZZZZZ' )

this will still not choose index tableB field2 - hence 5 000 000 reads

In the above example, since tableA always has 1 or more linking
records to tableB - i won't have nulls.

Any ideas?

Thanks

Rod