1

I have a simple search store procedure in Oracle 11GR2 in a table with over 1.6 million records. I am puzzled by the fact that if I want to search for a work inside a column, such as "%boston%", it would take 12 seconds. I have an index on the name collumn.

select description from travel_websites where name like "%boston%";

If I only search for a word start with Boston like "boston%", it only takes 0.15 seconds.

select description from travel_websites where name like "boston%";

I added an index hint and try to force optimizer to use my index on the name column, it did not help either. 

select description /*+ index name_idx */  from travel_websites where name like "%boston%";

Any advises would be greatly appreciated.

infused
  • 24,000
  • 13
  • 68
  • 78
dave
  • 296
  • 1
  • 12
  • 26
  • 1
    In addition to the answers below you could consider a function based index if there is a consistent rule about the location of the search term within the text column. E.g. if Boston (or any other search term) starts at position X or after the (1st, 2nd, etc) space. Then matching your where predicate to the index function can result in the use of that function. – Karl Aug 29 '12 at 21:26

2 Answers2

6

You cannot use an index range scan for a predicate that has a leading wildcard (i.e. like '%boston%'). This makes sense if you think about how an index is stored on disk-- if you don't know what the first character of the string you are searching is, you can't traverse the index to look for index entries that match that string. You may be able to do a full scan of the index where you read every leaf block and search the name there to see if it contains the string you want. But that requires a full scan of the index plus you then have to visit the table for every ROWID you get from the index in order to fetch any columns that are not part of the index that you just full-scanned. Depending on the relative size of the table and the index and how selective the predicate is, the optimizer may easily decide that it is quicker to just do a table scan if you're searching for a leading wildcard.

Oracle does support full text search but you have to use Oracle Text which would require that you build an Oracle Text index on the name column and use the CONTAINS operator to do the search rather than using a LIKE query. Oracle Text is very robust product so there are quite a few options to consider both in building the index, refreshing the index, and building the query depending on how sophisticated you want to get.

Your index hint is not correctly specified. Assuming there is an index on name, that the name of that index is name_idx, and that you want to force a full scan of the index (just to reiterate, a range scan on the index is not a valid option if there is a leading wildcard), you would need something like

select /*+ index(travel_websites name_idx) */ description
  from travel_websites
 where name like '%boston%'

There is no guarantee, however, that a full index scan is going to be any more efficient than a full table scan. And it is entirely possible that the optimizer is choosing the index full scan already without the hint (you don't specify what the query plans are for the three queries).

Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
2

Oracle (and as far as I know most other databases) by default indexes strings so that the index can only be used to look up string matches from the start of the string. That means, a LIKE 'boston%' (startswith) will be able to use the index, while a LIKE '%boston' (endswith) or LIKE '%boston%' (contains) will not.

If you really need indexes that can find substrings fast, you can't use the regular index types for strings, but you can use TEXT indexes which sadly may require slightly different query syntax.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294