Discussion :

[Alkhan]

Bonjour,

version oracle : 11g

J'ai quelques problème avec une base de données qui se trouve être générée à partir d'une autre base (non oracle).
L'un des problèmes vient du fait que cette base ne doit pas être sensible à la casse, et aux accents.

J'ai donc pour cela des indexes qui ont été créés avec certaines colonnes en nslsort et affecte pour la session les paramètres NLS_SORT à BINARY_AI et NLS_COMP à LINGUISTIC.

Lorsque je fais une requête, oracle effectue un full table scan selon ce qui est écrit dans la documentation. Je n'y vois aucun inconvénient sur des petites tables mais sur une table de 6 millions d'enregistrements cela me pose un vrai problème.

Y a-t-il un autre moyen, de passer par des indexes classiques et d'avoir des requêtes non sensibles à la casse, et aux accents ?

Merci d'avance !

[mnitu]

Avez vous un petit jeu d'essai de ce que vous avez fait ?

[Alkhan]

Voila un bon exemple de la situation dans laquelle je me trouve.
Bien sur dans cet exemple la table est petite, mais j'obtiens le même comportement sur une table avec beaucoup d'enregistrements.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
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 (5, '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 (6, 'aE');
INSERT INTO TABLE_NLSSORT (id, desc1) VALUES (7, 'aF');
INSERT INTO TABLE_NLSSORT (id, desc1) VALUES (8, 'aD');
 
COMMIT;
 
ALTER SESSION SET NLS_SORT = BINARY_AI;
ALTER SESSION SET NLS_COMP = LINGUISTIC;
 
CREATE INDEX IX_TABLE_NLSSORT_DESC1 ON TABLE_NLSSORT ( NLSSORT(DESC1,'nls_sort=''BINARY_AI''') );
ANALYZE INDEX IX_TABLE_NLSSORT_DESC1 COMPUTE STATISTICS;
exec dbms_stats.gather_table_stats('User1', 'TABLE_NLSSORT');
SELECT * FROM TABLE_NLSSORT WHERE desc1 = 'aÉ';

Le plan d'exécution :

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_fd28fb5fa3768f74
SQL text: SELECT * FROM TABLE_NLSSORT WHERE desc1 = 'aÉ'
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_gua7vbyjrd3vn696830d3         Plan id: 1768435923
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
 
Plan hash value: 1524426049
 
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |     8 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE_NLSSORT |     1 |     8 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DESC1"=U'a\00C9')

[pachot]

Bonjour,

Chez moi (11.2.0.3) ça marche:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
> drop table TABLE_NLSSORT
table TABLE_NLSSORT dropped.
> CREATE TABLE TABLE_NLSSORT
(
    ID NUMBER(2,0) NOT NULL
,   DESC1 NVARCHAR2(2) NOT NULL
)
table TABLE_NLSSORT created.
> INSERT INTO TABLE_NLSSORT (id, desc1) VALUES (1, 'aé')
1 rows inserted.
> INSERT INTO TABLE_NLSSORT (id, desc1) VALUES (5, 'aÉ')
1 rows inserted.
> INSERT INTO TABLE_NLSSORT (id, desc1) VALUES (2, 'ae')
1 rows inserted.
> INSERT INTO TABLE_NLSSORT (id, desc1) VALUES (3, 'af')
1 rows inserted.
> INSERT INTO TABLE_NLSSORT (id, desc1) VALUES (4, 'ad')
1 rows inserted.
> INSERT INTO TABLE_NLSSORT (id, desc1) VALUES (6, 'aE')
1 rows inserted.
> INSERT INTO TABLE_NLSSORT (id, desc1) VALUES (7, 'aF')
1 rows inserted.
> INSERT INTO TABLE_NLSSORT (id, desc1) VALUES (8, 'aD')
1 rows inserted.
> COMMIT
committed.
> ALTER SESSION SET NLS_SORT = BINARY_AI
session SET altered.
> ALTER SESSION SET NLS_COMP = LINGUISTIC
session SET altered.
> CREATE INDEX IX_TABLE_NLSSORT_DESC1 ON TABLE_NLSSORT ( NLSSORT(DESC1,'nls_sort=''BINARY_AI''') )
index IX_TABLE_NLSSORT_DESC1 created.
> ANALYZE INDEX IX_TABLE_NLSSORT_DESC1 COMPUTE STATISTICS
index IX_TABLE_NLSSORT_DESC1 analyzed.
> exec dbms_stats.gather_table_stats(user, 'TABLE_NLSSORT')
anonymous block completed
> select * from table_nlssort where desc1 = 'aÉ'
ID DESC1
-- -----
 1 aé    
 5 aÉ    
 2 ae    
 6 aE    
 
> select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1570464067                                                                                                                                                                                                                                                                                  
 
------------------------------------------------------------------------------------------------------                                                                                                                                                                                                       
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                       
------------------------------------------------------------------------------------------------------                                                                                                                                                                                                       
|   0 | SELECT STATEMENT            |                        |     1 |    40 |     1   (0)| 00:00:01 |                                                                                                                                                                                                       
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_NLSSORT          |     1 |    40 |     1   (0)| 00:00:01 |                                                                                                                                                                                                       
|*  2 |   INDEX RANGE SCAN          | IX_TABLE_NLSSORT_DESC1 |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                       
------------------------------------------------------------------------------------------------------                                                                                                                                                                                                       
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   2 - access(NLSSORT("DESC1",'nls_sort=''BINARY_AI''')=HEXTORAW('0061006500') )                                                                                                                                                                                                                             
 
Note                                                                                                                                                                                                                                                                                                         
-----                                                                                                                                                                                                                                                                                                        
   - dynamic sampling used for this statement (level=2)                                                                                                                                                                                                                                                      
 
 18 rows selected

Pouvez-vous afficher vos paramètres NLS:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
> select * from nls_session_parameters
PARAMETER                      VALUE                                  
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN                                 
NLS_TERRITORY                  AMERICA                                  
NLS_CURRENCY                   $                                        
NLS_ISO_CURRENCY               AMERICA                                  
NLS_NUMERIC_CHARACTERS         .,                                       
NLS_CALENDAR                   GREGORIAN                                
NLS_DATE_FORMAT                DD-MON-RR                                
NLS_DATE_LANGUAGE              AMERICAN                                 
NLS_SORT                       BINARY_AI                                
NLS_TIME_FORMAT                HH.MI.SSXFF AM                           
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM                 
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                       
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR             
NLS_DUAL_CURRENCY              $                                        
NLS_COMP                       LINGUISTIC                               
NLS_LENGTH_SEMANTICS           BYTE                                     
NLS_NCHAR_CONV_EXCP            FALSE

Cordialement,
Franck.

[Alkhan]

Merci pour votre réponse.
Moi je suis sur une version 11.2.0.1.

Du coup, j'ai vraiment un problème de mon coté, mais je ne vois pas lequel !

Mes paramètre nls sont les suivants :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
PARAMETER                      VALUE                                  
------------------------------ ----------------------------------------
NLS_LANGUAGE                   FRENCH                                   
NLS_TERRITORY                  FRANCE                                   
NLS_CURRENCY                   €                                        
NLS_ISO_CURRENCY               FRANCE                                   
NLS_NUMERIC_CHARACTERS         ,                                        
NLS_CALENDAR                   GREGORIAN                                
NLS_DATE_FORMAT                DD/MM/RR                                 
NLS_DATE_LANGUAGE              FRENCH                                   
NLS_SORT                       BINARY_AI                                
NLS_TIME_FORMAT                HH24:MI:SSXFF                            
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF                   
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR                        
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR               
NLS_DUAL_CURRENCY              €                                        
NLS_COMP                       LINGUISTIC                               
NLS_LENGTH_SEMANTICS           BYTE                                     
NLS_NCHAR_CONV_EXCP            FALSE

De quel façon je pourrais savoir pourquoi cela ne fonctionne pas chez moi ?

[mnitu]

Pourriez-vous faire un test sous sqlplus en copiant le code en dessous et nous montrer le résultat ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
ALTER SESSION SET NLS_SORT = BINARY_AI;
ALTER SESSION SET NLS_COMP = LINGUISTIC;
set linesize 132
set autotrace on
SELECT * FROM TABLE_NLSSORT WHERE desc1 = 'aÉ'
/

[pachot]

De quel façon je pourrais savoir pourquoi cela ne fonctionne pas chez moi ?

Pouvez-vous montrer le résultat et le plan d'exécution tel que je l'ai fait:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 SELECT * FROM table_nlssort WHERE desc1 = 'aÉ'
SELECT * FROM TABLE(dbms_xplan.display)

[Alkhan]

@mnitu : j'ai essayé mais j'ai un problème de droit et me retrouve avec le message suivant :

Impossible de rassembler les statistiques. Assurez-vous que l'utilisateur dispose de l'accès adéquat.
La fonction de statistique nécessite que l'utilisateur puisse sélectionner v_$sesstat, v_$statname et v_$session.

J'ai tenté d'ajouté le privilège select sur ces trois objets mais rien n'y fait.


@pachot
voila le résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
 5 aÉ    
 
 
-----------------------------------------------------------------------------------                                                                                                                                                                                                                          
| Id  | Operation                         | Name          | Rows  | Bytes | Cost  |                                                                                                                                                                                                                          
-----------------------------------------------------------------------------------                                                                                                                                                                                                                          
|   0 | SELECT STATEMENT                  |               |     1 |    17 |     3 |                                                                                                                                                                                                                          
|   1 |  TABLE ACCESS FULL                | TABLE_NLSSORT |     1 |    17 |     3 |                                                                                                                                                                                                                          
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY       |  8168 | 16336 |    29 |                                                                                                                                                                                                                          
|   0 | SELECT STATEMENT                  |               |  8168 | 16336 |    29 |                                                                                                                                                                                                                          
|   1 |  TABLE ACCESS FULL                | TABLE_NLSSORT |     1 |    17 |     3 |                                                                                                                                                                                                                          
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY       |  8168 | 16336 |    29 |                                                                                                                                                                                                                          
-----------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                        
Note                                                                                                                                                                                                                                                                                                         
-----                                                                                                                                                                                                                                                                                                        
- 'PLAN_TABLE' is old version

Je vois la note "'PLAN_TABLE' is old version" mais je ne comprend pas ce que cela signifie exactement ?

[pachot]

Désolé, c'est SELECT * FROM TABLE(dbms_xplan.display_cursor)
à lancer juste après le select.

[mnitu]

To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you.
...
Example 8-2 Creating the PLUSTRACE Role

Run the following commands from your SQL*Plus session to create the PLUSTRACE role and grant it to the DBA:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
CONNECT / AS SYSDBA 
@$ORACLE_HOME/sqlplus/admin/plustrce.sql 
 
drop role plustrace;
-- Role dropped.
 
create role plustrace;
-- Role created.
 
grant plustrace to dba with admin option;
-- Grant succeeded.

Example 8-3 Granting the PLUSTRACE Role

Run the following commands from your SQL*Plus session to grant the PLUSTRACE role to the HR user:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CONNECT / AS SYSDBA 
GRANT PLUSTRACE TO HR; 
-- Grant succeeded.

[Alkhan]

ok j'ai essayé mais j'ai un problème de privilège :

User has no SELECT privilege on V$SQL_PLAN

J'ai essayé de l'ajouté (via enterprise manager) mais cela ne semble rien y faire.

comment je dois procéder ?

[Alkhan]

@mnitu : visiblement je suis vraiment dans la semoule !

[oracle@gavarnie ~]$ sqlplus / AS SYSDBA

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 10 15:51:26 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

.....

mon instance est pourtant bien lancé car je suis connecter dessus dans l'enterprise manager !

J'ai lu que la taille de la SGA pourrait en être la cause, mais je ne suis pas sur que ce soit ca le problème, et si c'est le cas comment la modifier ?

[ojo77]

Vous avez dû oublier de positionner vos variables d'environnement

[Alkhan]

je ne sais pas ! de quels variables parle t-on ?
Si c'est ORACLE_HOME et ORACLE_SID elles sont correct !
il en faut d'autres ?

[Alkhan]

J'ai visiblement quelques problèmes avec la base qui font que je ne peux plus me connecter avec sqlplus.
en attendant je passe par l'utilisateur system sur sqlDevelopper sur lequel j'ai quelques possibilités.

@mnitu : je vais donc essayer de résoudre ces problèmes et je donnerai le résultat lorsque ce sera ok !

@pachot : voila le résultat

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gvqugkwx56q9g, child number 1                                                                                                                                                                                                                                                                        
-------------------------------------                                                                                                                                                                                                                                                                        
SELECT * FROM table_nlssort WHERE desc1 = 'aÉ'                                                                                                                                                                                                                                                        
 
Plan hash value: 1570464067                                                                                                                                                                                                                                                                                  
 
------------------------------------------------------------------------------------------------------                                                                                                                                                                                                       
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                       
------------------------------------------------------------------------------------------------------                                                                                                                                                                                                       
|   0 | SELECT STATEMENT            |                        |       |       |     2 (100)|          |                                                                                                                                                                                                       
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_NLSSORT          |     3 |    42 |     2   (0)| 00:00:01 |                                                                                                                                                                                                       
|*  2 |   INDEX RANGE SCAN          | IX_TABLE_NLSSORT_DESC1 |     3 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                       
------------------------------------------------------------------------------------------------------                                                                                                                                                                                                       
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
2 - access("TABLE_NLSSORT"."SYS_NC00003$"=HEXTORAW('0061006500') )

Mais je l'ai executé en system est il possible que le problème vienne d'un problème de privilège ?

[Mohamed.Houri]

Bonjour,

Vous avez créé un "function based" index comme suit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
session 1> CREATE INDEX IX_TABLE_NLSSORT_DESC1 ON TABLE_NLSSORT ( NLSSORT(DESC1,'nls_sort=''BINARY_AI''') );

Pour que votre index soit éventuellement utilisé par le CBO il faut absolument que l'index coïncide parfaitement avec (a) soit votre requête (b) ici votre environnement (nls_sort)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
 
session 2> select * from nls_session_parameters;
 
PARAMETER                      VALUE
------------------------------ ---------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                YYYYMMDD HH24:MI:SS
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
 
17 rows selected.
 
session 2> SELECT * FROM TABLE_NLSSORT WHERE desc1 = 'a';
 
no rows selected
 
session 2> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  5m6syxqjx4yvb, child number 1
-------------------------------------
SELECT * FROM TABLE_NLSSORT WHERE desc1 = 'a'
 
Plan hash value: 3632603016
 
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     5 (100)|          |
|*  1 |  TABLE ACCESS FULL| TABLE_NLSSORT |     1 |     8 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DESC1"=U'a\0090')
 
 
session 2> alter session set NLS_SORT = BINARY_AI;
 
Session altered.
 
session 2> SELECT * FROM TABLE_NLSSORT WHERE desc1 = 'a';
 
no rows selected
 
session 2> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  5m6syxqjx4yvb, child number 2
-------------------------------------
SELECT * FROM TABLE_NLSSORT WHERE desc1 = 'a'
 
Plan hash value: 3632603016
 
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     5 (100)|          |
|*  1 |  TABLE ACCESS FULL| TABLE_NLSSORT |     1 |     8 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DESC1"=U'a\0090')
 
 
18 rows selected.
 
session 2> ALTER SESSION SET NLS_COMP = LINGUISTIC;
 
Session altered.
 
session 2> SELECT * FROM TABLE_NLSSORT WHERE desc1 = 'a';
 
no rows selected
 
session 2>select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  5m6syxqjx4yvb, child number 0
-------------------------------------
SELECT * FROM TABLE_NLSSORT WHERE desc1 = 'a'
 
Plan hash value: 3119938428
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_NLSSORT          |     1 |     8 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TABLE_NLSSORT_DESC1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("TABLE_NLSSORT"."SYS_NC00003$"=HEXTORAW('0061009000') )

Je crois avoir lu ici Franck Pachot dire que les paramètres au niveau session priment sur ceux au niveau instance qui eux priment sur ceux au niveau Database; quid alors de ceux au niveau environnement Windows(OS) par exemple (registry ou variable d'environnement). Peut-être que vous devriez aussi regarder de ce côté là si vous êtes sûr qu'au niveau de votre session vous avez bien mis les paramètres qu'il faut.

[mnitu]

En conclusion ça marche mais vous devez prendre soin d’exécuter la requête avec les paramètres NLS_SORT et NSL_COMP correctement positionnés.

[pachot]

Bonjour,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

access("TABLE_NLSSORT"."SYS_NC00003$"=HEXTORAW('0061006500') )

Ca c'est bon. Il a remplacé le prédicat sur DESC1 par un prédicat sur la colonne virtuelle du Function Based Index.

Mais je l'ai executé en system est il possible que le problème vienne d'un problème de privilège ?

Non, mais de l'environnement NLS_SORT NLS_COMP, oui.
Dans mon exemple, j'ai explicitement fait:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 ALTER SESSION SET NLS_SORT = BINARY_AI
 ALTER SESSION SET NLS_COMP = LINGUISTIC

avant de lancer la requête. Puis j'ai regardé NLS_SESSION_PARAMETERS dans le même environnement que là où est lancée la requête (pas dans une autre session connectée différemment).

quid alors de ceux au niveau environnement Windows(OS) par exemple (registry ou variable d'environnement). Peut-être que vous devriez aussi regarder de ce côté là si vous êtes sûr qu'au niveau de votre session vous avez bien mis les paramètres qu'il faut.

Oui. L'environnement va définir les paramètres de sessions. Par exemple NLS_LANG va définir NLS_LANGUAGE qui va lui-même définit NLS_SORT...
En gros, du moment que NLS_LANG est défini (ce qui est la bonne pratique), les valeurs NLS de la base et de l'instance n'ont pas d'importance pour le client.
Mais de toute façon, les paramètres qu'utilisent la requête devrait se voir dans NLS_SESSION_PARAMETERS.

Cordialement,
Franck.

[Alkhan]

pour le moment je ne peux pas récupérer le plan d'execution via une requete, mais j'espère résoudre mes autres problèmes rapidement pour pouvoir le faire directement via sqlplus.
en attendant, j'execute le script sur sqldevelopper et récupère le plan sur enterprise manager.
Voila donc le script exécuté :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
ALTER SESSION SET NLS_SORT = BINARY_AI;
ALTER SESSION SET NLS_COMP = LINGUISTIC;
SELECT * FROM nls_session_parameters;
ALTER SESSION SET optimizer_capture_sql_plan_baselines = true;
SELECT * FROM table_nlssort WHERE desc1 = 'aÉ';
ALTER SESSION SET optimizer_capture_sql_plan_baselines = false;

l'affichage du résultat du script :

session SET modifié(e).
session SET modifié(e).
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE FRENCH
NLS_TERRITORY FRANCE
NLS_CURRENCY €
NLS_ISO_CURRENCY FRANCE
NLS_NUMERIC_CHARACTERS ,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD/MM/RR
NLS_DATE_LANGUAGE FRENCH
NLS_SORT BINARY_AI
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY €
NLS_COMP LINGUISTIC
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 lignes sélectionnées

session SET modifié(e).
ID DESC1
-- -----
1 aé
5 aÉ
2 ae
6 aE

session SET modifié(e).

et enfin le plan :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
 
Détails de la ligne de base de plan SQL
 
 
 
 
 
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_fd28fb5fa3768f74
SQL text: SELECT * FROM table_nlssort WHERE desc1 = 'aÉ'
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_gua7vbyjrd3vn696830d3         Plan id: 1768435923
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
 
Plan hash value: 1524426049
 
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |     8 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE_NLSSORT |     1 |     8 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DESC1"=U'a\00C9')

j'ai pourtant bien effectué les alter session pour nls_sort et nls_comp, mais le plan n'est pourtant pas bon !

cependant cela fonctionne avec l'utilisateur system, donc d'ou vient le problème ?

[pachot]

Pour moi ce plan ne correspond pas à celui qui a été exécuté.

filter("DESC1"=U'a\00C9') ne peut pas retourner aé ni ae ni aE car \00C9 en Unicode, c'est É

D'autre part, c'est un peu bizarre de créer une plan baseline pour avoir le plan d'exécution.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM TABLE(dbms_xplan.display_cursor)

Donne le plan d'exécution de la dernière requête exécutée.

Cordialement,
Franck.