Using the 'OR' keyword in expressions against FGDB

[MichaelThompson]

I have a File Geodatabase table that I am querying against with an attribute index on a field called "STREET_NAME".
If I perform this query, it is very fast: SELECT * FROM STREET_RANGE WHERE STREET_NAME LIKE 'W JACKSON%'
However, if I add an OR condition and both sides of the condition reference the STREET_NAME field, it becomes very slow:
SELECT * FROM STREET_RANGE WHERE STREET_NAME LIKE 'W JACKSON%' OR STREET_NAME LIKE 'JACKSON%'
As you can see, I'm using LIKE in my expression, so using 'IN' instead of 'OR' is not an option for me. I read somewhere that if you 'OR' two conditions that reference the same indexed field, the index is not used. True statement? What are my options?

Thanks!
Michael Thompson
Westminster, CO

[VinceAngelo]

Are you using the File Geodatabase API or ArcObjects?  (This Forum is for the
FGDB API.)

I'd recommend benchmarking your results, with an expectation that the index can't
be used with an OR, and compare it to the overhead of detecting duplicate rows
in multiple singleton queries.  If there are a number of alternates, you might want
to select *only* the rowid for each, then execute a SELECT * with an IN over rowids
(though, since file geodatabase is not based on a full SQL engine, there might
not be much to gain through this route).

- V

[DavidSousa]

If you have two or more predicates in a WHERE clause, and they are connected by the boolean OR operator, the query processor will not be able to optimize the query by using an index.  This is true for the FileGDB query processor/optimizer, and is also true in varying degrees with other DBMS.

Even in the case of using a single LIKE predicate, the FileGDB optimizer can only use the index when the pattern string has a non-wildcard prefix.  Your example was able to use the index because the wildcard was at the end of the pattern string:

     WHERE STREET_NAME LIKE 'W JACKSON%'

But, the index cannot be used in the following case:

     WHERE STREET_NAME LIKE '% JACKSON'

I have not tested this extensively with other DBMS, but in general I expect that the LIKE predicate cannot be optimized using an index, especially in cases where there is no prefix in the pattern string.