6

Can some one tell me the difference between INSTR and LIKE in Oracle?

Which one is faster in Oracle10g?

Ollie
  • 17,058
  • 7
  • 48
  • 59
Naveenraja Subramaniam
  • 367
  • 2
  • 6
  • 17
  • See also http://stackoverflow.com/questions/1197026/is-substr-or-like-faster-in-oracle – Vadzim Dec 13 '16 at 11:38
  • For [MySql](https://stackoverflow.com/questions/2451608/which-is-faster-instr-or-like) this [answer](https://stackoverflow.com/a/22171511/480894) says that INSTR is faster. – Roland Jun 13 '17 at 11:27

4 Answers4

8

That depends on the data and on the pattern. If you use like 'a%', then Oracle can use a BTree indexes to look up the matches because it can search the btree with the start of the pattern and then consider only the subtree.

This doesn't work for LIKE '%a' but you can work around this by creating a calculated column which reverses all values from the column you want to search (so you get the pattern above).

If you use hashed indexes, there is little that Oracle can do but scan the whole index. It might sill be faster when there are only few different values.

I'm not sure whether INSTR can ever use an index because it has no fixed anchor.

So like with all performance questions:

  1. Fill the database with some realistic test data and run some tests.
  2. Always write your code in a way that it can be optimized easily later, when you know about the bottlenecks
  3. Never guess what might be slow. You'll be wrong 90% of the time. Always measure.
Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
3
  • INSTR searches for a string inside another string, with options for direction, starting positions etc
  • LIKE is simple pattern matching and standard SQL

Neither will be "faster" consistently because they aren't comparable: it depends what you need to do, your data, how you search etc

gbn
  • 422,506
  • 82
  • 585
  • 676
2

INSTR is a oracle function since Oracle 8i for finding a substring, and LIKE is an SQL condition mainly used for matching string with wildcards.

I expect INSTR to be a bit faster since it's less complex but I didn't measure it.

Michał Šrajer
  • 30,364
  • 7
  • 62
  • 85
  • Maybe INSTR is less complex but the performance of a statement does not depend if a function used uses some cpu cycles more or less for evaluation but from how much data must be retrieved and evaluated to get the result. – miracle173 Jun 09 '17 at 05:47
1

This page says that INSTR is faster:

http://oracle.veryoo.com/2013/03/performance-comparation-between-like.html

EDIT: Archive link since the above is no longer available: http://web.archive.org/web/20160301212009/http://oracle.veryoo.com/2013/03/performance-comparation-between-like.html

Also on SO for MySql this answer says that INSTR is faster.

Roland
  • 7,525
  • 13
  • 61
  • 124
  • the page isn't available. – miracle173 Jun 09 '17 at 05:32
  • Thank you that you searched vor a valid link. But I have other complaints, too. On stackexchange answers that contain only links aren't appreciated. The relevant content of the link should be presented in the post, too. But I think the measurements presented in this link are of no relevance. The comparison is between `instr(title,' manual')>0` and `title like'%manual%'` The first expression searches for titles starting with 'manual' and the latter for titles that contain 'manual' anywhere. – miracle173 Jun 13 '17 at 06:28
  • Besides that we are told that the table has 11 Mio. rows and we see the elapsed times, We don't know the structure of the tables, the length of the title field, no execution plans, no statistics, nothing about the test environment. This test should be ignored. – miracle173 Jun 13 '17 at 06:29
  • [here is an experiment that has been conducted on a mysql database](https://stackoverflow.com/a/2451665/754550) (and lacks the same additional information as the experiment in your link): the result is that there is no difference – miracle173 Jun 13 '17 at 09:21
  • @miracle173 `instr(title,'manual')>0` will search for titles which have 'manual' anywhere exactly like `like` does. [INSTR documentation](https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1103.htm#OLADM564) – Roland Jun 13 '17 at 09:59
  • @miracle173 from the link that you provided, this answer says that [INSTR is faster](https://stackoverflow.com/a/22171511/480894) – Roland Jun 13 '17 at 11:25
  • yes, I was wrong with the INSTR function, it works as you describe. The answer you are referencing does not fetch data at all, it calls a function that 100 000 000 repeats the same operation on the same strings and measures that 'like' needs 4 seconds mor, so for only 10 000 000 repetitions it will take 0.4 seconds longer. Assume we have similar numbers for Oracle. Can you tell me why in the article you are referencing Oracle needs 20 seconds more when using 'like'? This is 500 times larger than 0.4. The numbers in the article you link to in your answer do not make sense. – miracle173 Jun 13 '17 at 16:35
  • @miracle173 I don't know. Maybe Oracle just has a different implementation? – Roland Jun 15 '17 at 06:41