Re: delete based on multipart key
Rudy Fernandes wrote:
>
> Funnily, if the following statement is run, the index does get
> used.
>
> select a || b from first_table
> where a || b in (select a || b from second_table);
>
> but
>
> select * from first_table
> where a || b in (select a || b from second_table);>
> results in a SEQUENTIAL SCAN of first_table
>
> Isn't this an optimizer goof up? Why should a || b be treated
> differently from a, b? Isn't the index (a,b) stored as a || b?
That -is- strange.
Does this still work if a or b are a mix of character, numeric, or
date/datetime
types?
I still think there should be an ANSI (or at least Informix) support of
the following:
select * from first_table
where a,b,..(as many fields as necessary) in (select a,b,.. from
second_table)
because it's simple. If you need to hard-code values in the list
you could allow:
select * from first_table
where a,b in (("1","A"),("2","B"))
Does anyone know how to get this submitted to the proper ANSI
committee?
Douglas Wilson