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(description),'abc') >0;

Here description is an CLOB column
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Test each in your environment and see.

What do the explain plans show for each?

Set up a simple table, populate it with random data.

Then run tests against it.

Maybe create a loop of 1000 iterations, do the same query and check the stats?
0
sdstuberCommented:
neither one is good, but the LIKE should be more efficient because you won't be incurring sql - pl/sql context switches

but definitely test it for yourself
0
sdstuberCommented:
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(description),'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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
I went ahead as set up the simple test case I was referring to.

Based on the runs, they appear to be about the same but your mileage may vary.

dbms_random.string is limited to varchar2 so I had to limit the possible data to 4000 characters.  If you want more, it is a small tweak to generate larger dummy data.

drop table tab1 purge;
create table tab1(col1 clob);

begin
	for i in 1..10000 loop
		insert into tab1 values(dbms_random.string('a',dbms_random.value(1,4000)));
	end loop;
	commit;
end;
/


-- individual stats
set lines 120

set autotrace on
select count(*) from tab1 where lower(col1) like '%abc%';

select count(*) from tab1 where dbms_lob.instr(lower(col1),'abc') > 0;

set autotrace off

*/

--loop timings
declare
	start_time timestamp;
	junk number;
begin
	start_time := systimestamp;
	for i in 1..10000 loop
		select count(*) into junk from tab1 where lower(col1) like '%abc%';
	end loop;
	dbms_output.put_line('Run time: ' || to_char(systimestamp-start_time));

	start_time := systimestamp;
	for i in 1..10000 loop
		select count(*) into junk from tab1 where dbms_lob.instr(lower(col1),'abc') > 0;
	end loop;
	dbms_output.put_line('Run time: ' || to_char(systimestamp-start_time));
end;
/

Open in new window

0
sdstuberCommented:
In my tests the elapsed time was also approximately the same

Howerver

the pl/sql route consumed more pga memory
the sql LIKE route consumed more db time

interestingly - the LIKE performed fewer gets and logical reads.  So it seems like it should have been faster but it wasn't measurable for me
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
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.
0
slightwv (䄆 Netminder) Commented:
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:
http://www.experts-exchange.com/Database/Oracle/Q_28528077.html
0
awking00Information Technology SpecialistCommented:
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."
0
sdstuberCommented:
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.
0
sam_2012Author Commented:
thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.