Index utilization question when using wild card searches

howemr
howemr used Ask the Experts™
on
I'm not a database guy in general, and far more comfortable in MS SQL than Oracle...so apologies in advance.

I have a fairly large table (say 2.5 million rows) with individual indexes on several columns -- A, B, and C.

If I initiate a query with conditions on both indexed and non-indexed fields -- with a wildcard on the non-indexed field(s) -- similar to this:

...WHERE A = "value1" and B = "value2" and K = "%partialvalue%" AND L = "%partialvalue%"...

Will the index on column A and/or B be used at all?  I've been told that it will NOT, but this seems illogical to me.  I would guess the optimizer is smart enough to "handle" this by breaking the request into parts (or something) -- but my understanding of optimizers is about nil.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Oracle Database Administrator III
Commented:
There is an EXPLAIN PLAN AS "your statement" that will answer your question definitively, and I may be wrong (and tired):  but clearly the wildcard conditions will require a full table scan, and I can't think of any rational reason for the optimizer to make multiple scans against the one table.
Geert GOracle dba
Top Expert 2009
Commented:
there is also some pointers in the oracle faq:
http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ#Why_is_Oracle_not_using_the_darn_index.3F

the whole article is a good read


more indept:
http://www.orafaq.com/node/1403

Author

Commented:
@dvz:  more specifically, my thought would be that the optimizer would (since all parts of the query were ANDs) do one scan using the index to return the matching values and then scan those results using the "wildcards parts". Kind of like building a nested query. Seems on the surface to be more efficient that way.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

David VanZandtOracle Database Administrator III
Commented:
I understand -- it may be worthwhile to compare the nested query against an unnested one.  But Geert has two good sources -- reminding me to consider your first index cardinality -- is it returning 2%, or 98%, for example.  

Thinking a moment that you're not familiar with the tuning nuances in Oracle:  there are some built-in tools (EXPLAIN PLAN, TKPROF) you should read up on, as they'll measure the estimated and relative CPU cost.

Author

Commented:
I was finally able to determine that an index range scan WAS used when the query was modified to use an index field along with a wild-card search.

Thanks for the links to the documentation and tools.
David VanZandtOracle Database Administrator III

Commented:
Glad to be a sounding board for you!
Geert GOracle dba
Top Expert 2009

Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial