instr vs. like

[RottPaws]

I know 2 ways to filter for records that do or do not include some specific text in a field.


WHERE FIELD LIKE '%SUM TEXT%'
vs
WHERE INSTR(FIELD, 'SUM TEXT')>0

=================== or =======================

WHERE FIELD NOT LIKE '%SUM TEXT%'
vs
WHERE INSTR(FIELD, 'SUM TEXT')=0


Does anyone have an opinion on which way is better/faster/more efficient or a link to some information on this topic?


_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[SantaMufasa]

Rott,

I would be very surprised if there were a difference in processing speed between you alternatives. I use both fairly interchangably.

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:58 (19Mar04) UTC (aka "GMT" and "Zulu"), 18:58 (18Mar04) Mountain Time)

 

[sem]

I'm pretty sure that INSTR is more efficient. Of course, from Oracle point of view (i.e. taking into account execution plan only) they are equivalent, but I think that C implementation of INSTR is much more simple.

Regards, Dima

http://seminihin.narod.ru

 

[RottPaws]

Thanks. I've also been using them interchangeably, but I was just wondering if one or the other was better . . .

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[Turkbear]

Here is output from each method( separate sessions to try to avoid caching advantages that may affect results):

SQL> set autotrace on
SQL> 
SQL> select dept_nbr from hr_public where empl_nm like 'Grec%';

DEPT_NBR
----------
T792309


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=2 Bytes=118)
   1    0   TABLE ACCESS (FULL) OF 'HR_PUBLIC' (Cost=18 Card=2 Bytes=1
          18)





Statistics
----------------------------------------------------------
         23  recursive calls
          6  db block gets
        275  consistent gets
        272  physical reads
          0  redo size
        373  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Second session:

SQL> set autotrace on
SQL> select dept_nbr from hr_public where
  2  instr(empl_nm,'Grec') > 0;

DEPT_NBR
----------
T792309


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=255 Bytes=15
          045)

   1    0   TABLE ACCESS (FULL) OF 'HR_PUBLIC' (Cost=18 Card=255 Bytes
          =15045)





Statistics
----------------------------------------------------------
          0  recursive calls
          6  db block gets
        272  consistent gets
          0  physical reads
          0  redo size
        373  bytes sent via SQL*Net to client
        339  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Now you can compare...

 

[sem]

Calling them from different sessions doesn't prevent the second statement from using data cached by the first one. And of course this is not quite illustrative, because as I've already mentioned they both produce equivalent execution plans, though processor time may differ.
I traced it and the result was the opposite to my first guess: general procedure was more efficient than specific one. I can not comment it, but have to recall my first hurried answer :-(

Regards, Dima

http://seminihin.narod.ru