Blog d'un DBA sur le SGBD Oracle et SQL

05 août 2018

Indexer la valeur NULL et l'utiliser dans la clause WHERE


Introduction
"Oracle n'indexe pas la valeur NULL" : combien de fois j'ai lu cela... il faudrait ajouter, pour être plus précis, "sauf sous certaines conditions", que nous allons voir de suite :-)



 

Points d'attention
NA.


 

Base de tests
N'importe quelle base Oracle; tests sur une 12.1.

 


 

Exemples
On crée une table de test basique, avec 10 000 enregistrements.
     SQL> create table TEST_NULL (ID NUMBER, NAME VARCHAR2(30
CHAR));
     Table created.

     SQL> begin
          for i in 1..10000
          loop
              insert into test_null values (i, 'test' || to_char(i));
          end loop;
          end;
          /
     PL/SQL procedure successfully completed.

     SQL> commit;
     Commit complete.

     SQL> select count(*) from test_null;
       COUNT(*)
     ----------
          10000

On update la table en mettant à NULL la colonne NAME tous les 100 enregistrements : de la sorte on aura 1% de la table avec NULL; cette cardinalité et cette sélectivité décideront le CBO à utiliser un index si possible.
     SQL> update test_null set name = NULL where mod(id, 100) = 0;
     100 rows updated.

     SQL> commit;
     Commit complete.
    
     SQL> select count(*) from test_null where name is null;
       COUNT(*)
     ----------
            100

On crée un index sur la colonne NAME puis les stats sont calculées pour la table et son index associé.
     SQL> create index idx_test_null_name on test_null(name);

     SQL> exec dbms_stats.gather_table_stats(ownname=>'HR', tabname=>'TEST_NULL', CASCADE=>true);
     PL/SQL procedure successfully completed.

OK, voyons le plan d'exécution si on fait une recherche sur NAME IS NULL. Ah, l'index n'est pas utilisé, comme on s'y attendait puisque Oracle n'indexe pas la valeur NULL. On notera la remarque "
filter("NAME" IS NULL)".
     SQL> set autotrace traceonly

     SQL> select name from test_null where name is null;
     100 rows selected.

     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 1897709643

     -------------------------------------------------------------------------------
     | Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
     -------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |          |   100 |   900 |     9    (0)| 00:00:01 |
     |*  1 |  TABLE ACCESS FULL| TEST_NULL |   100 |   900 |     9    (0)| 00:00:01 |
     -------------------------------------------------------------------------------

     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - filter("NAME" IS NULL)

On crée maintenant un index composite sur le même champ mais, attention, avec en deuxième position une constante. De la sorte tous les enregistrements de la table seront bien indexés puisque "NULL, 1" n'est plus NULL :-)  On aurait pu indexer sur NAME et ID si je l'avais mis en NOT NULL mais, avec cette constante, on augmente la taille de l'index d'un seul octet par row.
     SQL> create index idx_test_null_name_compo on test_null(name, 1);
     Index created.

     SQL> exec dbms_stats.gather_table_stats(ownname=>'HR', tabname=>'TEST_NULL', CASCADE=>true);
     PL/SQL procedure successfully completed.

Bingo, les 100 lignes ayant NULL pour NAME sont cette fois indexées.
     SQL> select index_name, num_rows from user_indexes where table_name = 'TEST_NULL' order by 1;
     INDEX_NAME                    NUM_ROWS
     ---------------------------------------
     IDX_TEST_NULL_NAME            9900
     IDX_TEST_NULL_NAME_COMPO     10000

Et cette fois le nouvel index est bien utilisé avec la clause NAME IS NULL! On note d'ailleurs après le plan que l'index est accédé via NULL :
"access ("NAME" IS NULL)".
     SQL> select name from test_null where name is null;
     100 rows selected.

     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 1219616549
     ---------------------------------------------------------------------------------------------
     | Id  | Operation     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |    
    
---------------------------------------------------------------------------------------------

     |   0 | SELECT STATEMENT |                |    100 |    900 |      2   (0)| 00:00:01 |
     |*  1 |  INDEX RANGE SCAN| IDX_TEST_NULL_NAME_COMPO |    100 |    900 |      2   (0)| 00:00:01 |     ---------------------------------------------------------------------------------------------
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - access("NAME" IS NULL)

 

