Blog d'un DBA sur le SGBD Oracle et SQL

08 octobre 2017

Un ORDER BY ne ralentit pas toujours une requête : Consistents gets réduits par 10!


Introduction
Vous savez qu'un ORDER BY dans une requête SQL va générer au niveau d'Oracle un SORT des données. Cette opération de tri est très consommatrice en terme de ressources mais, sur le coût total du plan d'exécution, ce coût peut être annulé en gagnant sur d'autres opérations. Nous allons voir qu'une requête avec un ORDER BY peut être aussi rapide voir même plus rapide qu'une requête sans le ORDER BY.



Points d'attention
Réfléchissez bien avant de faire un ORDER BY car cette opération est très lourde pour Oracle et dans certains cas, cela alourdit considérablement le temps d'exécution.



Base de tests
N'importe quelle base Oracle.



Exemples
============================================================================================
Création de notre base de test
============================================================================================
Pour vérifier notre hypothèse, il nous faut une table avec un nombre d'enregistrements assez important. Nous allons prendre des données de la table DBA_OBJECTS et les dupliquer dans notre table de test jusqu'à obtenir plus de 600 000 lignes.
          SQL> create table test_obj01 as select OWNER, OBJECT_NAME, SUBOBJECT_NAME from dba_objects;
          SQL> insert into test_obj01 select * from test_obj01;
          78417 rows created.

          SQL> /
          ...
          ...

          SQL> select count(*) from test_obj01;
          COUNT(*)
          ----------
          627352

On crée un ID dans la table avec la pseudo colonne ROWNUM puis on génère les stats.
          SQL> update test_obj01 set OWNER = OWNER || to_char(rownum);
          627352 rows updated.

          SQL> commit;
          SQL> exec dbms_stats.gather_schema_stats('HR');


============================================================================================
SELECT sans le ORDER BY
============================================================================================

Première exécution du SELECT sans ORDER BY : lecture des données sur disque dur, temps total 04 secondes 36 centièmes pour un coût de 1168.
          SQL> set autotrace traceonly explain statistics
          SQL> SET TIMING ON

         
SQL> select owner from test_obj01;
          627352 rows selected.

          Elapsed: 00:00:04.36

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2410895595

          ---------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT       |                      |   627K|  7351K|  1168     (1)| 00:00:01 |
          |   1 |  TABLE ACCESS FULL     | TEST_OBJ01  |   627K|  7351K|  1168     (1)| 00:00:01 |
          --------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          27  recursive calls
          0  db block gets
          45886  consistent gets
          4285  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          627352  rows processed
    

Deuxième exécution du SELECT sans ORDER BY : les données sont lues uniquement en mémoire, temps total 04 secondes 13 centièmes pour un coût de 1168. Les physical reads sont à zéro, les consistent gets restent vers 46 000.
          SQL> /
          627352 rows selected.

          Elapsed: 00:00:04.13

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2410895595

          -------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
          -------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT   |                       |   627K|  7351K|  1168     (1)| 00:00:01 |
          |   1 |  TABLE ACCESS FULL | TEST_OBJ01  |   627K|  7351K|  1168     (1)| 00:00:01 |
          -----------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          0  recursive calls
          0  db block gets
          45841  consistent gets
          0  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          627352  rows processed
    

Troisième exécution du SELECT sans ORDER BY pour valider les données du SELECT précédent : temps total 04 secondes 19 centièmes pour un coût de 1168. Les physical reads sont à zéro, les consistent gets restent vers 46 000.    
          SQL> /
          627352 rows selected.

          Elapsed: 00:00:04.19

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2410895595

          ---------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT     |                        |   627K|  7351K|  1168     (1)| 00:00:01 |
          |   1 |  TABLE ACCESS FULL   | TEST_OBJ01   |   627K|  7351K|  1168     (1)| 00:00:01 |
          --------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          0  recursive calls
          0  db block gets
          45841  consistent gets
          0  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          627352  rows processed
    

