Re: UNUSED INDEX on CHAR fields

sklusmann_at_my-deja.com writes:

> ... you may suspect me correctly to be not deeply engaged with SQL, but
> how then can I persuade the ORACLE-Server to do it vice versa, that
> means:
>
> full scanning varlist
> for each row we find
> do an index lookup into v_btypen

An old trick is to concatenate '' (or add 0), to the field which belongs to the table which you want to drive the query with, this will prevent using an index on that field.

       SELECT MAX(a.v_adr + a.v_size * b.pc_size) max_size
       FROM   varlist a, v_btypen b
       WHERE  a.v_typ||'' = b.name;

This is even somewhat portable - I've used it successfully (for numerical fields, like in "A.KEY+0 = B.KEY") in both Oracle and MS SQL*Server, don't know about other systems, though.

-Tapio. Received on Sun Jul 09 2000 - 00:00:00 CDT