Points Importants (voir les détails dans ce qui suit) :

• Si on veut que le tri/comparaison soit binaire (sensible aux accents et à la casse), alors on est mieux :
  
  
  • de définir la valeur du paramètre de la session NLS_SORT à BINARY ;
    
    
  • de définir la valeur du paramètre de la session NLS_COMP à BINARY ;
    
    
  • et d'utiliser un indexe régulier (sans fonction).
    
  
  
• Si on veut effectuer un tri/comparaison linguistique sensible aux accents et à la casse, alors on est mieux :
  
  
  • de définir la valeur du paramètre de la session NLS_SORT à une valeur linguistique (FRENCH par exemple) ;
    
    
  • de définir la valeur du paramètre de la session NLS_COMP à LINGUISTIC ;
    
    
  • et d'utiliser un indexe avec la fonction NLSSORT qui initialise NLS_SORT à la même valeur que celle définie pour le paramètre NLS_SORT de la session.
    
  
  
• Si on veut que le tri/comparaison soit insensible aux accents et/ou à la casse, alors on est mieux :
  
  
  • de définir la valeur du paramètre de la session NLS_SORT à BINARY_AI/BINARY_CI (ou une valeur linguistique avec l'extension _AI/_CI, FRENCH_AI/FRENCH_CI par exemple) ;
    
    
  • de définir la valeur du paramètre de la session NLS_COMP à LINGUISTIC ;
    
    
  • et d'utiliser un indexe avec la fonction NLSSORT qui initialise NLS_SORT à la même valeur que celle définie pour le paramètre NLS_SORT de la session.
    

This table (and its data) is used in the examples bellow:
CREATE TABLE table_nlssort ( id NUMBER(2,0) NOT NULL , desc1 NVARCHAR2(2) NOT NULL );
INSERT INTO table_nlssort (id, desc1) VALUES (1, 'aé'); INSERT INTO table_nlssort (id, desc1) VALUES (2, 'ae'); INSERT INTO table_nlssort (id, desc1) VALUES (3, 'af'); INSERT INTO table_nlssort (id, desc1) VALUES (4, 'ad'); INSERT INTO table_nlssort (id, desc1) VALUES (5, 'aÉ'); INSERT INTO table_nlssort (id, desc1) VALUES (6, 'aE'); INSERT INTO table_nlssort (id, desc1) VALUES (7, 'aF'); INSERT INTO table_nlssort (id, desc1) VALUES (8, 'aD'); COMMIT;

Si la valeur du paramètre de la session NLS_SORT est binaire alors seul un indexe régulier (sans fonction) peut être utilisé :
ALTER SESSION SET NLS_SORT = BINARY; ALTER SESSION SET NLS_COMP = LINGUISTIC;
CREATE INDEX ix_table_nlssort_desc1 ON table_nlssort ( desc1 ); ANALYZE INDEX ix_table_nlssort_desc1 COMPUTE STATISTICS;
SELECT * FROM table_nlssort WHERE desc1 = 'aÉ';
Plan d'exécution :
| OBJECT_NAME | OPTIONS | Prédicats d'accès | +------------------------+----------------+-------------------+ | TABLE_NLSSORT | BY INDEX ROWID | | | IX_TABLE_NLSSORT_DESC1 | RANGE SCAN | DESC1 = U'a\00C9' |
Si on crée un indexe avec la fonction NLSSORT avec nls_sort initialisé à BINARY_AI, alors cet indexe ne sera pas utilisé et Oracle effectuera un full scan de la table :
DROP INDEX ix_table_nlssort_desc1; CREATE INDEX ix_table_nlssort_desc1 ON table_nlssort ( NLSSORT(desc1,'nls_sort=''BINARY_AI''') ); ANALYZE INDEX ix_table_nlssort_desc1 COMPUTE STATISTICS;
SELECT * FROM table_nlssort WHERE desc1 = 'aÉ';
Plan d'exécution :
| OBJECT_NAME | OPTIONS | Prédicats de filtre | +---------------+---------+---------------------+ | TABLE_NLSSORT | FULL | DESC1 = U'a\00C9' |
Il est possible cependant de forcer l'utilisation de l'indexe par un hint, mais ça risque, en fonction du volume des données de la table, d'être moins performant :
SELECT /*+ index(table_nlssort ix_table_nlssort_desc1) */ * FROM table_nlssort WHERE desc1 = 'aÉ';
Plan d'exécution :
| OBJECT_NAME | OPTIONS | Prédicats de filtre | | -----------------------+----------------+---------------------+ | TABLE_NLSSORT | BY INDEX ROWID | DESC1 = U'a\00C9' | | IX_TABLE_NLSSORT_DESC1 | FULL SCAN | |

Si la valeur du paramètre de la session NLS_SORT est BINARY_AI (insensible aux accents et à la casse) alors un indexe régulier ne sera pas utilisé; Oracle effectuera un full scan de la table :
ALTER SESSION SET NLS_SORT = BINARY_AI; ALTER SESSION SET NLS_COMP = LINGUISTIC;
DROP INDEX ix_table_nlssort_desc1; CREATE INDEX ix_table_nlssort_desc1 ON table_nlssort ( desc1 ); ANALYZE INDEX ix_table_nlssort_desc1 COMPUTE STATISTICS;
SELECT * FROM table_nlssort WHERE desc1 = 'aÉ';
Plan d'exécution :
| OBJECT_NAME | OPTIONS | Prédicats de filtre | +---------------+---------+----------------------------------------------------------------+ | TABLE_NLSSORT | FULL | NLSSORT(DESC1,'nls_sort=''BINARY_AI''')=HEXTORAW('0061006500') |
Il est possible cependant de forcer l'utilisation de l'indexe par un hint, mais ça risque, en fonction du volume des données de la table, d'être moins performant :
SELECT /*+ index(table_nlssort ix_table_nlssort_desc1) */ * FROM table_nlssort WHERE desc1 = 'aÉ';
Plan d'exécution :
| OBJECT_NAME | OPTIONS | Prédicats de filtre | +------------------------+----------------+----------------------------------------------------------------+ | TABLE_NLSSORT | BY INDEX ROWID | | | IX_TABLE_NLSSORT_DESC1 | FULL SCAN | NLSSORT(DESC1,'nls_sort=''BINARY_AI''')=HEXTORAW('0061006500') |
Mais si on crée un indexe avec la fonction NLSSORT, alors cet indexe sera utilisé :
DROP INDEX ix_table_nlssort_desc1; CREATE INDEX ix_table_nlssort_desc1 ON table_nlssort ( NLSSORT(desc1,'nls_sort=''BINARY_AI''') ); ANALYZE INDEX ix_table_nlssort_desc1 COMPUTE STATISTICS;
SELECT * FROM table_nlssort WHERE desc1 = 'aÉ';
Plan d'exécution :
| OBJECT_NAME | OPTIONS | Prédicats d'accès | +------------------------+----------------+----------------------------------------------------------------+ | TABLE_NLSSORT | BY INDEX ROWID | | | IX_TABLE_NLSSORT_DESC1 | RANGE SCAN | NLSSORT(DESC1,'nls_sort=''BINARY_AI''')=HEXTORAW('0061006500') |
Remarque :
Si on crée un indexe unique non sensible aux accents et/ou à la casse, on peut avoir une erreur :
CREATE UNIQUE INDEX ix_table_nlssort_desc1 ON table_nlssort (NLSSORT(code,'NLS_SORT=BINARY_AI'));
Résultat de la requête : Error starting at line 1 in command: CREATE UNIQUE INDEX IX_TABLE_NLSSORT_DESC1 ON TABLE_NLSSORT (NLSSORT(CODE,'NLS_SORT=BINARY_AI')) Error at Command Line:1 Column:69 Error report: SQL Error: ORA-00904: "CODE": invalid identifier. *Cause: *Action:
La cause de l'erreur est que la fonction NLSSORT a générée la même clé pour au moins deux valeurs des lignes différentes et donc l'indexe ne peut être unique.