Oracle Index recommendations for nls_sort=BINARY_AI

Version 1
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy AR System Server


    COMPONENT:

    AR System Server


    APPLIES TO:

    ARS 8.x



    DETAILS:

    Indexes were not used, We can identify that by looking at the Explain plan :
     

    Elapsed: 00:00:01.70  Execution Plan ---------------------------------------------------------- Plan hash value: 2731527207  --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | UPDATE STATEMENT   |      |     1 |    72 | 51118   (1)| 00:00:02 | |   1 |  UPDATE            | T479 |       |       |            |          | |*  2 |   TABLE ACCESS FULL| T479 |     1 |    72 | 51118   (1)| 00:00:02 | ---------------------------------------------------------------------------
      
    Predicate Information (identified by operation id): ---------------------------------------------------     2 - filter(NLSSORT("C1",'nls_sort=''BINARY_AI''')=HEXTORAW('73616B313               0393234353200'))
      
    This indicates that indexes are not used or not created. 

    Suggested customer to use following KB : https://bmcsites.force.com/casemgmt/sc_KnowledgeArticle?sfdcid=kA014000000dyTpCAI&type=FAQ 

    Customer had used this query but indexes were not created correctly. 

    Customer was using following configuration : 

    Db-Functional-Index: T
    Db-Case-Insensitive: T
    nls_comp   LINGUISTIC
    nls_sort BINARY_AI


    But Remedy support only BINARY_CI indexes by default Binary CI indexes will be created, so customer need to make sure they re-create the indexes correctly. 
       
      CREATE INDEX "ARADMIN"."I479_536870966_1" ON "ARADMIN"."T479" (NLSSORT("C53687 0966",'nls_sort=''BINARY_AI'''))
      
      

     


    Article Number:

    000131212


    Article Type:

    Product/Service Description



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles