CODEWITHSUNDEEP
sqloracle-database
nissamps
nissamps

Reputation: 3

Oracle Performance leading wildcards search

I have one select statement which joins with other table using wildcard search and performance of this statement is ridiculous

Is there any way Oralce to use index with LIKE '%string%' clause?

SQL

WITH x AS
        (SELECT /*+ PARALLEL(a 8)  PARALLEL(b 8) ORDERED */
               a.location AS a_name, UPPER (b.name) b_name
           FROM table_a a LEFT OUTER JOIN table_b b ON b.name LIKE (LOWER ('%' || a.name) || '%')
         ORDER BY a_name,  b_name)
SELECT DISTINCT a_name, b_name
  FROM x;

Upvotes: 0

Views: 282

Answers (1)

Gandalf
Gandalf

Reputation: 9845

Try using an Oracle Full Text Index.

Upvotes: 1

Related Questions