Hi Rod!

rodbracher wrote:

> 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.

It is a non-sense query anyway, you simply don't use a table on the
right side of a LEFT JOIN in the WHERE clause (except for IS NULL). This
query should be written

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

which will use an index.

> 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' )

Hmm, I'd prefer

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

Still, that does require you to go through every single record in
tableA. Since you cannot tell in advance whether a record of tableA
should be updated, there's no other possibility.

That is, you can of course write a stored procedure that starts off with
a FOR SELECT on tableB and then update tableA. As far as I know, this is
the sensible thing to do if tableA is big, tableB is small and the
update doesn't have an indexable WHERE criterium on tableA.

HTH,
Set