Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

need help on sql

Posted on 2014-10-23
10
Medium Priority
?
210 Views
Last Modified: 2014-10-28
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
0
Comment
Question by:sam_2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1336 total points
ID: 40400214
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40400219
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40400240
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1336 total points
ID: 40400252
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 664 total points
ID: 40400280
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
 
LVL 32

Expert Comment

by:awking00
ID: 40402075
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40402082
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
 
LVL 32

Expert Comment

by:awking00
ID: 40402121
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40402855
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
 

Author Closing Comment

by:sam_2012
ID: 40408990
thanks a lot.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question