Home » SQL & PL/SQL » SQL & PL/SQL » LIKE vs NOT LIKE operator (Oracle 12c)
LIKE vs NOT LIKE operator [message #662248] Fri, 21 April 2017 08:08 Go to next message
srinivas.k2005
Messages: 364
Registered: August 2006
Senior Member
Hello,

Please advise which one is better, LIKE operator or NOT LIKE operator in the where condition of the query.

Consider both conditions in table is having same records count i.e. for example, query with condition LIKE operator will result in 1 million records and
with NOT LIKE operator, 1 million records as well.

Please advise.

Regards,
SRK
Re: LIKE vs NOT LIKE operator [message #662249 is a reply to message #662248] Fri, 21 April 2017 08:13 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
srinivas.k2005 wrote on Fri, 21 April 2017 06:08
Hello,

Please advise which one is better, LIKE operator or NOT LIKE operator in the where condition of the query.

Consider both conditions in table is having same records count i.e. for example, query with condition LIKE operator will result in 1 million records and
with NOT LIKE operator, 1 million records as well.

Please advise.

Regards,
SRK
post EXPLAIN PLAN for each alternative
Re: LIKE vs NOT LIKE operator [message #662250 is a reply to message #662248] Fri, 21 April 2017 08:14 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
If you are selecting half the table, the only sensible execution plan will be a full table scan. So LIKE or NOT LIKE is not relevant. Run the tests and you'll see.

By the way, I wish you would not say "record" when you mean "row".

Re: LIKE vs NOT LIKE operator [message #662252 is a reply to message #662250] Fri, 21 April 2017 08:38 Go to previous messageGo to next message
srinivas.k2005
Messages: 364
Registered: August 2006
Senior Member
I checked cost from explain plan for both conditions and both remains almost the same. Yes, it is doing full table scan.
Re: LIKE vs NOT LIKE operator [message #662253 is a reply to message #662252] Fri, 21 April 2017 08:47 Go to previous message
srinivas.k2005
Messages: 364
Registered: August 2006
Senior Member
Thanks for the information. Will check again and come back.
Previous Topic: DEFINE_COLUMN in DBMS_SQL
Next Topic: Pivot result using "Pivot" keyword
Goto Forum:
  


Current Time: Tue Jan 23 05:05:03 CST 2018

Total time taken to generate the page: 0.04030 seconds