Re: function-based indexes

On 28 Jun 2001 13:39:39 -0700, michaelmoss123_at_yahoo.com (Michael) wrote:

>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

Hi Mike,

In your first 2 queries, you actually force the use of the FB indexes by hints. You don't do that in the third clause, and, obviously, the optimizer prefers a full table scan.

Try to remove the hints from the first 2 queries. If the execution plan now shows full table scan, it is no surprise that your join does that as well. If the execution plan shows index scan, you can either try to use hints in your join or find out why the optimizer uses full table scan.

I would also advice you to change your join clause to :

 t1.a is not null and
 t2.a is not null and
 upper(t1.a)=upper(t2.a)

As a Function-based index does not include NULL-values.  

Regards,
Kenneth Koenraadt
Systems Consultant
Oracle DBA
plovmand@<no-spam>hotmail.com Received on Sat Jun 30 2001 - 11:46:46 CDT