Home » SQL & PL/SQL » SQL & PL/SQL » Index not getting used when LIKE '%..%' IS USED
Index not getting used when LIKE '%..%' IS USED [message #196884] Mon, 09 October 2006 00:51 Go to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
hi frns,

I need u r help urgently.
I have created on index on ename column of emp table but when i am trying to say

select * from emp where ename like '%SMITH%';

index is not getting used.Why?

I have nearly 30lakh recs in emp table and when i am searching it is almost getting hanged up.
Can anyone plz suggest me what can be done so that index will get used.

Regards,
Frank
Re: Index not getting used when LIKE '%..%' IS USED [message #196887 is a reply to message #196884] Mon, 09 October 2006 00:56 Go to previous messageGo to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
My Stored procedure contains code as follows:

IF(LENGTH(TRIM(p_first_name))>0)THEN
p_sqlqry1:=p_sqlqry1||' AND UPPER(mhp.first_name) LIKE '||'''%'||UPPER(p_first_name)||'%''';
END IF;
IF(LENGTH(TRIM(p_last_name))>0)THEN
p_sqlqry1:=p_sqlqry1||' AND UPPER(mhp.last_name) LIKE '||'''%'||UPPER(p_last_name)||'%''';
END IF;
IF(LENGTH(TRIM(p_city))>0)THEN
p_sqlqry1:=p_sqlqry1||' AND UPPER(mpd.city) LIKE '||'''%'||UPPER(p_city)||'%''';
END IF;

Regards,
frank
Re: Index not getting used when LIKE '%..%' IS USED [message #196898 is a reply to message #196884] Mon, 09 October 2006 01:38 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Try forcing the index by using like this:-

SELECT /*+INDEX (IND_XYZ) */E FROM XYZ WHERE E LIKE 'G%'

In brackets is the index name.

thanks
Re: Index not getting used when LIKE '%..%' IS USED [message #196899 is a reply to message #196898] Mon, 09 October 2006 01:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Well, that's another query altogether! (besides, an index hint also takes the table-alias)
If you don't prefix your search-condition with a wildcard (%), the hint will probably not be necessary anyway!
To use an (b-tree)index, you have to provide the first (couple of) characters.
Oracle Text indexes might be an alternative

[Updated on: Mon, 09 October 2006 01:45]

Report message to a moderator

Re: Index not getting used when LIKE '%..%' IS USED [message #196920 is a reply to message #196899] Mon, 09 October 2006 02:53 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I saw a thread either here or AskTom (probably Tom) where Oracle Text was used for full LIKE-like searches. It involves indexing every subset of the string, which can create a pretty big index. But I don't think it would support the LIKE operator - you'd have to use the Oracle Text function-style operators. Do a search and look for it.

If you are wondering why a regular index does not work, imagine yourself looking up a paper-phonebook for every name containing SMITH, including SMITH, WRIGHTSMITH, JONES-SMITHSON. This is effectively the problem you are asking Oracle to solve with bog-standard index functionality - not so easy huh?

Ross Leishman
Previous Topic: How to get the names that have mistakes from 2 tables
Next Topic: query Filter
Goto Forum:
  


Current Time: Mon Dec 05 21:14:04 CST 2016

Total time taken to generate the page: 0.16140 seconds