Optimizing String-Searching Oracle Query

Question

I'm trying to optimizing an Oracle Query that looks like this:

select * from upcTable where upc like '%567%'

The query is fast for small search terms like 567, however it can get as long as 15 digits in which it can take ~1.5 seconds. Is there anyway to speed this up?

I've tried doing something like

select * from
(select * from rldb.productmaster where upc like '%567%')
where upc like '%380%'

select * from rldb.productmaster where upc like '%567380%'

In this case, the number of terms if about the same, but the first example is a bit faster. However when using the same technique for 15 digits, it's a bit faster but still too slow.

There's no reason why '%567%' would be faster than '%567380%' to return all rows (as it has to scan the same volume of source data). It might seem faster because the short the string to match the more chance it has of finding matches so it will return the first matches earlier. If you were going through a telephone book looking for people with a 'J' in their name, you'd get a list of 100 quicker than if you were searching for ones with a 'JU' in their name. — Gary Myers
– Gary Myers, Commented Jun 16, 2011 at 0:27

Tony Andrews · Accepted Answer · 2011-06-15 13:47:52Z

5

If you index the column like this:

create index upcIndex on upcTable (upc, pk);

(where pk is the primary key of the table)

then this query can perform an INDEX FAST FULL SCAN on that index:

select pk from upcTable where upc like '%567380%';

If you really need all the columns (select *) then you could try this:

select * from upcTable where pk in
(select pk from upcTable where upc like '%567380%');

answered Jun 15, 2011 at 13:47

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tbone · Accepted Answer · 2011-06-15 13:20:58Z

1

you won't be using any indexes doing a '%123%' (leading %). If you can search using '123%' then you can take advantage of an index.

answered Jun 15, 2011 at 13:20

tbone

15.5k3 gold badges37 silver badges42 bronze badges

2 Comments

Overload119 Over a year ago

This is true but I have to use the two %. The user won't know the UPC completely, so this function is meant to add an "autocomplete" The item UPC might by 55000 but in the database it is stored as 00055000

tbone Over a year ago

without any other restrictions, you're basically letting the user search for any string of any length in the field, so the db will need to perform a full scan to determine matches.

Collectives™ on Stack Overflow

Optimizing String-Searching Oracle Query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related