OraFAQ Forum: Text & interMedia » performance issue with indexes

Home » Server Options » Text & interMedia » performance issue with indexes

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

performance issue with indexes [message #590585]

Fri, 19 July 2013 07:48

367364
Messages: 10
Registered: July 2013
Location: Hyderabad

Junior Member

Hi,

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..

Report message to a moderator

Re: performance issue with indexes [message #590607 is a reply to message #590585]

Fri, 19 July 2013 15:48

Barbara Boehmer
Messages: 9104
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  /

LOCATION
------------------------------------------------------------
EUROPE
EUROPEAN
EUROPE AND ASIA

3 rows selected.

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

LOCATION
------------------------------------------------------------
EUROPE
EUROPEAN
EUROPE AND ASIA
UNITED STATES OF AMERICA

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 23:31:29 CST 2025