I've got a query like: SELECT ... FROM TBL WHERE COALESCE( FIELD1, FIELD2 ) = 12345
where FIELD2 is the primary key and FIELD1 also has an index (in fact it's a foreign key back to FIELD2). This results in a table scan. However expanding the COALESCE by hand: SELECT ... FROM TBL WHERE ( FIELD1 = 12345 ) OR ( FIELD1 IS NULL AND FIELD2 = 12345) results in use of both indices and no table scan. (In the vast majority of cases FIELD1 is null so I just want a lookup of a single record by primary key; just occasionally FIELD1 is not null and I might want two or three records in the result set.) So, my questions: (1) Have I got this right? Is the second query a correct expansion of COALESCE that produces the same results as the first query? (2) If so, is it indeed the case that the optimiser doesn't understand how to expand COALESCE? -- Tim Ward
