Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: compare two function based columns in two tables

Re: compare two function based columns in two tables

From: Brian Peasland <oracle_dba_at_remove_spam.peasland.com>
Date: Tue, 22 Apr 2003 18:08:03 GMT
Message-ID: <3EA58503.1D1CC161@remove_spam.peasland.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US