>If I put in WHERE clause an expression like UPPER(field_name) = UPPER(var_char) and my index is not UPPER(field_name) then I won't get the Rushmore optimization at all. Thus, data retrieval would be a slow process. I have tried SYS(3054,1) to check the level of optimization and there is no optimization if WHERE and INDEX expressions don't match.
Maybe there is some misunderstanding in this thread? If you want to haveep index as GENERAL collate you should first SET COLLATE TO GENERAL and secondly recreate your index from scratch. Now you have GENERAL collated index which is not case sensitive.
Edward Pikman
Independent Consultant