We are creating a data engine for our product that will enable access several dbs.

There is an option of specifying ESCAPE char when using 'like' expression in a query.

Is there any performance penalty if we'll use it all the time instead if only when there are escape chars:

e.g.

select * from CA_ARO_L_SAPTRANS

where value01 like 'M%' ESCAPE '\'


Thanks,

Tal (otal@mercuy.co.il)

I just tried it with TIMING ON in SQL Plus and there was a slight increase in the time taken, about 10% :-

SQL> select count(*) from all_objects where object_name like '%EMP%'
2 /

COUNT(*)
----------
564

real: 681
SQL> /

COUNT(*)
----------
564

real: 640
SQL> select count(*) from all_objects where object_name like '%EMP%' ESCAPE '/'
2 /

COUNT(*)
----------
564

real: 741
SQL> /

COUNT(*)
----------
564

real: 701
SQL>

Are you sure the same will happen with 30 current users and 50GB db ?

Not sure what you mean. What does the number of users or the size of the database have to do with the performance of the LIKE function? My test just shows that there is some apparent overhead in using the ESCAPE clause. It probably wouldn't be 10% in all queries - my test query was a particularly nasty one because the search string began with '%'. I just tried it with 'EMP%' instead of '%EMP%' and the overhead became unnoticeable. Presumably this has something to do with how many records it has to apply the ESCAPE to.

I suspect you are in unknown territory here, and the only way to be sure that it will not be detrimental to your application is to benchmark it with your tables and queries.