Re: Normalization of attributes

Although Oracle's SOUNDEX function isn't very good, you could develop your own "soundex"-type function which would allow you to "compare" two strings using your (somewhat peculiar) own criteria.

select string1, string2
from a_table
where my_soundex(string1, string2) = my_desired_level_of_equality;

Peter Balon <balon_at_stud.uni-frankfurt.de> wrote in message news:38276336.D58B7313_at_stud.uni-frankfurt.de...
>
>
> Jonathan Lewis wrote:
> >
> > Should 'abcxx' match 'abcyy' ?
> > Your example is much more precise
> > than your general statement.
>
> Well, yes and no. 'abcxx' and 'abcyy' should be treated as same entries,
> but in practice there should be no such combination.
> We can assume that the prefix from T1 will be fully in T2 and vice
> versa, so
> that 'abc' match 'abcxx'.
>
> >
> > Also do you have any high-precision
> > access method for either table, or is
> > this the only condition that will be applied.
>
> This is the only condition in the where clause.
> Both rows have an unique index.
>
> >
> > Finally, how large is large, and what is
> > the time-scale you are aiming at ?
>
> 1e+6 in one and 1e+4 records in the other table.
> As for a time-scale there are two cases. First case is
> online-decision support. After the user hits return, he
> wants to know whether the value is in the larger table or not.
> The second case is when the user loads the smaller table
> and after a reasonable time wants to know which records are
> or are not in the table. A flag will be set in the smaller table
> for further processing.
>
>
> Peter
> >
> > --
> >
> > Jonathan Lewis
> > Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
> >
> > Peter Balon wrote in message

<38220929.6FE2F27B_at_stud.uni-frankfurt.de>...
> > >Hello,
> > >
> > >I am looking for a way to improve search and comparison speed on large
> > >tables. The problem is that I have to compare two strings which might
> > >have different length. I need to check if the prefixes are equal so
that
> > >
> > > T1.'abc' = T2.'abcd'
> > >and
> > > T1.'abcd' = T2.'abc'
> > >
> > >from both ends, with T1 and T2 as tables.
> > >Are there some known normalization techniques which I could apply?
> > >We are using Oracle 7.3 and will move to 8.x someday.
> > >
> > >Any comments would be greatly appreciated.
> > >
> > >thanks in advance
> > >
> > >Peter Balon
> > >
> > >
Received on Wed Nov 10 1999 - 17:05:02 CST