Thread: non-equi self-join optimization

I have a table of names with two subsets of entities. I want to find
those names from set 1 that are substrings of names from set 2 from the
same table. Basically the pared down query I want is something like
this:
select t1.myname, t2.myname  from mytable t1     inner join mytable t2      on position (t1.myname in t2.myname) > 0
wheret1.flag = 1    and t2.flag = 2   
;

I have gone through a few variations on the theme, but none perform too
well. Any advice on the best way to optimize a query like this would be
appreciated.

Thanks!

George

On Tue, Jan 17, 2006 at 12:57:30 -0800, George Pavlov <gpavlov@mynewplace.com> wrote:
> I have a table of names with two subsets of entities. I want to find
> those names from set 1 that are substrings of names from set 2 from the
> same table. Basically the pared down query I want is something like
> this:
> 
>  select t1.myname, t2.myname
>    from mytable t1 
>      inner join mytable t2
>        on position (t1.myname in t2.myname) > 0
>    where t1.flag = 1
>      and t2.flag = 2  
> ;
> 
> I have gone through a few variations on the theme, but none perform too
> well. Any advice on the best way to optimize a query like this would be
> appreciated.

I wouldn't expect this to be fast.
You would need some sort of index on which substrings are in which names
in table 2 to be able to use an index scan. You could build a table for
this, but this might be worse for you than what you are doing now.