join on function-based indexes

I'm trying to create a case-insensitive match on two tables using function-based indexes, but can't get the optimizer to use the indexes. I've set the 2 query_rewrite parameters and analyzed both tables, and both indexes work fabulously when searching only one table at a time. In other words

select /*+ index(f1) */ count(*) from t1 where upper(a)='TEST' and select /*+ index(f2) */ count(*) from t2 where upper(a)='TEST'

both work great and use the indexes. It's when I try to join the tables on upper(t1.a)=upper(t2.a) that I'm getting full-table scans. Does anyone know what I've missed? Thanks very much,

Mike Received on Wed Jun 27 2001 - 01:36:10 CEST