Re: compare two function based columns in two tables

> Hi,
>
> I am trying to see if table B's varchar2 column_1 is contained in
> table A's varchar2 column_1. The comparison uses a function to get
> the string 'into shape'. So the test is either:
>
> ... where instr(function(a.column_1),function(b.column_1)) > 0
>
> or
>
> ... where function(a.column_1) like '%'||function(b.column_1)||'%'
>
> I have created function based indexes on each table for the function,
> and then analyzed compute statistics the tables, but so far the
> functions are not being used.
>
> When I create a 'shadow' column (using triggers to maintain it) in
> each table of the function(column_1) and use these columns in the
> first method above, response is acceptable.
>
> What I can't tell is if I should be expecting the same response using
> function based indexes if I just get the syntax and all correct.

Dave,

it looks like your indexes cannot be used for this purpose.

Indexes are used in order to reduce the time it takes to find a row in a table that matches a certain criteria. In your case, however, you scan the table a, bypassing the index.

hth
Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html