Posté par David DBA à 22:09 - - Permalien [#]
Tags : , ,


10 juillet 2018

Problème de caractères spéciaux et d'accents dans le nom d'objets


Introduction
Dans tout logiciel ou langage informatique, il existe des caractères spéciaux. En général il faut les manipuler avec précaution sinon vous risquez d'avoir de très très mauvaises surprises. Nous allons voir cela avec le nom des objets sous Oracle.



 

Points d'attention
NA.


 

Base de tests
N'importe quelle base Oracle; tests sur une 12.1.

 


 

Exemples
============================================================================================
Nom de table avec des caractères spéciaux
============================================================================================
Oracle autorise, dans les noms d'objets, les caractères spéciaux suivants $ # et _ avec en plus @ et . pour les dblinks; pour les autres caractères spéciaux, il faut utiliser des guillemets.

Premier test : nom d'une table avec les caractères spéciaux autorisés; pas besoin de guillemets.
     SQL> create table A$#_ (id number);
     Table created.
     
Si on utilise d'autres caractères spéciaux, guillemets obligatoires; exemple d'un nom de colonne.
     SQL> CREATE TABLE test02 (a$^*$ VARCHAR2(10));
     CREATE TABLE test02 (a$^*$ VARCHAR2(10))
                            *
     ERROR at line 1:
     ORA-00902: invalid datatype
    
     SQL> CREATE TABLE test02 ("a$^*$" VARCHAR2(10));
     Table created.

On voit que le nom de la colonne est correctement stocké, avec les caractères spéciaux saisis.
     SQL> desc test02
     Name        Null?    Type
     ----------------------------
     a$^*$                 VARCHAR2(10)
    
     SQL> SELECT COLUMN_NAME from user_tab_cols where TABLE_NAME = 'TEST02';
     COLUMN_NAME
     --------------
     a$^*$
    
     SQL> insert into test02 values ('TEST');
     1 row created.
     
     SQL> commit;
     Commit complete.
    
Ah, problème si on veut utiliser le nom de la colonne, il faut là aussi les guillemets. Et oui, c'est le piège : vous devez TOUJOURS utiliser les guillemets si vous avez décidé de contourner les règles de nommage d'Oracle :-)  Vous voilà prévenus, EVITEZ AU MAXIMUM de contourner ces règles, même si Oracle le permet de façon documentée avec ces fameux guillemets : vous allez au devant de mauvaises surprises!

Vous avez aussi noté que dans le dictionnaire de données il n'y a pas de guillemets, alors si vous récupérez via une requête SQL la liste des tables ou colonnes de votre schéma, vous risquez d'avoir un message du style "Table inconnue" alors qu'elle existe bien dans le dictionnaire...
     SQL> select a$^*$ from test02;
     select a$^*$ from test02
              *
     ERROR at line 1:
     ORA-00923: FROM keyword not found where expected
    
     SQL> select "a$^*$" from test02;
     a$^*$
     ----------
     TEST


============================================================================================
Nom d'objet et accents : aïe, là c'est plus bizarre!
============================================================================================
Maintenant nous allons faire un autre test et utiliser, non pas un caractère spécial mais un caractère accentué qui ne fait pas partie du langage de la base, même s'il fait partie du character set de celle-ci. En bref, ma base est en anglais et je vais utiliser le caractère ù qui fait partie du jeu de données Unicode de ma base mais pas de l'anglais.

Vous noterez que je n'ai pas besoin de mettre les guillemets pour créer le nom de ma colonne. Néanmoins, pour utiliser ce nom, ça va être plus compliqué qu'avec un caractère spécial.
Ma base est en américain, character set UTF8.
     SQL> select parameter, value from v$nls_parameters order by parameter;
     PARAMETER                             VALUE
     ----------------------------------------------------------------
     NLS_CALENDAR                             GREGORIAN
     NLS_CHARACTERSET                         AL32UTF8
     NLS_COMP                             BINARY
     NLS_CURRENCY                             $
     NLS_DATE_FORMAT                          DD-MON-RR
     NLS_DATE_LANGUAGE                         AMERICAN
     NLS_DUAL_CURRENCY                         $
     NLS_ISO_CURRENCY                         AMERICA
     NLS_LANGUAGE                             AMERICAN
     NLS_LENGTH_SEMANTICS                         BYTE
     NLS_NCHAR_CHARACTERSET                         AL16UTF16
     NLS_NCHAR_CONV_EXCP                         FALSE
     NLS_NUMERIC_CHARACTERS                         .,
     NLS_SORT                             BINARY
     NLS_TERRITORY                             AMERICA
     NLS_TIMESTAMP_FORMAT                         DD-MON-RR HH.MI.SSXFF AM
     NLS_TIMESTAMP_TZ_FORMAT                      DD-MON-RR HH.MI.SSXFF AM TZR
     NLS_TIME_FORMAT                          HH.MI.SSXFF AM
     NLS_TIME_TZ_FORMAT                         HH.MI.SSXFF AM TZR
     
     19 rows selected.

Je crée une table avec une colonne appelée ù
     SQL> CREATE TABLE testaccent (ù VARCHAR2(10));

     Table created.
     
Et voilà le problème! Le ù a été transformé en deux ?? dans le dictionnaire de données.
Pourtant ù est connu du character set UTF8.
     SQL> desc testaccent
     Name            Null?   Type
     ---------------- ---------------------
     ??                        VARCHAR2(10)
    
