Re: Finding rows with text columns beginning with other text columns

On 10 May 2010, at 21:24, Christoph Zwerschke wrote:

> Am 10.05.2010 11:50 schrieb Alban Hertroys:
> > On 10 May 2010, at 24:01, Christoph Zwerschke wrote:
> >
> >> select * from b join a on b.txt like a.txt||'%'
> >>
> >> I feel there should be a performat way to query these entries,
> >> but I can't come up with anything. Can anybody help me?
> >
> > Have you tried using substring instead of like?
>
> How exactly? I tried this:
>
> substr(b.txt, 1, length(a.txt)) = a.txt
>
> but it cannot be optimized and results in a nested loop, too.

I feared as much, but it was worth a try.

Thinking more on the issue, I don't see a way to prevent the nested loop as there's no way to decide beforehand what part of the string to index for b.txt. It depends on a.txt after all.

You would basically need a cross-table index, those are not supported. If it were, you could create a functional index of substrings of b.txt with string lengths from a.txt (eeps, that'd be a table product!).

Your best solution is probably to add a column to b that contains the substring of b.txt that would match a.txt.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4be87c0b10418212361837!