Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: compare two function based columns in two tables
In order to use function based indexes, you'll need to set up query
rewrite for your session as follows:
ALTER SESSION SET query_rewrite_enabled=TRUE
HTH,
Brian
Dave Rawding wrote:
>
> 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
Received on Tue Apr 22 2003 - 13:08:03 CDT