OraFAQ Forum: SQL & PL/SQL » LIKE vs NOT LIKE operator

Home » SQL & PL/SQL » SQL & PL/SQL » LIKE vs NOT LIKE operator (Oracle 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

LIKE vs NOT LIKE operator [message #662248]

Fri, 21 April 2017 08:08

srinivas.k2005
Messages: 404
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

Report message to a moderator

Re: LIKE vs NOT LIKE operator [message #662249 is a reply to message #662248]

Fri, 21 April 2017 08:13

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: LIKE vs NOT LIKE operator [message #662250 is a reply to message #662248]

Fri, 21 April 2017 08:14

John Watson
Messages: 8929
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".

Report message to a moderator