18

We're using Oracle 11g database.
As you may or may not know, if you use wildcard query with "%" in front of the string, the column index is not being used and a full table scan is happening.

It looks like there isn't a definitive suggestion on how to improve this kind of query, but perhaps you could share some valuable information from your experience on how to optimize the following query:

SELECT * 
  FROM myTable 
 WHERE UPPER(CustomerName) like '%ABC%' 
    OR UPPER(IndemnifierOneName) like '%ABC%' 
    OR UPPER(IndemnifierTwoName) like '%ABC%';

...where all 3 columns are of type varchar2(100) and ABC is a value of variable input parameter.

@All suggesting CONTEX index, please note my data gets updated any time of the day every day and this index requires re-syncing, hence it's not a good option for a table of 1.5 million rows, sorry.

P.S. I'll upvote every answer, so please do keep them coming.

Ruslan
  • 9,927
  • 15
  • 55
  • 89
  • 2
    Don't do that kind of query? It has to do a table scan because the leading `%` indicates that there could be anything at the front of the field. This means it needs to search the contents of every row of that field. – JNK Jun 03 '11 at 15:53
  • thanks, unfortunately it's not an option – Ruslan Jun 03 '11 at 15:55
  • First add a function based index on the fields CustomerName, IndemnifierOneName, IndemnifierTwoName and then follow the suggestions from this article: http://www.dba-oracle.com/t_sql_like_clause_index_usage.htm – Chandu Jun 03 '11 at 15:56
  • Possible duplicate of [Does Oracle support full text search?](http://stackoverflow.com/questions/202623/does-oracle-support-full-text-search). While not word-for-word, the desired functionality is... – OMG Ponies Jun 03 '11 at 16:10
  • Also: http://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql – OMG Ponies Jun 03 '11 at 18:03
  • @Cybernate wildcard query with "anywhere" substring (like mine) won't use function based indexes – Ruslan Jun 06 '11 at 08:26
  • @OMG Ponies why have you changed british (i.e. original) english for "optimise" to american "optimize"? LOL – Ruslan Jun 06 '11 at 08:28

5 Answers5

9

As already mentioned you could add a ctx context index to the name columns.

assuming a small number of records get updated, 1 option is to refresh your index daily. (and record when it happened)

then add a lastupdate date column & index to your table being searched.

It should be possible to scan your ctx index for the majority of the old unchanged data and select from the small percentage of updated data using the traditonal LIKE e.g:

WHERE (lastupdated<lastrefresh AND contains(name,'%ABC%')) 
   OR (lastupdated>lastrefresh AND name like '%ABC%')

NOTE: you may find your query plan goes a little mental (lots of bitmap conversions to row ids) in that case split the 2 parts of the OR into a UNION ALL query. e.g

SELECT id FROM mytable   
    WHERE 
    (lastupdate>lastrefresh and name LIKE '%ABC%')
    UNION ALL
    SELECT id FROM mytable   
    WHERE lastupdate<lastrefresh and CONTAINS(name, '%ABC%', 1) > 0
Kevin Burton
  • 11,676
  • 2
  • 24
  • 37
6

The only optimization is to not use that type of query and instead use the native capabilities of the database platform:

See Oracle Text: http://www.oracle.com/technetwork/database/enterprise-edition/index-098492.html

The common answer for SQL Server related questions would be Full Text Search.. nice to see Oracle has something as good or better.

Fosco
  • 38,138
  • 7
  • 87
  • 101
3

The UPPER() is killing your indexes before anything, consider using a regexp. The initial % may avoid a normal index scan, but not always results in a Full Table Scan but into a Full Index Scan, which is faster than the FTS.

I suppose that 'ABC' is variable. If not, a function index is the way to go.

Magnilex
  • 11,584
  • 9
  • 62
  • 84
Samuel
  • 2,430
  • 2
  • 19
  • 21
  • yes, it is a variable, I'll edit the q to note that, thanks. there is an index on UPPER(CUSTOMERNAME) and it's not being used according to Explain Plan. – Ruslan Jun 06 '11 at 10:48
  • For the fast full scan every column in the query should be covered by the index. You could try "SELECT UPPER(CustomerName) FROM myTable WHERE UPPER(CustomerName) like '%ABC%' " to see if results into a FFS? – Samuel Jun 06 '11 at 11:08
  • Sorry, I guess it does use the index: SELECT STATEMENT, GOAL = ALL_ROWS Cost=1930 Cardinality=78206 Bytes=1173090 INDEX FULL SCAN Object owner=POSOWN Object name=XIF20CR_PROPOSALSEARCH Cost=1930 Cardinality=78206 Bytes=1173090 – Ruslan Jun 06 '11 at 13:20
  • Is performance acceptable with real data? If so, you could explore this option, with an index covering those three columns, or three indexes and three queries. If you need more columns apart of that three, I think you can retrieve the rowid and do subsequent lookups, something like : select * from mytable where rowid in (select rowid from mytable where upper(customer) like '%abc%')... probably a hint could do the same. – Samuel Jun 06 '11 at 14:13
  • that does a full table scan i'm afraid :( I've tried this: 'select * from cr_proposalsearch where rowid in ( select rowid from cr_proposalsearch ps where UPPER(ps.CustomerName) like '%ABC%' ) – Ruslan Jun 06 '11 at 14:43
0

Sometimes this kind of query is unavoidable -- extracting the domain from a URL, or perhaps the root from a word with a prefix and a suffix.

You can resort to a full text index with or without a custom tokenizer.

Or if the strings you're searching for are finite in number and known in advance (e.g. you're working with a limited set of domain names that need to be extracted from a URL) you can use a deterministic function which can be indexed.

http://www.akadia.com/services/ora_function_based_index_2.html

Tim
  • 8,669
  • 31
  • 105
  • 183
-2

Use Oracle text BUT the slightly newer CTXCAT variation - this domain index is updated as part of the transaction that inserts/updates the row in question and is therefore always up-to-date - See Oracle's own Oracle Text documentation for details.

Martin Ridgway
  • 1