Joining tables using LIKE or INSTR
Lu_BuOct 28 2008 — edited Oct 29 2008Ok, so first off I'm pretty sure that joining using LIKE or INSTR is probably not a great idea, but my hands are a bit tied here. If you can think of a better way to do this I would love to hear it. I need to join a Group name Table 1 to text within a large text field in Table 2. What I have below returns the correct data but it is very slow. When looking at the explain plan 99% of the time is spent on a Merge Join Cartesian and a Buffer Sort.
Thanks and I hope you know of a better way to accomplish this.
SELECT y.support_group, c.full_name, c.login_name
FROM dbo.escalation_support_group y
dbo.esg_compositeesgmembership c
WHERE c.escalation_groups LIKE '%' || y.support_group || '%'
-- instr(y.support_group,c.ESCALATION_GROUPS) > 0 --alternate join option