We help IT Professionals succeed at work.
Get Started

Index utilization question when using wild card searches

howemr
howemr asked
on
398 Views
Last Modified: 2013-03-01
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
Sr Software Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 3 Answers and 7 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE