1 Reply Latest reply: Sep 1, 2012 9:51 AM by Girish Sharma RSS

    Performance impact with NLS_SORT= binary_ci

    user62721
      Hi,

      Is there any performance impact setting NLS_SORT=binary_ci at instance level ?

      Thanks for your help
      11.2.0.3
        • 1. Re: Performance impact with NLS_SORT= binary_ci
          Girish Sharma
          Oracle Performance issue after change the session parameters NLS_SORT and NLS_COMP.
          The default value of NLS_SORT='BINARY_CI' and NLS_COMP='BINARY' which you could get from "select * from nls_database_parameters". When people use Oracle full text search function, as suggestion to correctly return the sorted result those parameters will be changed to NLS_SORT='BINARY_CI' and NLS_COMP='LINGUISTIC'.
          The parameter 'LINGUISTIC' will completely change the search execution plan. If the index is not created for "Linguistic" purpose (like case insensitive, multilingal), the index espacially with text type (VARCHAR2) may not be selected by Oracle even you specify an index hint. See reason in Oracle document.

          To avoid this happen, do not change those two parameters in global level. Only use it in your specific session which requires Linguistic search. It could be done by set SQL command
          "ALTER SESSION SET NLS_COMP='LINGUISTIC'" or "ALTER SESSION SET NLS_COMP='ansi'"
          and don't forget change your index creation with following parameter:

          for example,

          CREATE INDEX myCaseLessIndex
          ON my_table
          (
          NLSSORT(searchColum, 'NLS_SORT = BINARY_CI')
          );

          Note: As the NLS_SORT parameter will affect all indexed search critera, make sure all expected indexes have the corresponding NLS_SORT type index. Another word using case insensitive search you have to create additional Linguistic indexes for all your PK and indexes fileds otherwise Oracle may do full table scan!!!

          Source: http://tao.qshine.com/note/note_ora.htm

          In addition to this below doc link is must for more details :
          http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch5lingsort.htm#NLSPG005

          I found below also a good note :
          http://myoracleguide.com/msa/Linguistic_Sorting_Frequently_Asked_Questions.htm

          Regards
          Girish Sharma