============================================================================================
SELECT avec le ORDER BY
============================================================================================

On va maintenant exécuter le même ordre SQL mais en ajoutant un ORDER BY; Oracle ajoute une opération de SORT avec un coût de 4029, soit près de quatre fois le coût de lecture de toutes les données de la table.
          SQL> set autotrace traceonly explain statistics
          SQL> SET TIMING ON

          SQL> select owner from test_obj01 order by owner;
          627352 rows selected.

          Elapsed: 00:00:03.96

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2720153981

          -----------------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                          | Name            | Rows    | Bytes |TempSpc| Cost (%CPU)| Time   |
          -----------------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT       |                      |   627K|  7351K|                |  4029   (1)| 00:00:01 |
          |   1 |  SORT ORDER BY            |                      |   627K|  7351K|    12M      |  4029   (1)| 00:00:01 |
          |   2 |   TABLE ACCESS FULL    | TEST_OBJ01  |   627K|  7351K|                |  1168   (1)| 00:00:01 |
          -----------------------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          27  recursive calls
          0  db block gets
          4328  consistent gets
          4285  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          627352  rows processed

On réexécute le SELECT, pour ne lire les données qu'en SGA.
          SQL> /
          627352 rows selected.

          Elapsed: 00:00:04.10

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2720153981

          ----------------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                        | Name            | Rows    | Bytes |TempSpc| Cost (%CPU)| Time    |
          ----------------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT     |                      |   627K   |  7351K|            |  4029   (1)| 00:00:01   |
          |   1 |  SORT ORDER BY          |                      |   627K   |  7351K|    12M  |  4029   (1)| 00:00:01   |
          |   2 |   TABLE ACCESS FULL  | TEST_OBJ01 |   627K    |  7351K|            |  1168   (1)| 00:00:01   |
          ----------------------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4283  consistent gets
          0  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          627352  rows processed

Troisième exécution.
          SQL> /
          627352 rows selected.

          Elapsed: 00:00:04.09

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2720153981

          ------------------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                        | Name            | Rows    | Bytes |TempSpc  | Cost (%CPU)| Time    |
          ------------------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT     |                      |   627K|  7351K|                  |  4029   (1)| 00:00:01  |
          |   1 |  SORT ORDER BY          |                      |   627K|  7351K|    12M        |  4029   (1)| 00:00:01  |
          |   2 |   TABLE ACCESS FULL  | TEST_OBJ01  |   627K|  7351K|                  |  1168   (1)| 00:00:01  |
          ------------------------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4283  consistent gets
          0  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          627352  rows processed


============================================================================================
Comparaison entre les deux SELECT : le ORDER BY ne ralentit pas le SELECT
============================================================================================

Nous n'allons comparer que les temps d'exécution après le premier SELECT puisque, lors du premier, les données sont sur disque dur et pas lors des SELECT suivants.
Sans ORDER BY
          Temps d'exécution : 04 secondes 13 et 04 secondes 19
          Consistent gets : 45 841 et 45 841

Avec ORDER BY
          Temps d'exécution : 04 secondes 10 et 04 secondes 09
          Consistent gets : 4 283 et 4 283
Opération en plus : SORT ORDER BY pour un coût de 4029


Comment expliquer qu'une opération de coût 4029 induite par le ORDER BY, soit quatre fois plus que le Full Table Scan de la table, ne ralentisse pas du tout la requête et même, on croit rêver, s'offre le luxe d'avoir une requête plus rapide de quelques dixièmes de secondes?

L'explication m'a été donné par Franck PACHOT, un DBA certifié Oracle Master 12c : sans ORDER BY, Oracle va lire N fois le même bloc dans le Database Buffer Cache, N variant selon ARRAYSIZE, pour récupérer toutes les données du bloc MAIS, attention, en faisant à chaque lecture un Consistent get, qui a un certain coût. Avec le ORDER BY, Oracle déplace les blocs de données du Database Buffer Cache vers la zone "SQL Work Area" de la PGA du process serveur. Et, l'explication est que Oracle ne lit pas les blocs de la même façon dans la PGA que dans la SGA puisque le nombre de Consistents gets est divisé par 10.

