Home » Server Options » Text & interMedia » performance issue with indexes
performance issue with indexes [message #590585] Fri, 19 July 2013 07:48 Go to next message
Messages: 10
Registered: July 2013
Location: Hyderabad
Junior Member

Initially my query is like this

select * from emp
where location like '%EUROPE'%;

Even though i created index it is not using so i created CTXSYS.CONTEXT like
create index LOC_A_IDX on emp(location) INDEXTYPE IS CTXSYS.CONTEXT;
Now i modified my query as

select * from emp
where contains(location,'%EUROPE%')>0;

Performance improved, but i have an issue here.My requirement is, if i search with a value it is giving results,
but if i won't give any value, it is not fetching any record.
select * from emp
where contains(location,'%%')>0;

As in like operator if we give location like '%%'; it will fetch all the values. but in contain it is not working
please help..

Re: performance issue with indexes [message #590607 is a reply to message #590585] Fri, 19 July 2013 15:48 Go to previous message
Barbara Boehmer
Messages: 8954
Registered: November 2002
Location: California, USA
Senior Member
I see this is your first post, so welcome to the OraFAQ forums.

I am not seeing the same problem that you are describing. What is your Oracle version? Please see the demonstration below. If you are getting different results then please post a copy and paste of a complete test as I did below.

Also, using prefix and substring indexing, as in the example below, should increase performance with wildcard searches and setting the wildcard_maxterms to 0 should eliminate the potential for exceeding the expansion limit, although you may experience memory problems.

SCOTT@orcl_11gR2> create table emp (location  varchar2(60))
  2  /

Table created.

SCOTT@orcl_11gR2> insert all
  2  into emp values ('EUROPE')
  3  into emp values ('EUROPEAN')
  4  into emp values ('EUROPE AND ASIA')
  5  into emp values ('UNITED STATES OF AMERICA')
  6  select * from dual
  7  /

4 rows created.

SCOTT@orcl_11gR2> begin
  2    ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
  3    ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
  4    ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH',3);
  5    ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', 4);
  6    ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
  7    ctx_ddl.set_attribute('mywordlist','WILDCARD_MAXTERMS', 0);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> create index LOC_A_IDX on emp (location) INDEXTYPE IS CTXSYS.CONTEXT
  2  parameters ('wordlist mywordlist')
  3  /

Index created.

SCOTT@orcl_11gR2> select * from emp
  2  where  contains (location, '%EUROPE%') > 0
  3  /


3 rows selected.

SCOTT@orcl_11gR2> select * from emp
  2  where  contains (location,'%%') > 0
  3  /


4 rows selected.

[Updated on: Fri, 19 July 2013 15:55]

Report message to a moderator

Previous Topic: Is there a simple "Fuzzy" LIKE query to match similar strings?
Next Topic: while updating error recursive SQL level 1
Goto Forum:

Current Time: Wed Feb 08 20:44:19 CST 2023