Swaminathan K
asked on
need help on sql
Hi ,
I need to know the difference between using like operator or DBMS_LOB.INSTR for an CLOB column. I need to know which one is better and efficient in processing
Select *
from user_notes where lower(description) like '%abd%'
or
Select *
From user_notes where DBMS_LOB.INSTR(lower(descr iption),'a bc') >0;
Here description is an CLOB column
I need to know the difference between using like operator or DBMS_LOB.INSTR for an CLOB column. I need to know which one is better and efficient in processing
Select *
from user_notes where lower(description) like '%abd%'
or
Select *
From user_notes where DBMS_LOB.INSTR(lower(descr
Here description is an CLOB column
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
note, if you have a specific substring that you will always be searching for, then you could create a function-based index that would be very efficient
CREATE INDEX idx_user_notes_abc ON user_notes (DBMS_LOB.INSTR(lower(desc ription),' abc'));
but, this does not work if you will be searching for variable strings because you would need to create a separate index for every possible string.
CREATE INDEX idx_user_notes_abc ON user_notes (DBMS_LOB.INSTR(lower(desc
but, this does not work if you will be searching for variable strings because you would need to create a separate index for every possible string.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Couldn't a context index be in order here? I've had some pretty good experience with them although I've never combined them with a function like lower() on the indexed column.
awking00,
Yes, Oracle Text is the correct way to go and it is case insensitive so no need for lower.
Check out the askers previous questions, for some reason they have been asked to NOT use a Text index:
https://www.experts-exchange.com/questions/28528077/need-help-on-oracle-indexes.html
Yes, Oracle Text is the correct way to go and it is case insensitive so no need for lower.
Check out the askers previous questions, for some reason they have been asked to NOT use a Text index:
https://www.experts-exchange.com/questions/28528077/need-help-on-oracle-indexes.html
slightwv,
That's an odd restriction. As you and sdstuber both indicated, "You can use this paint brush to drive that nail, but not this hammer."
That's an odd restriction. As you and sdstuber both indicated, "You can use this paint brush to drive that nail, but not this hammer."
When I see people doing searches like this I suggest this exercise to try to relate to the problem.
Grab a dictionary, find all words that begin with "p".
Within a few seconds they have pinched off the pages with those words.
The alphabet makes a great index. If your dictionary has labeled thumb tabs it's even easier. Regardless, it's a trivial effort.
Now, find all words that have a "p" in the middle.
When they inevitably responde: "I can't do that - I'd have to read the entire dictionary"
Then they have the answer as to why these searches don't work.
Grab a dictionary, find all words that begin with "p".
Within a few seconds they have pinched off the pages with those words.
The alphabet makes a great index. If your dictionary has labeled thumb tabs it's even easier. Regardless, it's a trivial effort.
Now, find all words that have a "p" in the middle.
When they inevitably responde: "I can't do that - I'd have to read the entire dictionary"
Then they have the answer as to why these searches don't work.
ASKER
thanks a lot.
but definitely test it for yourself