Attention, si je peux saisir un ù à l'écran de mon PC, je suis du côté du client (pc windows 7) mais ma base est en américain et donc ne connaît pas le ù, ce qui explique pourquoi Oracle ne stocke pas le ù dans la base.
     SQL> SELECT COLUMN_NAME from user_tab_cols where TABLE_NAME = 'TESTACCENT';
     COLUMN_NAME
     ------------
     ??
    
     SQL> insert into testaccent values ('TEST');
     1 row created.
    
     SQL> commit;
     Commit complete.

Le nom de la colonne pose problème...    
     SQL>  select ù from test02;
     select ù from test02
            *
     ERROR at line 1:
     ORA-00904: "??": invalid identifier
    
Avec des guillemets ça ne passe pas non plus : ça devient compliqué...
     SQL> select "ù" from test02;
     select "ù" from test02
            *
     ERROR at line 1:
     ORA-00904: "??": invalid identifier

Alors là, même avec le nom de la colonne stocké en base, impossible de référencer ma colonne, même avec "", ça ne passe pas.
     SQL> SELECT ?? FROM testaccent;
     SELECT ?? FROM testaccent
            *
     ERROR at line 1:
     ORA-00911: invalid character
    
     SQL> SELECT "??" FROM testaccent;
     ERROR at line 1:
     ORA-00904: "??": invalid identifier

Même en PL/SQL ça plante : je pensais que le nom de la colonne stocké en base était différent de ?? mais que ?? était ce qui été affiché par Oracle. Hé ben non, là encore  impossible d'accéder à ma colonne.
     SQL> set serveroutput on
     
     SQL> DECLARE
         v_col_name VARCHAR2(128);
         v_select VARCHAR2(128);
     BEGIN
         SELECT COLUMN_NAME into v_col_name from user_tab_cols where TABLE_NAME = 'TESTACCENT';
         v_select := 'SELECT ' || v_col_name || ' FROM test02';
         dbms_output.put_line(v_select);
         EXECUTE IMMEDIATE 'SELECT ' || v_col_name || ' FROM test02';
     END;
     /  
     SELECT ?? FROM test02
     DECLARE
     *
     ERROR at line 1:
     ORA-00904: "??": invalid identifier
     ORA-06512: at line 8
    
Avec "", message d'erreur encore.
     SQL> DECLARE
         v_col_name VARCHAR2(128);
         v_select VARCHAR2(128);
     BEGIN
         SELECT COLUMN_NAME into v_col_name from user_tab_cols where TABLE_NAME = 'TESTACCENT';
         v_select := 'SELECT "' || v_col_name || '" FROM test02';
         dbms_output.put_line(v_select);
         EXECUTE IMMEDIATE 'SELECT ' || v_col_name || ' FROM test02';
     END;
     /  
     
     SELECT "??" FROM test02
     DECLARE
     *
     ERROR at line 1:
     ORA-00904: "??": invalid identifier
     ORA-06512: at line 8
    
Mais, alors là, le plus bizarre est que le ù est traité différemment selon qu'il est dans le nom d'une table ou d'une colonne.
     SQL> CREATE TABLE ù (ID NUMBER);
     Table created.

Le nom de la table est lui aussi ??     
     SQL> select table_name from user_tables order by 1;
     TABLE_NAME
     --------------
     A
     COUNTRIES
     DEPARTMENTS
     EMPLOYEES
     JOBS
     JOB_HISTORY
     LOCATIONS
     REGIONS
     TEST01
     TEST02
     TESTACCENT
     ??
     
     12 rows selected.

Mais on arrive quand même à l'interroger en utilisant tout simplement le caractère ù, avec guillemets ou non, alors que c'est impossible pour une colonne de même nom.    
     SQL> desc ù
     Name        Null?    Type
     -------------------------------
     ID                    NUMBER
     
     SQL> desc "ù"
     Name        Null?    Type
     -------------------------------
     ID                    NUMBER
     
Le SELECT aussi est OK.
     SQL> insert into ù values (1);
     1 row created.
     
     SQL> commit;
     Commit complete.
     
     SQL> select * from ù;
         ID
     ----------
          1

En revanche ?? reste inconnu alors que c'est ce qui est affiché dans user_tables.
     SQL> select * from ??;
     select * from ??
                   *
     ERROR at line 1:
     ORA-00911: invalid character
     
     
     SQL> select * from "??";
     select * from "??"
                   *
     ERROR at line 1:
     ORA-00942: table or view does not exist
         


J'espère que vous avez compris : ne jouez pas avec les caractères spéciaux d'Oracle et les caractères non supportés par le langage de la base.
En bref : n'utilisez que les caractères américains avec en plus $ # et _ cela vous évitera de perdre vos cheveux par poignées :-)


Posté par David DBA à 17:32 - - Permalien [#]
Tags : ,