Pour lire la même quantité de données, Oracle fait dans un cas 45 841 Consistents gets et dans l'autre cas seulement 4 283. C'est donc sur cette action, la façon de lire les blocs de données, que le ORDER BY est plus performant et permet d'annuler le surcoût de l'opération du SORT.


Posté par David DBA à 14:21 - - Commentaires [0] - Permalien [#]
Tags : ,


15 septembre 2017

Les informations affichées par Oracle ne sont pas toujours fiables



Introduction
Un problème agaçant avec Oracle est que les informations recherchées sont parfois difficiles à trouver voir même sont incomplètes. Soit ces infos sont dispersées dans plusieurs vues sans point d'entrée unique, soit des paramètres de niveau base sont écrasés par des paramètres de niveau local soit les infos ne sont pas où le DBA junior pensent qu'elles devraient être.

Oracle est devenu au fil des versions de plus en plus complexe et des règles d'anciennes versions sont obsolètes pour les nouvelles versions, comme par exemple que le DESC permet de voir les colonnes NOT NULL.


 
Points d'attention
N/A.
 



Base de tests
N'importe quelle base Oracle.
 



Exemples
============================================================================================
Une contrainte UNIQUE peut se poser sur la colonne ou sur l'index

============================================================================================

Dans une table il est possible de définir une contrainte UNIQUE au niveau de la colonne pour empêcher la saisie de deux valeurs identiques (à l'exception du NULL). Cette contrainte est visible, non pas en faisant un DESC comme pour les contraintes NOT NULL (du moins certaines :-) ), mais en regardant dans les vues XXX_CONSTRAINTS.
          SQL> create table TEST_UNIQ (ID NUMBER(2) UNIQUE)

          Table created.

          SQL> desc TEST_UNIQ
          Name           Null?    Type
          -----------------------------------------
          ID                            NUMBER(2)

          SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS from dba_constraints where table_name = 'TEST_UNIQ' AND OWNER = 'HR';
          CONSTRAINT_NAME      C      STATUS
          -------------------- - ------------------------------
          SYS_C0011856               U      ENABLED

          SQL> insert into TEST_UNIQ values (1);
          1 row created.

          SQL> /
          insert into TEST_UNIQ values (1)
          *
          ERROR at line 1:
          ORA-00001: unique constraint (HR.SYS_C0011856) violated

On crée maintenant une deuxième table, sans contrainte unique et, une fois celle-ci créée, on ajoute un index UNIQUE. Dans les vues XXX_CONSTRAINTS cette table n'a toujours pas de contrainte UNIQUE, néanmoins elle réagit de la même façon que la table précédente car l'index sur cette colonne interdit la saisie de deux valeurs identiques. Vous noterez que le message d'erreur est le même dans les deux cas : "ORA-00001: unique constraint () violated", ce qui montre que la vérification de l'unicité d'une valeur se fait en parcourant l'index.
          SQL> create table TEST_UNIQ_IDX (ID NUMBER(2));
          Table created.

          SQL> create UNIQUE index IDX_UNIQ on TEST_UNIQ_IDX(ID);
          Index created.

          SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, STATUS from dba_constraints where table_name = 'TEST_UNIQ_IDX' and OWNER = 'HR';
          no rows selected

          SQL> select INDEX_NAME, INDEX_TYPE, UNIQUENESS from user_indexes where TABLE_NAME = 'TEST_UNIQ_IDX';
          INDEX_NAME      INDEX_TYPE         UNIQUENES
          ---------- --------------------------- -----------------------------------
          IDX_UNIQ            NORMAL               UNIQUE

          SQL> insert into TEST_UNIQ_IDX values (1);
          1 row created.

          SQL> /
          insert into TEST_UNIQ_IDX values (1)
          *
          ERROR at line 1:
          ORA-00001: unique constraint (HR.IDX_UNIQ) violated

Oracle permet de définir des contraintes d'intégrité UNIQUE de deux façons : au niveau de la colonne ou au niveau de l'index. Et, in fine, le problème pour le DBA junior ou le développeur souhaitant lister les contraintes d'intégrité, est qu'il ne peut plus se limiter à un SELECT dans les vues XXX_CONSTRAINTS mais doit obligatoirement faire un UNION avec les vues XXX_INDEXES pour afficher les contraintes UNIQUES définies au niveau des indexes.
          SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS from user_constraints where table_name = 'TEST_UNIQ_IDX'
          UNION
          select INDEX_NAME, INDEX_TYPE, UNIQUENESS from user_indexes where TABLE_NAME = 'TEST_UNIQ_IDX';
          CONSTRAINT_NAME      CONSTRAINT_TYPE         STATUS
          -------------------- --------------------------- --------- ---------------------------------
          IDX_UNIQ                        NORMAL                         UNIQUE

OK, OK, on est d'accord pour dire que stricto sensu créer un index UNIQUE ne crée pas une contrainte d'intégrité sur la colonne puisque celle-ci n'est pas dans XXX_CONSTRAINTS. Mais, comme le résultat est identique, à savoir impossibilité de créer deux fois la même valeur, on peut considèrer que c'est l'équivalent d'une contrainte d'intégrité.

============================================================================================
Taille d'un bloc de données : paramètre DB_BLOCK_SIZE ou colonne du tablespace

============================================================================================

Le paramètre DB_BLOCK_SIZE est le paramètre donnant la taille par défaut du bloc de données. Depuis plusieurs versions d'Oracle ce paramètre peut être écrasé au niveau du tablespace, exceptions faites des tablespaces SYSTEM et SYSAUX qui eux utilisent DB_BLOCK_SIZE.
          SQL> show parameter block_size
          NAME               TYPE     VALUE
          --------------------------------------------------
          db_block_size    integer     8192

          SQL> select TABLESPACE_NAME, BLOCK_SIZE from dba_tablespaces order by 1;
          TABLESPACE_NAME                    BLOCK_SIZE
          ------------------------------ ---------- -------------------------------
          APEX_1991375173370654            8192
          APEX_1993195660370985            8192
          SYSAUX                             8192
          SYSTEM                             8192
          TEMP                             8192
          UNDOTBS1                         8192
          USERS                             8192
          7 rows selected.

Nous allons créer un tablespace avec une taille de bloc différent du paramètre par défaut. Avant cela, il faut une zone mémoire dimensionnée pour accepter les blocs de taille non standard de ce tablespace sinon le CREATE TABLESPACE échoue.
          SQL> create tablespace "TEST_4K" DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/users_4K.dbf' SIZE 10M blocksize 4k EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
               *
          ERROR at line 1:
          ORA-29339: tablespace block size 4096 does not match configured block sizes

          SQL> show parameter _k_cache
          NAME                              TYPE             VALUE
          ------------------------------------ ----------- ------------------------------
          db_2k_cache_size             big integer         0
          db_4k_cache_size             big integer         0
          db_8k_cache_size             big integer         0
          db_16k_cache_size             big integer       0
          db_32k_cache_size             big integer       0

Cette opération de configuration de la SGA se fait dans le CDB$ROOT si on est en Oracle 12c architecture multi-tenant.
          SQL> alter system set db_4k_cache_size = 16m;
          alter system set db_4k_cache_size = 16m
          *
          ERROR at line 1:
          ORA-65040: operation not allowed from within a pluggable database

          SQL> connect SYS@orcl12c as sysdba
          Enter password:
          Connected.

          SQL> show con_name
          CON_NAME
          ------------------------------
          CDB$ROOT

          SQL> alter system set db_4k_cache_size = 16m;
          System altered.

          SQL> show parameter _k_cache
          NAME                               TYPE              VALUE
          ------------------------------------ ----------- ------------------------------
          db_2k_cache_size             big integer          0
          db_4k_cache_size             big integer     16M
          db_8k_cache_size             big integer          0
          db_16k_cache_size             big integer        0
          db_32k_cache_size             big integer        0

On se reconnecte à la PDB et on crée le tablespace.
          SQL> connect HR/HR@orcl
          Connected.

          SQL> create tablespace "TEST_4K" DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/users_4K.dbf' SIZE 10M blocksize 4k EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
          Tablespace created.

On voit bien que dans DBA_TABLESPACES la taille du bloc du nouveau tablespace a une taille différente de celui des autres tablespaces.
          SQL> select TABLESPACE_NAME, BLOCK_SIZE from dba_tablespaces order by 1;
          TABLESPACE_NAME            BLOCK_SIZE
          ----------------------------------------------------- ----------
          APEX_1991375173370654     8192
          APEX_1993195660370985     8192
          SYSAUX                     8192
          SYSTEM                     8192
          TEMP                     8192
          TEST_4K                  4096
          UNDOTBS1                 8192
          USERS                     8192
          8 rows selected.

Qu'est-ce que cela implique pour le DBA? Hé bien, que si vous utilisez un script calculant la taille de table ou d'index en utilisant le paramètre DB_BLOCK_SIZE, ce script sera faux pour les objets d'un tablespace créé avec une taille de bloc différente de DB_BLOCK_SIZE. On peut même dire que dans les dernières versions d'Oracle, le paramètre DB_BLOCK_SIZE est utilisé lors de la création de la base mais qu'ensuite c'est le champ BLOCK_SIZE de la table DBA_TABLESPACES qui fait foi.

Testons cela avec une table de 999 éléments.

          SQL> insert into TABLE_4K SELECT 'TEST' FROM dual connect by level < 1000;
          999 rows created.

          SQL> commit;
          Commit complete.

La taille de la table (327 680 octets) correspond bien à 80 blocs de 4 096 octets et non pas à 80 blocs de taille DB_BLOCK_SIZE pour notre base.
          SQL> select BYTES, BLOCKS from dba_segments where SEGMENT_NAME = 'TABLE_4K';
          BYTES     BLOCKS
          ---------- ----------
          327680       80

Vérification de la taille avec les valeurs en dur.
          SQL> select 80 * 4096 from dual;
          80*4096
          ----------
          327680

Vérification avec les champs de la table et du tablespace.
          SQL> select (select blocks from dba_segments where SEGMENT_NAME = 'TABLE_4K') * (SELECT BLOCK_SIZE from dba_tablespaces where tablespace_name = 'TEST_4K') AS "TAILLE TABLE"  from dual;
          TAILLE TABLE
          ------------
          327680


============================================================================================
Contrainte d'intégrité NOT NULL invisible avec le DESC et active que pour les nouveaux enregistrements

============================================================================================

Une contrainte d'intégrité NOT NULL ne signifie pas que tous les enregistrements de la table aient une valeur NOT NULL pour cette colonne! Etonnant, non? Et, plus fort, un certain type de contrainte NOT NULL n'apparait pas avec DESC alors que beaucoup de personnes utilisent cette technique pour ne pas faire de SELECT dans les vues XXX_CONSTRAINTS.

Pour rappel, Oracle a introduit les contraintes d'intégrité avec sa version 7 en 1992, soit il y a 25 ans; depuis ce temps, celles-ci se sont enrichies de beaucoup de fonctionnalités, donc certaines assez déroutantes.

La table ZZ02 a deux colonnes, dont une seule est NOT NULL.
          SQL> desc ZZ02;     
          Name                     Null?              Type
          --------------------------------------- ---------------------------------
          TABLE_NAME        NOT NULL     VARCHAR2(128)
          TABLE_TYPE                              VARCHAR2(11)

          SQL> select * from zz02;
          no rows selected

          SQL> insert into ZZ02 values ('TEST01', 'TABLE');
          1 row created.

          SQL> insert into ZZ02 values ('TEST02', 'VIEW');
          1 row created.

          SQL> insert into ZZ02 values ('TEST03', NULL);
          1 row created.

          SQL> commit;
          Commit complete.

          SQL> select * from zz02 order by 1;
          TABLE_NAME           TABLE_TYPE
          ---------------------------------------- -----------
          TEST01                     TABLE
          TEST02                     VIEW
          TEST03

Il est désormais possible avec l'option NOVALIDATE de créer une contrainte d'intégrité active uniquement pour les nouveaux enregistrements. L'alter table passe sans problème alors qu'on a pourtant un enregistrement dans la table avec le champ TABLE_TYPE à NULL.
          SQL> alter table ZZ02 modify TABLE_TYPE NOT NULL NOVALIDATE
          Table altered.

Vous noterez que la nouvelle contrainte NOT NULL avec l'option NOVALIDATE n'apparait pas dans le DESC de la table! Et pourtant, au niveau du dictionnaire de données, les deux contraintes NOT NULL existent bel et bien et ne différent que par la colonne VALIDATED.
          SQL> desc ZZ02
          Name                               Null?                   Type
          -------------------------------- -------- -------------------------------------------------
          TABLE_NAME                  NOT NULL           VARCHAR2(128)
          TABLE_TYPE                                              VARCHAR2(11)

          SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION,  STATUS,  VALIDATED, LAST_CHANGE FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'ZZ02';
          CONSTRAINT_NAME     CONSTRAINT_TYPE        SEARCH_CONDITION                 STATUS          VALIDATED          LAST_CHANGE
          --------------- ---------------     ------------------------    -------     -------------   ------------ ------------------ ----------------------------- ------
          SYS_C0011766              C                                    "TABLE_NAME" IS NOT NULL    ENABLED       VALIDATED          08-SEP-17        
          SYS_C0011855              C                                    "TABLE_TYPE" IS NOT NULL     ENABLED       NOT VALIDATED  14-SEP-17       
 

          SQL> insert into ZZ02 values ('TEST_NOT_NULL', 'IOT');
          1 row created.

La contrainte d'intégrité NOT NULL NOVALIDATE est active pour les nouveaux enregistrements et le code erreur associé est le même que celui de la contrainte NOT NULL classique.
          SQL> insert into ZZ02 values (NULL, 'TABLE');
          insert into ZZ02 values (NULL, 'TABLE')
                                   *
          ERROR at line 1:
          ORA-01400: cannot insert NULL into ("HR"."ZZ02"."TABLE_NAME")

         
SQL> insert into ZZ02 values ('TEST_NOT_NULL', NULL)

          insert into ZZ02 values ('TEST_NOT_NULL', NULL)
                                                    *
          ERROR at line 1:
          ORA-01400: cannot insert NULL into ("HR"."ZZ02"."TABLE_TYPE")

Pourtant Oracle détecte bien que certains enregistrements sont NULL même si la colonne est maintenant considérée comme NOT NULL. Par exemple, avec la fonction COUNT, seuls deux enregistrements sur quatre sont traités pour le champ TABLE_TYPE.
          SQL> select count(*) from zz02;
          COUNT(*)
          ----------
               4

          SQL> select count(table_type) from zz02;
          COUNT(TABLE_TYPE)
          -----------------
                  2

Je vous conseille donc dorénavant de ne plus vous fier au DESC d'une table pour identifier ses colonnes NOT NULL.


Posté par David DBA à 12:35 - - Commentaires [0] - Permalien [#]
Tags : , , ,