• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 86
  • Last Modified:

Oracle TEXT search question

Have a need for the TEXT search condition. After I indexed the column description in T1 with Oracle TEXT, I use the syntax "contains"

select count(1) from t1 where contains( lower( description), 'abcd', 1) >0;

However I got the error complaining  "lower( description)" is not indexed. I knew it was not for this, but how should I deal with this situation? If I do the following, it is fast, but it is not the way to form the sql.
 
select count(1) from t1 where contains(  description, 'abcd', 1) >0;
select count(1) from t1 where contains(  description, 'Abcd', 1) >0;
...
All combinations.


(for normal where clause:  select count(1) from t1 where lower( description) like 'abcd'; when the table is big, it is too slow)
0
jl66
Asked:
jl66
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
HuaMin ChenSystem AnalystCommented:
Try not to put lower below
select count(1) from t1 where contains( lower( description), 'abcd', 1) >0;

Open in new window


Try also instr to do the search.
0
 
jl66Author Commented:
In my original question, I already mentioned the query you gave, which caused ORA- error: lower( description) is not indexed.
0
 
HuaMin ChenSystem AnalystCommented:
Hi,
Is the speed fine after you've removed 'lower(...)' to your query?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
johnsoneSenior Oracle DBACommented:
The default behavior for a text index is case insensitive.  Unless you changed that when you created the index, there should be no need for the lower function.
0
 
jl66Author Commented:
I specifically made sure that I used the default one. the search result still showed the case sensitive. Thanks for info though.
0
 
jl66Author Commented:
Thanks.
0
 
slightwv (䄆 Netminder) Commented:
You closed this before I had time to post.

It is not case sensitive.  I've used it since somewhere around version 7 before it was called Text.

Please run this test case:
drop table tab1 purge;
create table tab1(col1 varchar2(20));

insert into tab1 values('HeLlO');
insert into tab1 values('hello');
insert into tab1 values('HELLO');
commt;

create index tab1_idx on tab1(col1) indextype is ctxsys.context;

select * from tab1 where contains(col1,'hello') > 0;
select * from tab1 where contains(col1,'HELLO') > 0;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Any reason you accepted a post that isn't even valid syntax?
0
 
johnsoneSenior Oracle DBACommented:
Honestly, I haven't used it since Oracle 8, before it was renamed to Text.  I did check the documentation and the BASIC_LEXER defaults still to case insensitive.

http://docs.oracle.com/database/121/CCREF/cdatadic.htm#CCREF0218

The default for mixed_case on the lexer is NO.  The AUTO_LEXER, if you are using that one, inherits the mixed_case value from the BASIC_LEXER, so it is still there and defaults to NO.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now