Results 1 to 4 of 4

Thread: Oracle escape

  1. #1
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Unanswered: Oracle escape

    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)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Oracle escape

    Originally posted by tal_olier
    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>

  3. #3
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Re: Oracle escape

    Originally posted by andrewst
    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 ?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Oracle escape

    Originally posted by tal_olier
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •