This discussion is archived
7 Replies Latest reply: Jul 11, 2013 12:22 PM by NaranH RSS

SQL cache case and space insensitivity

NaranH Newbie
Currently Being Moderated

Hi

 

I think Oracle version 9i onwards supports case insensitive SQL cache, i.e. SQL statements in cache may be matched in a case and space insensitive manner. Please can someone provide an authoritative answer which either confirms my assertion or points me in the right direction where I can check these facts.

 

I look forward to any responses.

 

Warm regards,

  • 1. Re: SQL cache case and space insensitivity
    Tubby Guru
    Currently Being Moderated

    NaranH wrote:

     

    Hi

     

    I think Oracle version 9i onwards supports case insensitive SQL cache, i.e. SQL statements in cache may be matched in a case and space insensitive manner. Please can someone provide an authoritative answer which either confirms my assertion or points me in the right direction where I can check these facts.

     

    I look forward to any responses.

     

    Warm regards,

     

    http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm#i40017

     

    "

    The text of the SQL statements or PL/SQL blocks must be identical, character for character, including spaces, case, and comments.

    "


    To my knowledge this has always been the case. Should be simple enough for you to pull up the documentation for other releases as I did for 11.2 and answer this question yourself now that you know where to look.


    Cheers,

  • 2. Re: SQL cache case and space insensitivity
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated


    Best answer is probably a simple demo:

     

     

    SQL> delete /*+ find this */ from t1 where owner = 'XXX';
    
    0 rows deleted.
    
    SQL> DELETE /*+ find this */ from t1 where owner = 'XXX';
    
    0 rows deleted.
    
    SQL> delete /*+ find this */ from    t1   where    owner = 'XXX';
    
    0 rows deleted.
    
    SQL> begin
      2  delete /*+ find this */ from t1 where owner = 'XXX';
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select sql_id, sql_text from v$sql where sql_text like '%find this%' and sql_text not like '%v$sql%';
    
    SQL_ID
    -------------
    SQL_TEXT
    ------------------------------------------------------------------
    7tc7jtr6jjd5r
    delete /*+ find this */ from t1 where owner = 'XXX'
    
    a4ud12x5fxmnw
    begin delete /*+ find this */ from t1 where owner = 'XXX'; end;
    
    bn65fyws830un
    delete /*+ find this */ from    t1   where    owner = 'XXX'
    
    9dumuwpg5b3ks
    DELETE /*+ find this */ from t1 where owner = 'XXX'
    
    9s7qm0xwcm7sc
    DELETE /*+ find this */ FROM T1 WHERE OWNER = 'XXX'
    
    
    5 rows selected.
    
    

    Note that changes in case, or insertion of white space, produces a different SQL_ID but when I put the statement into pl/sql as static SQL it gets "normalized" by the PL/SQL engine into upper case with no excess white space - the latter may be the phenomenon you are thinking of.

     

    Regards

    Jonathan Lewis

    Now on twitter @jloracle

  • 3. Re: SQL cache case and space insensitivity
    NaranH Newbie
    Currently Being Moderated

    Thank you Jonathan for that comprehensive response. Much appreciated.

     

    NaranH

  • 4. Re: SQL cache case and space insensitivity
    BrendanP Journeyer
    Currently Being Moderated

    Yep, and initially quite annoying when your sql comment is optimised out in pl/sql so you can't identify the sql id from the comment that you added for just that purpose!

  • 5. Re: SQL cache case and space insensitivity
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    BrendanP wrote:

     

    Yep, and initially quite annoying when your sql comment is optimised out in pl/sql so you can't identify the sql id from the comment that you added for just that purpose!

    That's why I tell people to use a "pseudo-hint" rather than a comment.  I think hint elimination can (or used to) happen from some middle-tier drivers as well.

     

    Regards

    Jonathan Lewis

  • 6. Re: SQL cache case and space insensitivity
    Solomon Yakobson Guru
    Currently Being Moderated

    Perhaps you were referring to execution plans:

     

    SQL> create table t1(owner varchar2(10));

    Table created.

    SQL> delete /*+ find this */ from t1 where owner = 'XXX';

    0 rows deleted.

    SQL> DELETE /*+ find this */ from t1 where owner = 'XXX';

    0 rows deleted.

    SQL> delete /*+ find this */ from    t1   where    owner = 'XXX';

    0 rows deleted.

    SQL> begin
      2  delete /*+ find this */ from t1 where owner = 'XXX';
      3  end;
      4  /

    PL/SQL procedure successfully completed.

    SQL> select sql_id,plan_hash_value, sql_text from v$sqlarea where sql_text like '%find this%' and sql_text not like '%v$sql
      2  /

    SQL_ID        PLAN_HASH_VALUE
    ------------- ---------------
    SQL_TEXT
    --------------------------------------------------------------------------------
    7tc7jtr6jjd5r       775918519
    delete /*+ find this */ from t1 where owner = 'XXX'

    a4ud12x5fxmnw               0
    begin delete /*+ find this */ from t1 where owner = 'XXX'; end;

    bn65fyws830un       775918519
    delete /*+ find this */ from    t1   where    owner = 'XXX'

    9dumuwpg5b3ks       775918519
    DELETE /*+ find this */ from t1 where owner = 'XXX'

    9s7qm0xwcm7sc       775918519
    DELETE /*+ find this */ FROM T1 WHERE OWNER = 'XXX'

     

    SY.

  • 7. Re: SQL cache case and space insensitivity
    NaranH Newbie
    Currently Being Moderated

    Solomon,

     

    This was not quite what I was thinking of but I found your answer useful anyway. Being a newbie I find all these explanations very useful to increase my awareness of Oracle.

     

    Many thanks for taking the time to respond.

     

    NH

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points