Index et NLSSORT

**Alkhan** · 08/10/2012, 17h34

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** · 09/10/2012, 08h42

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

**Alkhan** · 09/10/2012, 16h48

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** · 09/10/2012, 21h08

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** · 10/10/2012, 09h24

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** · 10/10/2012, 11h28

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** · 10/10/2012, 14h33

Envoyé par Alkhan

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)

**pachot** · 10/10/2012, 15h11

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

**mnitu** · 10/10/2012, 15h31

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** · 10/10/2012, 15h43

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** · 10/10/2012, 16h07

@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 ?